Re: DBIx::Class - Count with prefetch creates nested query

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).

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

Replies are listed 'Best First'.
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]


There's more than one way to do things
	PerlMonks