[Dbix-class] Help with Problem with Distinct

[Neil Lunn]

neil.lunn at trixan.com wrote:
>
> I write the following structure in full. The problem I am having is
> that any other combination of 'distinct' as shown in the commented parts
> throws an error. What am I missing?
>
> The SQL subselect is there
> simply because I can see no other way to implement it.
>   
Seems like the company web-mail made a right mess of my original post. 
Here it is a bit more readable.


my $rs = $schema->resultset('Inventorycategorymap')->search(
  {
    # Selection
    '-and' => [
      '-or' => [
        'c2p.parentcategoryid' => {'<>',463},
        'm2.inventorycategoryid' => 468,
      ],
      '-or' => [
        'c2p.parentcategoryid' => {'<>',716},
        'm2.inventorycategoryid' => 722,
      ],
    ],
  },
  {
    alias => 'm2',
    from  => [
      { 'm2' => 'inventorycategorymap' },
      [
        { 'myii' =>
          '(select i.id
             from inventorycategorymap icm
             join inventorycategories ic on ic.id = icm.inventorycategoryid
             join inventoryitems i on i.id = icm.inventoryitemid
             where ic.id = 715 or ic.id = 468 or ic.id = 722
             group by i.id
             having count(*) = 3)
           '
        },
        { 'myii.id' => 'm2.inventoryitemid' },
      ],
      [
        { 'c2' => 'inventorycategories' },
        { 'c2.id' => 'm2.inventorycategoryid' },
      ],
      [
        { 'c2p' => 'inventorycategories' },
        { 'c2.parentcategoryid' => 'c2p.id' }
      ],
    ],
    'select' => [ { distinct => [ 'm2.inventorycategoryid' ] } ],
    'as' => [ 'inventorycategoryid' ],
#   'select' => [ { distinct => [ 'm2.inventorycategoryid', 'c2.name' ] } ],
#   'select' => [ 'inventorycategoryid', 'c2.name' ],
#   'as'     => [ 'inventorycategoryid', 'inventorycategories.name' ],
    'order_by' => 'c2p.name, c2.name',
  },
);




>
>
>
> _______________________________________________
> 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@lists.rawmode.org
>