[Dbix-class] inner join in prefetch drops some rows

[Paul Makepeace]

On 6/4/07, Ronald J Kimball <rkimball+dbixclass at pangeamedia.com> wrote:
>
> Paul Makepeace wrote:
> > I have a feeling this issue is known about from a message back on the
> > 11th about collapse result but just in case:
> >
> > I notice adding a prefetch to a search causes some results to go
> > missing in the case that the column being joined on is null in the
> > first table. This is down to prefetch using an inner rather than left
> > join. Presumably y'all had a reason for that? The left join solved it
> > for me (in SQL tests). Is there a work-around for this?
>
> You haven't shown us how you're defining your relationships, nor your
> code to do the search.
>
> If I define a might_have() relationship and do a prefetch, I get a LEFT
> JOIN, as I expected.  Perhaps you specified a has_one() relationship
> where you meant to specify might_have()?


Ah yes, thanks (& Matt). I think that's it.

User.pm:
__PACKAGE__->belongs_to(company =3D> 'Company' =3D> { 'foreign.uid' =3D> '
self.company_uid' });

To save an RTFM for those following along at home,

   perldoc DBIx::Class::Relationship

       If the relationship is optional -- i.e. the column containing the
for-
       eign key can be NULL -- then the belongs_to relationship does the
right
       thing. Thus, in the example above "$obj->author" would return
"undef".
       However in this case you would probably want to set the "join_type"
       attribute so that a "LEFT JOIN" is done, which makes complex
resultsets
       involving "join" or "prefetch" operations work correctly.  The
modified
       declaration is shown below:

         # in a Book class (where Author has_many Books)
         __PACKAGE__->belongs_to(author =3D> 'My::DBIC::Schema::Author',
                                 'author', {join_type =3D> 'left'});

P
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070604/2b7=
fbbb6/attachment.htm