[Dbix-class] Left join with an extra condition

Rob Kinyon rob.kinyon at gmail.com
Sun Oct 25 02:48:18 GMT 2009


On Sat, Oct 24, 2009 at 20:18, Darren Duncan <darren at darrenduncan.net> wrote:
> Bill Moseley wrote:
>>
>> Looking for how to add an addition condition on a join.  Here's an example
>> of the query with the extra condition in CAPS
>>
>> select u.* <http://p.id/>, count(o.id <http://o.id>)
>> from user u
>> left outer join job j on   j.user = u.id <http://u.id> AND J.JOB_TYPE = 6
>> where u.location  = ?
>> group by u.*
>>
>> So, wondering how to represent that along with the extra join condition.
>
> What you are adding doesn't look like a normal join condition, as it isn't
> comparing values in the 2 tables being joined.
>
> On the other hand, any straight equality test certainly is expressible as a
> join, if you consider the "6" to be a single-column,single-row table, and
> then you are joining 3 tables.
>
> Is the syntax you propose supposed to be a shorthand for that?
>
> What DBMSs would support this syntax, as I've never seen it before?

MySQL, PG, and Oracle all support this. It's an inline WHERE condition.

Rob



More information about the DBIx-Class mailing list