John Davidson

php - Selecting from a subquery with the Doctrine query builder

0 comments
Message:


Suppose I have an entries table that looks something like this:


| id | category | text | user_id |
|----|----------|------|---------|
| 1 | A | z | 22 |
| 2 | B | y | 23 |
| 3 | B | x | 24 |
| 4 | C | w | 25 |

I want to get the row for specified category(ies) that have the highest ID. So in this example if I'm search for category B I'd expect it to return 3,B,x,24


There are several more columns in the table, but only those three are shown for brevity. Other columns exist which have to be joined, e.g. to get user info.


I have the following query, which works:


SELECT *
FROM (
SELECT MAX(id) as id
FROM entries
WHERE category = 'B'
GROUP BY category
) as tmp
LEFT JOIN entries e ON e.id = tmp.id
LEFT JOIN users u ON (e.user_id = u.id)


I'm not sure how to transform this in to DQL, specifically using the query builder because there are several conditions to be added for which I do not want to build up a DQL string using concatenation. I'm working with this:


$subquery = $entityManager
->getRepository(Entry::class)
->createQueryBuilder('entryIds')
->select('MAX(entryIds.id)'
->orWhere("entryIds.category = ?0")
->groupBy('entryIds.category')
->getDQL();

$rows = $entityManager
->getRepository(Entry::class)
->createQueryBuilder('entry')
->select('entry, user')
->from("($subquery)", 'tmp')
->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
->setParameter(0, 'B')
->getQuery()
->execute();

And I get this error:


[Semantical Error] line 0, col 453 near ', (SELECT MAX(entryIds.id)': Error: Subquery is not supported here

The query builder generates this DQL from the above:


SELECT entry, user
FROM Namespace\\Entry entry
LEFT JOIN Namespace\\User user WITH entry.user = user.id,
(SELECT MAX(entryIds.id) FROM Namespace\\Entry entryIds WHERE entryIds.category = ?0 GROUP BY entryIds.category) tmp

So it looks like I'm not putting the subquery in the right place; where can I put it to ensure that the base select queries the subquery and then joins the other tables?




Edit: I managed to get it to work with WHERE ANY, but the query is 4.5 times slower:


$subquery = $entityManager
->getRepository(Entry::class)
->createQueryBuilder('entryIds')
->select('MAX(entryIds.id)')
->orWhere('entryIds.sourceSystem = ?0')
->groupBy('entryIds.sourceSystem, entryIds.secondaryId')
->getDQL();

$qb = $entityManager
->getRepository(Entry::class)
->createQueryBuilder('entry');
$qb
->select('entry, user')
->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
->setParameter(0, 'B')
->where(
$qb->expr()->eq('entry.id', $qb->expr()->any($subquery))
)
->getQuery()
->execute();


My Answer:
>createQueryBuilder()
->select('MAX(e.id) as id')
->from('App\Entity\Entry', 'e')
->where('e.category = :category')
->groupBy('e.category');

$query = $entityManager
->createQueryBuilder()
->select('e, u')
->from('App\Entity\Entry', 'e')
->leftJoin('App\Entity\User', 'u', 'WITH', 'e.user = u.id')
->where($entityManager->getExpressionBuilder()->in('e.id', $subquery->getDQL()))
->setParameter('category', 'B')
->getQuery();

$result = $query->getResult();

Rate this post

3 of 5 based on 4223 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us