Who has a big customer-project without a database?
Microsoft Access 2000
SEQUEL
Structured Query Language
ISO/IEC 9075
SQL-86 (or SQL-87)
SQL-89
SQL-92
SQL:1999
SQL:2003
SQL:2006
SQL:2008
SQL:2011
SQL:2016
SQL:2019
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
It's a PostgreSQL database.
No!
Yes!
Ooooh...
Know your database!
How did you learn SQL?
A chapter in books
$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();
foreach ($result as $row) {
echo $row['id'] . ' contains name ' . $row['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);
Congratulations
ORM
+
You have doubled your problems
ORM is awesome, but
before it was:
Know how to query
OOP boilerplate code
Now it is:
Know how queries look like
Know when queries happen
Know how to query
No full SQL power due to DB compatibility
Know when queries happen
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 runs in there
Unit Of Work
$entityManager->find(Foo::class, 42);
$entityManager->getRepository(Foo::class)
->find(42);
$entityManager->createQuery('SELECT e FROM Foo e WHERE e.id = 42')
->getSingleResult();
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();
All languages
de
en
it
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();
$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
$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
Extra Lazy Loading
// fetch="EXTRA_LAZY"
$foos = $entityManager->getRepository(Foo::class)
->findAll(); // <-- There's SQL somewhere in there
foreach ($foos as $foo) {
count($foo->getBars()); // <-- There's SQL somewhere in there
}
SELECT t0.id AS id_1, t0.name AS name_2 FROM foo t0
SELECT COUNT(*) FROM bar t0 WHERE t0.foo_id = 1
SELECT COUNT(*) FROM bar t0 WHERE t0.foo_id = 2
SELECT COUNT(*) FROM bar t0 WHERE t0.foo_id = 3
Know how queries look like
Doctrine DQL
SELECT a.firstname, a.name FROM Entity\AddressBook a GROUP BY a.name
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_;
Equal code, different SQL
SELECT f FROM Entity\Foo f
SELECT f0_.id AS id_0, f0_.name AS name_1 FROM foo f0_
WHERE (f0_.deleted = 0);
Filter
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;
class FooFilter extends SQLFilter
{
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
if ($targetEntity->getTableName() !== 'foo') {
return '';
}
return $targetTableAlias.'.deleted = 0';
}
}
Why should I care about the SQL parts?
SELECT q FROM Entity\Qux q WHERE q.city = 'Hahnland'
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
It's that new MySQL 8 feature
It's a SQL 2003 standard
Values List
VALUES ('value1', 42, 1.8), ('value2', 12, 9);
Values List in DB Versions
PostgreSQL: Yes - 8.2 (and earlier)
MariaDB: Yes - 10.3
MySQL: 8.0.19 (But with ROW syntax)
SQL-92
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.3
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;
With Recursive
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
listagg
SQL 2016 (and Oracle)
SELECT LISTAGG(name, ',') AS comma_separated FROM foo GROUP BY number;
PostgreSQL / MSSQL
SELECT STRING_AGG(name, ',') AS comma_separated FROM foo GROUP BY number;
MariaDB / MySQL
SELECT GROUP_CONCAT(name SEPARATOR ',') AS comma_separated FROM foo GROUP BY number;
comma_separated
Sebastian,Andreas
You may not have the full power of SQL at your fingertips