On Mon, Jun 29, 2009 at 19:43, David Ihnen<[email protected]> wrote: > Rob Kinyon wrote: > > On Mon, Jun 29, 2009 at 10:13, Peter Rabbitson<[email protected]> wrote: > > > A classical use-case is "right side condition on a left-join". There is > no way to emulate those appropriately with WHERE - the condition has to > reside in the join spec itself: i.e. you need to get ALL artists, and > all cds issued in a *specific year*. If you do a regular join - you get > only artists with CDs. If you do a (standard for has_many) left join - > you populate the right side of the join with CDs you don't want, and there > is no way to WHERE them out. > > > I'm not seeing this. Code, please? > > > > I want to help because this is a CRITICAL FEATURE, as far as I'm concerned. > I have to jump through hoops and repetitive queries within dbix-class to get > my permissions data structure to resolve answers because of this lack... It > constrains my application because I can't execute the same queries I would > otherwise. > > In advanced query creation, there are data sets that you cannot declare > without using subqueries or multiple conditions in a join. > > I can't show you code that does what he asks because it is impossible for > DBIx::Class to describe that relationship. > > "Give me data on artists who didn't release a cd in 1994" > > The SQL would be like > > select artists.* from artists LEFT JOIN cds ON (cds.artist = artists.artist > AND cd.year = 1994) WHERE cd.year IS NULL;
Your query is functionally identical to: select artists.* from artists left join ( select cds.* from cds where cds.year = 1994 ) cds on ( cds.artist = artist.artist ) WHERE cds.year is null; DBIC and SQLA have never claimed that they can generate every possible SQL query. The only claim we try to achieve is to be able to generate a set of queries that is 1-to-N mappable to the set of all queries. No, this isn't the answer you're looking for. And, no, I haven't thought through all the ramifications of being able to actually use this from userland. But, hopefully this breaks one of the logjams Thanks, Rob _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
