Schrödinger's SQL

The SQL inside the ORM/Builder box


    SELECT firstname, name FROM address_book ORDER BY name;

Select firstname and name from address book and order them by name

id	firstname	name
1	John	Doe
2	Jane	Doe
3	Leo	Part


    SELECT firstname, name FROM address_book GROUP BY name;

ERROR: column "address_book.firstname" must appear in the GROUP BY clause or be used in an aggregate function


    $db = mysql_connect('127.0.0.1'):

    $result = mysql_db_query('bar', 'SELECT id, name FROM foo', $db);
    $rowCount = mysql_num_rows($result);

    for ($rowPointer = 0; $rowPointer < $rowCount; $rowPointer++) {
        echo mysql_result($result, $rowPointer,'id') . ' contains name ' .
            mysql_result($result, $rowPointer,'name') . PHP_EOL;
    }


    $pdo = new PDO('mysql:dbname=bar;host=127.0.0.1');

    $statement = $pdo->query('SELECT id, name FROM foo');

    $result = $statement->fetchAll(PDO::FETCH_CLASS, Foo::CLASS);

    foreach ($result as $foo) {
        echo $foo->getId() . ' contains name ' . $foo->getName() . PHP_EOL;
    }

Entity


    use Doctrine\ORM\Mapping as ORM;

    /**
     * @ORM\Entity
     * @ORM\Table(name="foo")
     */
    class Foo
    {
        /**
         * @ORM\Column(type="integer")
         * @ORM\Id
         * @ORM\GeneratedValue(strategy="AUTO")
         */
        private $id;

        /**
         * @ORM\Column(type="string")
         */
        private $value;

        // public function __construct(string $value)
        // public function setValue(string $value) : void
        // ...
    }

Insert new entity


    $entity = new Foo('value');

    $entityManager->persist($entity);

    $entityManager->flush();

DQL or find()


    SELECT e FROM Foo e WHERE e.id = 42


    $foo = $entityManager->find(Foo::class, 42);

Insert new entity


    $entity = new Foo('value');

    $entityManager->persist($entity);

    $entityManager->flush(); // <-- There's SQL somewhere in there

Update entity (default tracking policy)


    $entity = $entityManager
        ->find(Foo::class, 42); // <-- There's SQL somewhere in there

    $entity->setValue('new value');

    $entityManager->flush(); // <-- There's SQL somewhere in there

What Unit Of Work does


    $entity = $entityManager->find(Foo::class, 42);
    
    // ... sooo much code
    
    $entity = $entityManager->find(Foo::class, 42); // <-- No SQL query

Which language?


    $de = new Language('de');
    $en = new Language('en');
    $it = new Language('it');
    $entityManager->persist($de);
    $entityManager->persist($en);
    $entityManager->persist($it);

    $movie = new Movie('The Mutex');
    $movie->setLanguages([$de, $en, $it]);
    $entityManager->persist($movie);
    $entityManager->flush();

    $movie = $entityManager->createQuery(
            "SELECT m, l FROM Movie m
            JOIN m.languages l
            WHERE l.iso6391 = 'it' AND m.name = 'The Mutex'"
        )
        ->getResult();

    $movie[0]->getLanguages();

Clear EntityManager/Unit Of Work


    $entityManager->persist($movie);
    $entityManager->flush();

    $entityManager->clear(); // <-- Clear enities from the EntityManager/Unit Of Work

    $movie = $entityManager->createQuery(
            "SELECT m, l FROM Movie m
            JOIN m.languages l
            WHERE l.iso6391 = 'it' AND f.name = 'The Mutex'"
        )
        ->getResult();

    $movie[0]->getLanguages();

Clear EntityManager/Unit Of Work


    $entityManager->clear();
    $entityManager->clear(Entity::class); // Deprecated

Lazy/Eager Loading


    class Foo
    {
        // ... properties and metadata

        /**
         * @var Bar[]
         *
         * Use LAZY or EAGER in fetch
         * @ORM\OneToMany(targetEntity="Bar", mappedBy="foo", fetch="LAZY")
         */
        private $bars;
    }

Lazy Loading


  $foos = $entityManager->getRepository(Foo::class)
      ->findAll(); // <-- There's SQL somewhere in there

  foreach ($foos as $foo) {
      foreach ($foo->getBars() as $bar) { // <-- There's SQL somewhere in there
          // do stuff
      }
  }

