On Tue, Oct 14, 2008 at 11:51 AM, Jason Gottshall <[EMAIL PROTECTED]>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?
>
It's not returning all the books because now it's filtering only the ones
where book_user has a record for the user like you said. So it does seem
like you can only do it with the $user restriction in the ON clause.
I think you can do something like this but it's really ugly:
$c->model('DB::Book')->search({},
{ from => [ {me => 'book' },
[ { 'book_user' => 'book_user', -join_type => 'left outer'},
{ 'book_user.book_id' => 'me.book_id', 'book_user.user_id' =>
'$user'}
]
] } );
michael
_______________________________________________
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/[email protected]
