[Dbix-class] outer join with explicit criteria rears its ugly head (again)

[Benjamin Hitz]

I am resurrecting this tread...

So, and maybe this is obvious  - can you define a define two  
relationships to the same table (both, for example,  'belongs_to' or  
'has_many' as schema suggests), but with different join_types?

I can't see why not, but I would hate to go down this path and get  
creamed.

I think then the solution to original problem would be a where clause  
something like
"where book.user_id = 'givenuser' or book.user_id is null"

Ben



> May be smth like:
>
> $c->model('DB::Book')->search({ 'bookuser.user_id' => givenuser},{
>    join => 'bookuser'
> });
>
> where the 'bookuser' is the relationship defined like:
>
> Book->belongs_to(
>   "bookuser",
>   "User",
>   { id => "user_id" },
>   { join_type => "LEFT OUTER" },
> );
>
> ?
>
> Sorry for "draftness", I just copied samples from my code.
>
> On Tue, Oct 14, 2008 at 8:51 PM, Jason Gottshall <jgottshall at capwiz.com 
> > wrote:
> Jason Gottshall wrote:
> SELECT book.*, book_user.*
> FROM book
> LEFT OUTER JOIN book_user
>  ON (    book_user.book_id = book.book_id
>      AND book.user_id      = 'givenuser'
>  )
>
> michael reddick wrote:
> The and "book.user_id = 'giveruser'" doesn't have to be in the ON  
> statement does it? You could just put it into the where clause and  
> it should work the same.
>
> Yeah, that's the first thing I tried, but it didn't work (on Oracle,  
> anyway.) When I move the explicit criteria to the WHERE clause, I  
> seem to lose the left-outer-ness of the join, and I only get back  
> books for which the book_user table has a record. I'm stumped.  
> Anyone else?
>
> Jason
>
>
>
> _______________________________________________
> 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/dbix-class@lists.scsys.co.uk
>
> _______________________________________________
> 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/dbix-class@lists.scsys.co.uk

--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090616/d5255091/attachment.htm