[Dbix-class] Prefetch producing a apparently wrong query for multi-level relationships

[Nilson Santos Figueiredo Junior]

Hi everyone,

Today I ran into another issue regarding prefetching (unfortunately
I'm still trying to find some free time to write the previously asked
test cases). I'm not really sure if this is a bug or if it's by
design, but it sure doesn't DWIM.

DBIC is apparently generating a wrong query when prefetching
multi-level relationships when a has_many is involved and the relating
rows belongs_to another entity and all of those levels are prefetched.
An example might explain this a little bit better.

If you have a "article" class which might have many "comments" which
were written by a given "user" you'd probably set it up as
(pseudo-DBIC code ahead):

  article->has_many('comments')
  comment->belongs_to('user')

Then, when you try to fetch an article prefetching the comments and
their respective users, through:

  $articles->search( {}, { prefetch => { comments => ['user'] } } )

It will result in DBIC generating SQL like :

  SELECT article.*, comments.*, user.*
  FROM article article
    LEFT JOIN comment comments
      ON( comments.article_id = article.id )
    JOIN user user
      ON ( user.id = comments.user_id)

Which is not usually what you'd want since an article might have no
comments at all. Instead, it should be generating the "user" join
nested inside the outer LEFT JOIN, like this:

  SELECT article.*, comments.*, user.*
  FROM article article
    LEFT JOIN
      (
        comment comments
          JOIN user user
            ON ( user.id = comments.user_id)
      )
      ON( comments.article_id = article.id )

I don't really know if it's really by design, an unknown issue or just
a bug waiting to be fixed. But it sure is counter-intuitive.

I know that everyone prefers patches or at least some test cases but,
unfortunately, reporting the issue is the best I can do right now.

Just for the record, the obvious work-around is to not prefetch the
relationship.

-Nilson Santos F. Jr.