DBIx::Class chaining resultsets with same table in prefetch

Question

I am trying to implement resultsets chaining in my application.

Here are some methods from my resultset class:

package Schema::ResultSet::Category;

use base 'DBIx::Class::ResultSet';

sub with_translation {
  my ($self, $lang) = @_;

  $self->search(
    {
      'language.code' => $lang,
    },
    {
      prefetch => {
        'category_i18ns' => 'language'
      }
    }
  );
}

sub with_products {
  my ($self, $lang) = @_;

  $self->search(
    {
      'language.code' => $lang,
    },
    {
      prefetch => {
        'products' => {
          'product_i18ns' => 'language',
        },
      },
    },
  );
}

sub with_categories {
  my ($self, $lang) = @_;

  $self->search(
    {
      'language.code' => $lang,
    },
    {
      prefetch => {
        'parent' => {
          'category_i18ns' => 'language'
        },
      },
    }
  );
}

sub with_account {
  my ($self) = @_;

  $self->search(
    undef,{
    prefetch => ['account'],
  });
}

sub display {
  my ($self) = @_;

  $self->result_class('DBIx::Class::ResultClass::HashRefInflator');

  my @return = $self->all;

  return \@return;
}

When i call the chain in such manner:

my @categories = $self->db->resultset('Category')->with_translation($lang)->with_products($lang)->display;

DBIx::Class generates SELECT query which contains only one WHERE clause:

SELECT [...] WHERE ( language.code = ? ): 'en'

what is expected according to attributes and conditions resolving rules described in DBIx::Class::ResultSet documentation. But how can i generate query with multiple WHERE clauses for each related 'language.code' column? E.g, something like this:

SELECT [...] WHERE (( language.code = ? ) AND ( language_2.code = ? )): 'en', 'en'

As far as i understand, the problem is that search conditions are being merged during the chain call so i need to retrieve current 'language' alias and use it in search condition at every chain element but seems DBIx::Class doesn't provide such ability.

Answer 1

If you change your final version to this, it should work:

 $self->db->resultset('Category')
    ->with_translation($lang)->as_subselect_rs
    ->with_products($lang)->as_subselect_rs
    ->display;

I think it will work. The problem is that it might break the prefetch, I'm not positive...

Another thing to be aware of is that you are prefetching two sets of has manys. I believe that will "Cartesian" and pull back a huge set of data. A better option may be to do 2 queries and merge the output in perl.