Geekly Articles each Day
📜 ⬆️ ⬇️

SQL vs ORM

Friends, again it's time the author's column of the corporate blog PG Day'17 . We offer you a comparative analysis of the work with PostgreSQL from the popular ORM from varanio .


ORM (Object-Relational Mapping), in theory, should save us from writing SQL queries and, ideally, abstracting the database (the way data is stored) in general, so that we can work with classes that more or less express business objects. -logic, without asking the question, in which tables all this in fact lies.


Let's see how it works modern libraries in PHP. Let's look at some typical cases and compare ORM with bare, hand-written SQL.


For example, we take two tables: books and authors of books, many-to-many relations (books can have many authors, authors can have many books). Those. in the database it will be books, authors and the author_book link table:


Here is a diagram
CREATE TABLE authors (
   id bigserial,
   name varchar(1000) not null,
   primary key(id)
);

CREATE TABLE books (
   id bigserial,
   name VARCHAR (1000) not null,
   text text not null,
   PRIMARY KEY (id)
);

CREATE TABLE author_book (
   author_id bigint REFERENCES authors(id),
   book_id bigint REFERENCES books(id),
   PRIMARY key(author_id, book_id)
);

.


1.


.


SQL


, :


SQL
    $stmt = $pdo->prepare(
        "INSERT INTO books (name, text) VALUES (:name, :text) RETURNING id"
    );
    $stmt->execute(
        [':name' => '', ':text' => '']
    );
    $bookId = $stmt->fetchColumn();

    $stmt = $pdo->prepare(
        "INSERT INTO authors (name) VALUES (:name) RETURNING id"
    );
    $stmt->execute(
        [':name' => '']
    );
    $authorId = $stmt->fetchColumn();

    $pdo->prepare(
        "INSERT INTO author_book (book_id, author_id) VALUES (:book_id, :author_id)"
    )->execute(
        [':book_id' => $bookId, ':author_id' => $authorId]
    );

. PDO, - , - . SQL.


Laravel (Eloquent SQL)


Laravel ORM Eloquent. Eloquent — , , ActiveRecord, .. (""), .


, . , . $timestamps = false, .


Eloquent
 namespace App;

 use Illuminate\Database\Eloquent\Model;

 class Book extends Model
 {
     public $timestamps = false;

     public function authors()
     {
         return $this->belongsToMany(Author::class);
     }
 }

namespace App;

use Illuminate\Database\Eloquent\Model;

class Author extends Model
{
    public $timestamps = false;

    public function books()
    {
        return $this->belongsToMany(Books::class);
    }
}

, many-to-many . :


$book = new \App\Book;
$book->name = '';
$book->text = '';
$book->save();

$author = new \App\Author;
$author->name = '';
$author->save();

//  
$book->authors()->save($author);

:


$book = \App\Book::create(['name' => '', 'text' => '']);
$author = \App\Author::create(['name' => '']);
$book->authors()->save($author);

, , , SQL, .


Symfony (Doctrine ORM)


DataMapper. , - . (Repository), .. , Repository, EntityManager.


:


bin/console doctrine:mapping:import --force AppBundle yml
bin/console doctrine:generate:entities AppBundle

yml- , , (, many-to-many) .. .


, yml , . , yml, . , DDD.


- , .. POJO (plain old php object):


-
namespace AppBundle\Entity;

/**
 * Authors
 */
class Authors
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * @var
 \Doctrine\Common\Collections\Collection
     */
    private $book;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->book = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Authors
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Add book
     *
     * @param \AppBundle\Entity\Books $book
     *
     * @return Authors
     */
    public function addBook(\AppBundle\Entity\Books $book)
    {
        $this->book[] = $book;

        return $this;
    }

    /**
     * Remove book
     *
     * @param \AppBundle\Entity\Books $book
     */
    public function removeBook(\AppBundle\Entity\Books $book)
    {
        $this->book->removeElement($book);
    }

    /**
     * Get book
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getBook()
    {
        return $this->book;
    }
}


namespace AppBundle\Entity;

/**
 * Books
 */
