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]

In reply to DBIx::Class - Count with prefetch creates nested query by stepamil

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.