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

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 !

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]

DISTINCT

GROUP BY

SELECT DISTINCT

GROUP BY

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]