Recent searches
Search options
I uncovered a need to perform a nested query recently, but didn't know how to do it using Doctrine DBAL. So, now that I've figured it out, I've blogged about it.
I don't like the following sentence:
> When creating a SELECT using DBAL, you do something like this: $dbal->createQueryBuilder();
DBAL does not force you to use the queryBuilder.
You can just execute the SQL Query as is using fetchAllAssociative.
The queryBuilder is overused for non-dynamic queries in my opinion.
@mwop SQL is already a language meant to be readable by humans. Why obfuscate it by wrapping it in another language (php).
@bcremer The example in my blog post is partial, and doesn't tell the complete story - it's actually built dynamically based on a variety of parameters passed to the method, and some joins, columns, and group statements may be omitted or included as a result. I simplified it for the blog to zero in on the subquery.
@mwop Fair Enough. The QueryBuilder has it's use-cases. But in your blog post it reads like it's the only way to solve this.
@bcremer I've updated the post to clarify this.
To be honest, I often use heredoc SQL strings. Usually when I do that, however, I'll use straight PDO instead of introducing the DBAL; to me, the value of the DBAL is the query builder for dynamically generating queries.
@bcremer @mwop I like to go a step further and directly serialize DTOs/read models from it with a little package of mine: https://github.com/digital-craftsman-de/deserializing-connection
@mwop > A LEFT JOIN will cause the entire query to fail if it finds no matching rows on the joined table.
This is false. You are describing an `INNER JOIN` there.
For a `LEFT JOIN`, the joined table is optional (and the columns `NULL` if unavailable). The issue is the `t.content_type = :content_type` in your `WHERE`, which effectively makes your `LEFT JOIN` equivalent to an `INNER JOIN`.
@mwop Moving the `t.content_type = :content_type` into the `ON` would likely also have worked. But to me using a JOIN just for aggregating another table’s values is much less readable, so I agree with the fixed query.
@mwop FWIW: In the fixed query the `GROUP BY p.filename` is redundant if `p.filename` is a UNIQUE column. And if it is not, it is wrong, because then `p.description` would be a non-aggregated column that is also not functionally dependent on `p.filename`.
@timwolla Yes - somebody already left a comment to that effect on my post, along with the recommendation to move the content_type condition into the JOIN.
That said, I also agree with you that the sub select makes the intent more readable, and I've gone with that approach.
@mwop Ah, sorry. Comments did not load for me, so I missed it.
@timwolla I've updated the post to make all this more clear.