class Books
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * @var string
     */
    private $text;

    /**
     * @var \Doctrine\Common\Collections\Collection
     */
    private $author;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->author = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Books
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set text
     *
     * @param string $text
     *
     * @return Books
     */
    public function setText($text)
    {
        $this->text = $text;

        return $this;
    }

    /**
     * Get text
     *
     * @return string
     */
    public function getText()
    {
        return $this->text;
    }

    /**
     * Add author
     *
     * @param \AppBundle\Entity\Authors $author
     *
     * @return Books
     */
    public function addAuthor(\AppBundle\Entity\Authors $author)
    {
        $this->author[] = $author;

        return $this;
    }

    /**
     * Remove author
     *
     * @param \AppBundle\Entity\Authors $author
     */
    public function removeAuthor(\AppBundle\Entity\Authors $author)
    {
        $this->author->removeElement($author);
    }

    /**
     * Get author
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getAuthor()
    {
        return $this->author;
    }
}

. :


$em = $this->getDoctrine()->getManager();

$author = new Authors();
$author->setName("");

$book =  new Books();
$book->setName("");
$book->setText("");

$book->addAuthor($author);
$author->addBook($book);

$em->persist($book);
$em->persist($author);
$em->flush();


, ORM , SQL. setName .. , SQL-. .


2.


SQL


$stmt = $pdo->prepare('UPDATE books SET name=:name WHERE id=:id');
$stmt->execute([
    ':name' => ' 2', ':id' => 1
]);

Laravel (Eloquent)


$book = \App\Book::find(1);
$book->name = ' 2';
$book->save();

Symfony


$em = $this->getDoctrine()->getManager();
$repository = $em->getRepository(Books::class);
$book = $repository->find(1);
$book->setName(" 2");
$em->persist($book);


- ORM, SQL.


3.


:


delete from author_book;
delete from books;
delete from authors;

insert into authors 
(id, name) 
values 
(1, ' 1'),
(2, ' 2'),
(3, ' 3');

insert into books 
(id, name, text) 
values 
(1, ' 1', '  1'),
(2, ' 2', '  2'),
(3, ' 3', '  3');

insert into author_book 
(author_id , book_id) 
values 
  (1,1),
  (1,2),
  (2,2),  
  (3,3);

SQL


SQL , (, json):


select 
  b.id as book_id, 
  b.name as book_name, 
  json_agg(a.name) as authors 
from books b 
   inner join author_book ab
      on b.id = ab.book_id
   INNER join authors a 
      on ab.author_id = a.id
GROUP BY 
   b.id

:


 book_id | book_name |        authors         
---------+-----------+------------------------
       1 |  1   | [" 1"]
       3 |  3   | [" 3"]
       2 |  2   | [" 1", " 2"]
(3 rows)

Laravel


- "Getting Started":


    $books = \App\Book::all();

    /** @var $author \App\Author */
    foreach ($books as $book) {
        print $book->name . "\n";
        foreach ($book->authors as $author) {
            print $author->name . ";";
        }
    }

, SQL. , works like magic. . Eloquent 4 :


select * from "books";
--       : 
select 
   "authors".*, 
   "author_book"."book_id" as "pivot_book_id", 
   "author_book"."author_id" as "pivot_author_id" 
from "authors" 
   inner join "author_book" 
       on "authors"."id" = "author_book"."author_id" 
where "author_book"."book_id" = ?

. , .


-, select * select authors.*. . "" (" " " "), , . , , .. , . , authors.* .


? -, , , . (['id', 'name']). , with() .. "eager loading". :


$books = \App\Book::with('authors')->get(['id', 'name']);

, :


select "id", "name" from "books";
select 
  "authors".*, 
  "author_book"."book_id" as "pivot_book_id", 
  "author_book"."author_id" as "pivot_author_id" 
from "authors" 
  inner join "author_book" 
    on "authors"."id" = "author_book"."author_id" 
where 
  "author_book"."book_id" in (?, ?, ?);

: authors . , in() id, , , PostgreSQL. , , , . , , , .


, ORM Query Builder:


     $result = DB::table('books')
         ->join('author_book', 'books.id', '=', 'author_book.book_id')
         ->join('authors', 'author_book.author_id', '=', 'authors.id')
         ->select('books.id', 'books.name', 'authors.name')
         ->get();

, , ORM. SQL, , .


Symfony


-:


$doctrine = $this->getDoctrine();
$books = $doctrine
    ->getRepository(Books::class)
    ->findAll();

foreach ($books as $book) {
    print $book->getName() . "\n";
    foreach ($book->getAuthor() as $author) {
        print $author->getName() . ";";
    }
}

Laravel. SQL-, , :


SELECT 
    t0.id AS id_1, 
    t0.name AS name_2, 
    t0.text AS text_3 
FROM books t0;

--   3  :
SELECT 
    t0.id AS id_1, 
    t0.name AS name_2 
FROM authors t0 
    INNER JOIN author_book 
         ON t0.id = author_book.author_id 
WHERE 
     author_book.book_id = ?

.. , , , .


findAll .., , , - - . , SQL- DQL, , . , .


 $query = $this->getDoctrine()->getManager()->createQuery('
    SELECT 
         partial b.{id, name}, partial a.{id, name} 
    FROM AppBundle\Entity\Books b 
       JOIN b.author a'
 );
 $books = $query->getResult();

, , , , :


SELECT 
   b0_.id AS id_0, 
   b0_.name AS name_1, 
   a1_.id AS id_2, 
   a1_.name AS name_3 
FROM 
   books b0_ 
INNER JOIN author_book a2_ 
   ON b0_.id = a2_.book_id 
INNER JOIN authors a1_ 
   ON a1_.id = a2_.author_id


, SQL . , ORM- , .


DQL SQL, , , .


4. UPDATE


, "".


SQL


, .


UPDATE authors
SET name = ''
WHERE id in (
    SELECT id
    FROM authors
    ORDER BY id DESC
    LIMIT 2
);

Laravel


:


 \App\Author::orderBy('id', 'desc')->take(2)->update(["name" => ""]);

, . , , .


, SO, :


\App\Author::whereIn(
        'id',
        function($query) {
            $query->select('id')
                ->from((new \App\Author())->getTable())
                ->orderBy('id', 'desc')
                ->limit(2);
        }
    )->update(['name' => '']);

, . query builder - .


Symfony


, DQL , .


, , query builder, - , . ORM , . , - update .



- , .


, - . CRUD- ORM . , — SQL. , / (, CTE). , .


ORM vs SQL .


, ORM, , PG Day'17. - !


')

Source: https://habr.com/ru/post/328690/

More articles:


All Articles