Suppose I have anentries
table that looks something like this:
| id | category | text | user_id |
|----|
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{-code-3}
I'd expect it to return3,{-code-3},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 = '{-code-3}'
GROUP {-code-3}Y 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)
->createQuery{-code-3}uilder('entryIds')
->select('MAX(entryIds.id)'
->orWhere("entryIds.category = ?0")
->group{-code-3}y('entryIds.category')
->getDQL();
$rows = $entityManager
->getRepository(Entry::class)
->createQuery{-code-3}uilder('entry')
->select('entry, user')
->from("($subquery)", 'tmp')
->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
->setParameter(0, '{-code-3}')
->getQuery()
->execute();
And I get this error:
{-code-7}
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 {-code-3}Y 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)
->createQuery{-code-3}uilder('entryIds')
->select('MAX(entryIds.id)')
->orWhere('entryIds.sourceSystem = ?0')
->group{-code-3}y('entryIds.sourceSystem, entryIds.secondaryId')
->getDQL();
$qb = $entityManager
->getRepository(Entry::class)
->createQuery{-code-3}uilder('entry');
$qb
->select('entry, user')
->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
->setParameter(0, '{-code-3}')
->where(
$qb->expr()->eq('entry.id', $qb->expr()->any($subquery))
)
->getQuery()
->execute();
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.