I have two entities in Doctrine 2.1: Category and Site each category has many sites and each site has a parent category.
I would like to make a single update query (in DQL) which will update a field called count of the Category entity with the number of related sites.
So in SQL I would do something like this:
UPDATE categories c SET c.count = (SELECT COUNT(s.id) FROM sites s WHERE s.category_id = c.id);
This would work beautifuly, in DQL it might something like this:
UPDATE PackageNameBundle:Category c SET c.count = (SELECT COUNT(s.id) FROM PackageNameBundle:Site s WHERE s.category = c)
Such attempt raises [Syntax Error] line 0, col 61: Error: Expected Literal, got 'SELECT'.
Subqueries DO work in DQL, but the problem here (as far as I see it) is that Doctrine cannot assign the returned value from the subquery, to the c.count. This is understandable since I might fetch more than 1 field in the subquery and even more than one row. It magicaly works in MySQL since it sees one row, one field and for convenience returns a single integer value. Doctrine on the other hand has to be object oriented and has to work with different engines where such convertions might not be supported.
Finally, my question is:
What is the best way to do this in Doctrine, should I go with Native SQL or it can be done with DQL and how?
Thanks in advance!
EDIT: I just found this quote in the DQL Docs:
So, I guess assigning anything but a scalar value is impossible?
The main question remains though..
You can use native sql queries in Doctrine also, for that kind of specific queries. DQL is powerful in its own way, but it's also limited due to performance constraints. Using native sql queries and mapping the results will achieve the same thing, and there is no disadvantage in doing that.
The documentation explains it in detail.