John Davidson

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();