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

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

Comment on Re^3: Catalyst, DBIx::Class, SELECT DISTINCT and TT Select or Download Code

Replies are listed 'Best First'.
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
`SELECT DISTINCT( group ) FROM table1 me ORDER BY group` [download] is not really good SQL. `DISTINCT` is not a function, so you should have no parentheses following it. It happens to work in this case, but it is to be avoided and anyhow the `GROUP BY` solution is more "canonical". MySQL for instance optimizes `SELECT DISTINCT` into `GROUP BY` internally. Also `GROUP BY` is more flexible, whereas `SELECT DISTINCT` is an "all or nothing" thing: it applies to all fields in your `SELECT` statement. 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]