[Dbix-class] PATCH: Support for arbitrary SQL in relationship

[Rob Kinyon]

On Mon, Jun 29, 2009 at 19:43, David Ihnen<davidi at norchemlab.com> wrote:
> Rob Kinyon wrote:
>
> On Mon, Jun 29, 2009 at 10:13, Peter Rabbitson<rabbit+dbic at rabbit.us> 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