Re: Catalyst, DBIx::Class, SELECT DISTINCT and TT

Can you try this:

sub test_distinct :Local {
    $c->stash->{distinct_groups} = [map {$_->group} (
        $c->model('Database1')->resultset('Table1')->search(
            undef,
            {
                select   => ['group'],
                order_by => ['group'],
            },
        )
    )];
}
[download]

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Comment on Re: Catalyst, DBIx::Class, SELECT DISTINCT and TT Download Code

Replies are listed 'Best First'.
Re^2: Catalyst, DBIx::Class, SELECT DISTINCT and TT by Anonymous Monk on Apr 16, 2009 at 11:20 UTC
This code works. I can even ommit the: `map {$_->group}` [download] from the Perl code and add: `[% item.group %]` [download] in the Template Toolkit code. The initial problem is still there though : what you propose does not generate a SELECT DISTINCT clause !!!	[reply] [d/l] [select]
Re^3: Catalyst, DBIx::Class, SELECT DISTINCT and TT by CountZero (Bishop) on Apr 16, 2009 at 11:40 UTC
You do not need a `SELECT DISTINCT` as you already have a `GROUP BY` clause on the same field. Choose either one or the other, the results should be the same. To use the `SELECT DISTINCT` change your DBIx::Class search method as follows: `$c->model('Database1')->resultset('Table1')->search( undef, { select => ['group'], distinct => 1, }, )` [download] Update: added example of "distinct". CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re^4: Catalyst, DBIx::Class, SELECT DISTINCT and TT by Anonymous Monk on Apr 16, 2009 at 13:57 UTC
It works ! Thank you ! By the way, I tried to better understand why you mentioned there was a 'GROUP BY' statement that was being applied. I just understood that this code: `$c->model('Database1')->resultset('Table1')->search( undef, { select => ['group'], distinct => 1, order_by => ['group'] }, )` [download] generates this SQL, featuring a GROUP BY: `SELECT group FROM table1 me GROUP BY group ORDER BY group` [download] For me, this feels strange. Writting `distinct => 1` [download] in the query adds a GROUP BY statement. I just found the semantics of 'distinct' in the DBIx::Class::ResultSet documentation but, still, this is counter-intuitive in my opinion. So, thanks for pointing this out... ;-) Now, this code: `$c->model('Database1')->resultset('Table1')->search( undef, { select => [ { distinct => 'group' }, ], as => 'group', order_by => ['group'] }, )` [download] generates this SQL: `SELECT DISTINCT( group ) FROM table1 me ORDER BY group` [download] This second code is more like what I was looking for... Apparently the trick was to add a `as => '...'` [download] clause in the query... Thanks again !	[reply] [d/l] [select]
Re^5: Catalyst, DBIx::Class, SELECT DISTINCT and TT by CountZero (Bishop) on Apr 16, 2009 at 14:38 UTC