[Dbix-class] A count() after a grouping select() produces a "Duplicate column" error

[Robert Stone]

Greetings,

I'm happy to take a stab at it!  If anyone sees anything I'm missing please
feel free to add your thoughts.

Looking at the generated SQL:

SELECT
  COUNT( * )
  FROM (
      SELECT
        me.id
        ,me.valid_from
        ,me.valid_till
        ,me.removed
        ,me.provisioning_agreement_id
        ,me.resource_piece_id
        ,me.service_type_id
        ,me.service_level_id
*        ,me.quantity*
*        ,me.quantity*
        FROM provisioning_obligation me
        GROUP BY quantity
  ) me

We can see that the quantity column is appearing twice (which is what is
generating that Duplicate column name, because the derived table has the
same column twice so it can't be "fed into" the SELECT COUNT(*) ).  Is it
possible you copy pasted the quantity definition in the result source an
extra time?  Alternatively, how are you generating the $resultset you are
using, is it possible you are tacking on an extra quantity then, or are you
querying the provisioning_obligation result source directly?

More importantly though, I'm curious as to what information you are
actually trying to get.  You want a distinct count of quantities from the
provisioning_obligation table?  I'm not sure the utility of that
information, but a better way to answer that question would be to use a
SELECT COUNT( DISTINCT quantity ), there is a great example in the docs at:

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#SELECT_COUNT(DISTINCT_colname)

Hopefully you find this helpful.

Best Regards,
Robert Stone


On Tue, Oct 10, 2017 at 6:20 AM, Vladimir Melnik <v.melnik at uplink.ua> wrote:

> Dear colleagues,
>
> Here's a little snippet of the code:
>         $resultset
>             ->search(undef, { group_by => [ qw(quantity) ] })
>             ->count;
>
> It produces the following exception:
> DBI Exception: DBD::mysql::st execute failed: Duplicate column name
> 'quantity' [for Statement "SELECT COUNT( * ) FROM (SELECT me.id,
> me.valid_from, me.valid_till, me.removed, me.provisioning_agreement_id,
> me.resource_piece_id, me.service_type_id, me.service_level_id, me.quantity,
> me.quantity FROM provisioning_obligation me GROUP BY quantity) me"] at
> /home/mmkeeper/perl5/perlbrew/perls/perl-5.24.0/lib/site_
> perl/5.24.0/DBIx/Class/Schema.pm line 1118.
>
> What is the best way to avoid that?
>
> Thanks in advance!
>
> --
> V.Melnik
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/
> dbix-class at lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20171010/1be17df9/attachment.htm>