DBIx::Class - Count with prefetch creates nested query

stepamil has asked for the wisdom of the Perl Monks concerning the following question:

Hi PerlMonks,

I'm using DBIx::Class 0.082820 on Perl 5.10.1 and I have a situation when I'm trying to do count on table which is prefetched to another. Here's an example:

my $count = Core::Models->resultset('Shipment')->search(
  undef,
  { prefetch => 'rma' }
)->count();
[download]

This creates this query:

SELECT COUNT( * ) FROM (SELECT me.id FROM shipment me GROUP BY me.id) me

And logically it should be this:

SELECT COUNT( * ) FROM shipment me GROUP BY me.id

Maybe I'm missing something. The real usecase is very complicated, but I've simplified it.

Can you please help?

Update (I've added the real models):

package Models::Result::Shipment;

use strict;

use base 'DBIx::Class::Core';

__PACKAGE__->table("shipment");

__PACKAGE__->add_columns(
  "id",
  { 
    accesor => 'id', data_type => "integer", 
    extra => { unsigned => 1 }, is_nullable => 0, 
    is_auto_increment => 1 
  },
  "ref",
  { 
    accesor => 'reference', data_type => "varchar", 
    is_nullable => 1, size => 255 
  },
  "api_merchant_id",
  { 
    data_type => "integer", extra => { unsigned => 1 }, 
    is_nullable => 0, default_value => 1 
  },
  # ... lot of other fields bellow
);

__PACKAGE__->set_primary_key("id");

__PACKAGE__->has_many(
  "rma",
  "Models::Result::RMA",
  {
    "foreign.shipment_reference" => "self.ref",
    "foreign.merchant_id"    => "self.merchant_id",
  },
  { cascade_delete => 0 }
);

1;

package Models::Result::RMA;

use strict;

use base 'DBIx::Class::Core';

__PACKAGE__->table('api_rma');

__PACKAGE__->add_columns(
    'id'                  => { data_type => 'integer', is_nullable => 
+0, extra => { unsigned => 1 } },
    'shipment_reference'  => { data_type => 'varchar', is_nullable => 
+0, size => 255 },
    'merchant_id'     => { data_type => "integer", extra => { unsigned
+ => 1 }, is_nullable => 0 },
    # ... lot of other fields bellow
);

__PACKAGE__->set_primary_key('id');

1;
[download]

UPDATE 2: Here's what I have for now

$count = Core::Models->resultset('Shipment')->search(
  undef,
  { 
    prefetch => 'rma',
    select => { count => 'DISTINCT(me.id)' },
    as => [ 'total_count' ],
  }
)->get_column('total_count')->first();
[download]

Comment on DBIx::Class - Count with prefetch creates nested query Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class - Count with prefetch creates nested query by Myrddin Wyllt (Hermit) on Jul 06, 2015 at 18:09 UTC
I think the sub-select is in there because you prefetch 'rma'. You are not using it in the 'where' clause, so it is pretty much superfluous to the returned value; you are going to get a count of all 'shipment' records whether or not they have any associated 'rma' records. If that's what you want, just re-write the query as : `my $count = Core::Models->resultset('Shipment')->search( undef )->count();` [download] Which gives the obvious `SELECT COUNT( * ) FROM shipment me` If you need a count of all shipments which have at least one 'rma' record, you need to re-write your query as: `my $count = Core::Models->resultset('Shipment')->search( { 'rma.id' => {'!=' => undef} }, { prefetch => 'rma' } )->count();` [download] Which gives SQL of: `SELECT COUNT( * ) FROM (SELECT me.id FROM shipment me LEFT JOIN rma rm +a ON ( rma.shipment_reference = me.ref AND rma.merchant_id = me.api_ +merchant_id ) WHERE ( rma.id IS NOT NULL ) GROUP BY me.id) me` [download] I think DBIC puts the sub-select in your case because it expects the 'rma' prefetch to be used in the 'where' clause, and thinks it will be using a LEFT JOIN (which isn't unreasonable).	[reply] [d/l] [select]
Re^2: DBIx::Class - Count with prefetch creates nested query by stepamil (Acolyte) on Jul 07, 2015 at 07:30 UTC
Thank you for your reply. In the end I will check in the code if prefetch is needed - if yes then use it - if not, just go without prefetching. Interesting to note is that when I switch "prefetch" with "join" it does the LEFT JOIN in all cases	[reply]
Re: DBIx::Class - Count with prefetch creates nested query by 1nickt (Canon) on Jul 06, 2015 at 11:50 UTC
Maybe I'm missing something. The real usecase is very complicated, but I've simplified it. Can you do that with your code? Remember: Ne dederis in spiritu molere illegitimi!	[reply] [d/l]
Re^2: DBIx::Class - Count with prefetch creates nested query by stepamil (Acolyte) on Jul 06, 2015 at 12:18 UTC
Hi, I've updated the code with real example	[reply]
Re^3: DBIx::Class - Count with prefetch creates nested query by 1nickt (Canon) on Jul 06, 2015 at 14:46 UTC
Hi, I was really suggesting that you should simplify your actual code ... if it makes you say "it's very complicated" when you try to explain it or post some of it to get help, there is probably room to simplify it, likely by breaking it up into smaller chunks. That's what I meant. But looking at what you posted, I see an error in your calls to DBIx::Class::ResultSource::add_columns, in the misspelling of the accessor param ... a clue, maybe? Remember: Ne dederis in spiritu molere illegitimi!	[reply] [d/l]