Catalyst, DBIx::Class, SELECT DISTINCT and TT

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am a Catalyst/DBIx::Class beginner and have trouble with custom DBIx::Class SQL queries. My database table schema is the following:

CREATE TABLE `table1` (
    `id` char(15) NOT NULL,
    `label` char(255) default NULL,
    `group` char(255) default NULL,
    PRIMARY KEY  (`id`)
)
[download]

I want to access to all distinct 'group' values within this table. So, in the Catalyst Controller, I try to do a custom DBIx::Class query:

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

In the Template:

    [% FOREACH item IN distinct_groups %]
       '[% item  %]'<br />       
    [% END %]
[download]

The result I obtain is:

    'TestApp::Schema::Database1::Table1=HASH(0x34a21a4)'
    'TestApp::Schema::Database1::Table1=HASH(0x34a2234)'
    'TestApp::Schema::Database1::Table1=HASH(0x34a7f00)'
[download]

In the table content, there are 3 distinct groups. The result seems to be consistent.
My problem is that I haven't yet found a way to access to the values of the groups. For instance, I have tried:

    [% FOREACH item IN distinct_groups %]
        '[% item.group  %]'<br />       
    [% END %]
[download]

... and I just obtain empty strings. What am I doing wrong ?

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

Replies are listed 'Best First'.
Re: Catalyst, DBIx::Class, SELECT DISTINCT and TT by CountZero (Bishop) on Apr 16, 2009 at 10:40 UTC
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	[reply] [d/l]
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
Re^5: Catalyst, DBIx::Class, SELECT DISTINCT and TT by CountZero (Bishop) on Apr 16, 2009 at 14:38 UTC