I'm trying to mimic in DQL a SQL query that contains a correlated subquery in a join, ala:
SELECT ... FROM Table as t LEFT JOIN OtherTable as o ON <o condition> LEFT JOIN (SELECT count(*) FROM AnyOtherTable) as r ON <r condition> WHERE <where condition>
It doesn't seem to work. So first, is this something possible? And if yes, what would be the DQL syntax for it?
> I'm trying to mimic in DQL a SQL query that contains a correlated > subquery in a join, ala:
> SELECT ... > FROM > Table as t > LEFT JOIN > OtherTable as o ON <o condition> > LEFT JOIN > (SELECT count(*) FROM AnyOtherTable) as r ON <r condition> > WHERE > <where condition>
> It doesn't seem to work. > So first, is this something possible? > And if yes, what would be the DQL syntax for it?
SELECT ... FROM Table as t LEFT JOIN OtherTable as o ON o.id = t.id LEFT JOIN (SELECT a.id, count(*) as count FROM AnyOtherTable as a GROUP BY a.id) as r ON r.id=t.id WHERE <where condition>
Is this something doctrine/dql support? And if yes, what would be the DQL syntax?
On Wed, May 14, 2008 16:43, Jonathan Wage wrote: > Hmm. I don't understand. How would that work anyways? You are doing a left > join subquery where it is a count?
> You should be able to do a subquery in a join, have you tried it?
> - Jon
> On Wed, May 14, 2008 at 6:37 AM, Brice Figureau < > brice+doctr...@daysofwonder.com <brice%2Bdoctr...@daysofwonder.com>> > wrote:
>> Hi,
>> I'm trying to mimic in DQL a SQL query that contains a correlated >> subquery in a join, ala:
>> SELECT ... >> FROM >> Table as t >> LEFT JOIN >> OtherTable as o ON <o condition> >> LEFT JOIN >> (SELECT count(*) FROM AnyOtherTable) as r ON <r condition> >> WHERE >> <where condition>
>> It doesn't seem to work. >> So first, is this something possible? >> And if yes, what would be the DQL syntax for it?
> SELECT ... > FROM > Table as t > LEFT JOIN > OtherTable as o ON o.id = t.id > LEFT JOIN > (SELECT a.id, count(*) as count FROM AnyOtherTable as a GROUP BY a.id) > as r ON r.id=t.id > WHERE > <where condition>
> Is this something doctrine/dql support? > And if yes, what would be the DQL syntax?
> Thanks, > Brice
> On Wed, May 14, 2008 16:43, Jonathan Wage wrote: > > Hmm. I don't understand. How would that work anyways? You are doing a > left > > join subquery where it is a count?
> > You should be able to do a subquery in a join, have you tried it?
> > - Jon
> > On Wed, May 14, 2008 at 6:37 AM, Brice Figureau < > > brice+doctr...@daysofwonder.com <brice%2Bdoctr...@daysofwonder.com> < > brice%2Bdoctr...@daysofwonder.com <brice%252Bdoctr...@daysofwonder.com>>> > > wrote:
> >> Hi,
> >> I'm trying to mimic in DQL a SQL query that contains a correlated > >> subquery in a join, ala:
> >> SELECT ... > >> FROM > >> Table as t > >> LEFT JOIN > >> OtherTable as o ON <o condition> > >> LEFT JOIN > >> (SELECT count(*) FROM AnyOtherTable) as r ON <r condition> > >> WHERE > >> <where condition>
> >> It doesn't seem to work. > >> So first, is this something possible? > >> And if yes, what would be the DQL syntax for it?
I dug a little bit in the Doctrine_Query code and it seems that the kind of from subquery I'm trying to achieve is impossible with Doctrine.
I think this is just because Doctrine would have to define dynamically a new kind of Doctrine_Record and a new relation to the joined tables.
I fail to see how Doctrine could do that, but I might be wrong. The problem is that I can't do that either in RawSQL, because I can't select on such dynamic object. I fear I'll have to HYDRATE_NONE/HYDRATE_ARRAY or perform the request out of the join (ie issue one request per returned Record of the master query).
On Wed, 2008-05-14 at 17:14 -0500, Jonathan Wage wrote: > I don't know off the top of my head what the syntax would be. But yes, > DQL does support sub-queries.
> - Jon
> On Wed, May 14, 2008 at 4:24 PM, Brice Figureau <brice > +doctr...@daysofwonder.com> wrote:
> Hi,
> Oops, forgot a part in the query...
> SELECT ... > FROM > Table as t > LEFT JOIN
> OtherTable as o ON o.id = t.id > LEFT JOIN > (SELECT a.id, count(*) as count FROM AnyOtherTable as a GROUP > BY a.id) > as r ON r.id=t.id > WHERE > <where condition>
> Is this something doctrine/dql support? > And if yes, what would be the DQL syntax?
> Thanks, > Brice
> On Wed, May 14, 2008 16:43, Jonathan Wage wrote: > > Hmm. I don't understand. How would that work anyways? You > are doing a left > > join subquery where it is a count?
> > You should be able to do a subquery in a join, have you > tried it?
> > - Jon
> > On Wed, May 14, 2008 at 6:37 AM, Brice Figureau <
> >> I'm trying to mimic in DQL a SQL query that contains a > correlated > >> subquery in a join, ala:
> >> SELECT ... > >> FROM > >> Table as t > >> LEFT JOIN > >> OtherTable as o ON <o condition> > >> LEFT JOIN > >> (SELECT count(*) FROM AnyOtherTable) as r ON <r condition> > >> WHERE > >> <where condition>
> >> It doesn't seem to work. > >> So first, is this something possible? > >> And if yes, what would be the DQL syntax for it?
