[Dbix-class] Left join with an extra condition

[Bill Moseley]

On Sat, Oct 24, 2009 at 5:59 AM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> wrote:

>
> > select u.* <http://p.id/>, count(o.id)
> > from user u
> > left outer join job j on   j.user =3D u.id AND J.JOB_TYPE =3D 6
> > where u.location  =3D ?
> > group by u.*
>

(nice how Gmail turned those columns into links...)


>
> You can not do this natively yet. Your only option is a virtual view
> resultset as described here:
>
> http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/Resul=
tSource/View.pm<http://search.cpan.org/%7Eribasushi/DBIx-Class-0.08112/lib/=
DBIx/Class/ResultSource/View.pm>
>

Ok, thanks.

Can the virtual view inherit from the user?  That is, can I convince DBIC
that the object returned is a real user object so I can update the object
and have it written to storage?  The cookbook just says that the view cannot
be operated on.  Obviously, it would be much more useful to have a real user
object.

This is  not a very complex query -- I have many queries that I suspect may
require custom SQL.  Was there any discussion of being able to add custom
SQL to a custom ResultSet class, for example?  That way I could get back my
user object plus any additional columns from the custom SQL.  A different
beast, or course, but this is something that was quite trivial to do with
Class::DBI.

Thanks for the help, Peter.

-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091024/168=
bb306/attachment.htm