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...
$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;
}
use Doctrine\ORM\Mapping as ORM;
#[ORM\Table(name: 'foo')]
#[ORM\Entity]
class Foo
{
#[ORM\Column(type: 'integer')]
#[ORM\Id]
#[ORM\GeneratedValue(strategy: 'AUTO')]
private int $id;
#[ORM\Column(type: 'string')]
private string $value;
// public function __construct(string $value)
// public function setValue(string $value) : void
// ...
}
$entity = new Foo('value');
$entityManager->persist($entity);
$entityManager->flush();
SELECT e FROM Foo e WHERE e.id = 42
$foo = $entityManager->find(Foo::class, 42);
$entity = new Foo('value');
$entityManager->persist($entity);
$entityManager->flush(); // <-- There's SQL somewhere in there
$entity = $entityManager
->find(Foo::class, 42); // <-- There's SQL somewhere in there
$entity->setValue('new value');
$entityManager->flush(); // <-- There's SQL somewhere in there
$entity = $entityManager->find(Foo::class, 42);
// ... sooo much code
$entity = $entityManager->find(Foo::class, 42); // <-- No SQL query
$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();
$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();
class Foo
{
// ...
/**
* @var Bar[]
*
* Use LAZY or EAGER in fetch
*/
#[ORM\OneToMany(targetEntity: 'Bar', mappedBy: 'foo', fetch: 'LAZY')]
private $bars;
// ...
}
$foos = $entityManager->getRepository(Foo::class)
->findAll();
foreach ($foos as $foo) {
foreach ($foo->getBars() as $bar) {
// do stuff
}
}
-- 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
-- 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
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
SELECT q FROM Entity\Qux q WHERE q.city = 'Hahnland'
select * from qux WHERE city = 'Hahnland';
$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');
}
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 ('value1', 42, 1.8), ('value2', 12, 9);
SELECT JSON_OBJECT('a', name, 'b', number) AS jayson FROM foo;
SELECT JSON_BUILD_OBJECT('a', name, 'b', number) AS jayson FROM foo;
jayson |
---|
{"a": "value1", "b": 42} |
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 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 |
// Example with PostgreSQL
$rsm = new ResultSetMapping();
$rsm->addEntityResult(Foo::class, 'f');
$rsm->addFieldResult('f', 'column1', 'id');
$rsm->addFieldResult('f', 'column2', 'name');
$rsm->addFieldResult('f', 'column3', 'comment');
$sql = "VALUES (5, 'foo', 'It is foo.'), (6, 'bar', 'It is bar.');";
$query = $this->entityManager->createNativeQuery($sql, $rsm);
$foos = $query->getResult();