Lazy Loading - SQL


    -- findAll() returns 3 Foo entities
    SELECT t0.id AS id_1, t0.name AS name_2 FROM foo t0
    -- Each query is one iteration for getting the Bar relations
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 1
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 2
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 3

Eager Loading


    $foos = $entityManager->getRepository(Foo::class)
        ->findAll(); // <-- There's SQL somewhere in there

    foreach ($foos as $foo) {
        foreach ($foo->getBars() as $bar) {
            // do stuff
        }
    }

EAGER Loading - SQL


    -- findAll() returns 3 Foo entities and joins the bar relation
    SELECT t0.id AS id_1, t0.name AS name_2, t3.id AS id_4, t3.name AS name_5, t3.foo_id AS foo_id_6
        FROM foo t0
        LEFT JOIN bar t3 ON t3.foo_id = t0.id

Same code, different SQL


    SELECT t0.id AS id_1, t0.name AS name_2 FROM foo t0
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 1
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 2
    SELECT t0.id AS id_1, t0.name AS name_2, t0.foo_id AS foo_id_3 FROM bar t0 WHERE t0.foo_id = 3


    SELECT t0.id AS id_1, t0.name AS name_2, t3.id AS id_4, t3.name AS name_5, t3.foo_id AS foo_id_6
        FROM foo t0
        LEFT JOIN bar t3 ON t3.foo_id = t0.id

Explicit join - One query


SELECT f, b FROM Entity\Foo f JOIN f.bars b


SELECT f0_.id AS id_0, f0_.name AS name_1, b1_.id AS id_2, b1_.name AS name_3, b1_.foo_id AS foo_id_4
    FROM foo f0_
    INNER JOIN bar b1_ ON f0_.id = b1_.foo_id

DQL → SQL


    SELECT a FROM Entity\AddressBook a


    SELECT a0_.id AS id_0, a0_.firstname AS firstname_1, a0_.name AS name_2
        FROM address_book a0_;


    SELECT q FROM Entity\Qux q WHERE q.city = 'Hahnland'

Full table scan

Index scan


    select * from qux WHERE city = 'Hahnland';

Before

Next release

DBAL QueryBuilder


    $queryBuilder->where('f.id = 1')
        ->select('f.name', 'f.id', 'b.name as bname', 'q.stuff')
        ->leftJoin('f', 'bar', 'b', 'b.foo_id = f.id')
        ->from('foo', 'f')
        ->orWhere('f.id = 2')
        ->leftJoin('f', 'qux', 'q', 'q.foo_id = f.id'); // join order?

    if ($yolo) {
        $queryBuilder->andWhere('q.moreStuff = true');
    }

Window Function


    SELECT name, department,
        SUM(revenue) OVER (PARTITION BY department ORDER BY name)
        FROM company;

name	department	SUM
Albert	sales	1200
Zoe	sales	1200
Angela	development	1804

Values List


    VALUES ('value1', 42, 1.8), ('value2', 12, 9);

Partially implemented


    INSERT INTO foo VALUES ('value1', 42, 1.8), ('value2', 12, 9);

Column name alias


    SELECT * FROM (
        VALUES ('value1', 42), ('value2', 12)
    ) AS t (my_text, my_number);

my_text	my_number
value1	42
value2	12

Column aliases are not supported by MariaDB 10.6

JSON

MySQL/MariaDB


    SELECT JSON_OBJECT('a', name, 'b', number) AS jayson FROM foo;

PostgreSQL


    SELECT JSON_BUILD_OBJECT('a', name, 'b', number) AS jayson FROM foo;

jayson
{"a": "value1", "b": 42}

Common Table Expression (CTE)


    WITH bar (col1, col2) AS (SELECT id, content FROM foo)

    SELECT * FROM bar;

id	parent_id	name
1	NULL	A
2	NULL	B
3	NULL	C
4	2	B-1
5	1	A-1
6	2	B-2
7	1	A-2
8	5	A-1-1
9	5	A-1-2
10	8	A-1-1-1

With Recursive


    WITH RECURSIVE cat AS (
        SELECT * from category WHERE name = 'A'
        UNION ALL
        SELECT cat_recursive.* from category as cat_recursive
            JOIN cat AS parent ON parent.id = cat_recursive.parent_id
    )
    SELECT * FROM cat;

id	parent_id	name
1	NULL	A
5	1	A-1
7	1	A-2
8	5	A-1-1
9	5	A-1-2
10	8	A-1-1-1

Schrödinger's SQL The SQL inside the ORM/Builder box