[Dbix-class] Left join with an extra condition

[Peter Rabbitson]

On Sat, Oct 24, 2009 at 01:43:33PM -0700, Bill Moseley wrote:
> On Sat, Oct 24, 2009 at 8:24 AM, Wallace Reis <wallace at reis.org.br> wrote:
> 
> > On 24/10/2009, at 11:48, Bill Moseley wrote:
> >
> >  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.
> >>
> >
> > You can do it if you use a DBMS with RULEs support (like PostgreSQL).
> >
> 
> Are you talking about creating a database view that can be updated?
> 
> 
> 
> > Or you can setup a belongs_to rel (where is_foreign_key_constraint => 0) to
> > the user and override qw/insert delete update/ row methods in your view
> > class to do such operations on ->referred_user instead of $self, like you
> > would do with RULEs on DBMS.
> >
> 
> That seems like a complex approach for something seemingly simple.   DBIC
> allows fetching additional columns when using database functions (e.g
> length() ).  And my query is returning just a user row and a count.  Would
> not a simple approach be to run the query and then populate user objects?  I
> guess I won't have result set  to use for count or paging results.
> 
> I'm wondering if I'm missing something.  Is it really true that with
> DBIx::Class if you need a list of objects, but the query to fetch that list
> cannot be represented by a search on a resultset then there's no way to use
> SQL directly to get the set of objects?
> 

You have your terms mixed up, this is why it looks complex. The virtual view
is a *result source*. It will spawn *result sets* which will eventually become
SQL queries. The results coming back from the database will be interpreted
and stuffed into objects of the *result class* associated with the result set.

So depending on what you actually want as an end result you can:

1) Create a virtual view resultsource, and give it a specific result_class
via ->resultset_attributes (which will add the result_class setting to every
resultset class spawned from the virtual source)

2) You can take a resultset from your original source, and *temporarily* (i.e.
just for the life of this particular resultset object) substitute the 'from'
attribute with a scalarref of the SQL you want to execute. The 'from'
attribute represents everything between the FROM and WHERE keywords. There
used to be documentation of this attribute, but it was mostly factually
incorrect and thus was undocumented. However you might see how the scalarref
part worked here (the rest is now mostly irrelevant, as the format has changed,
A LOT): http://dev.catalyst.perl.org/svnweb/bast/revision/?rev=7711

Cheers