[Catalyst] Support distinct in search() and search_like()

[Bill Moseley]

On Mon, Nov 07, 2005 at 09:44:12PM +0000, Pedro Melo wrote:
> hi,
> 
> On Nov 7, 2005, at 5:40 PM, Bill Moseley wrote:
> >On Mon, Nov 07, 2005 at 04:56:03PM +0000, Pedro Melo wrote:
> >>I needed Class::DBI::Sweet search() to use SELECT DISTINCT in one
> >>project I'm working on.
> >
> >In trunk there's an option to pass in the name of the sql method to
> >use instead of the default "Join_Retrieve".  With this you can define
> >your custom selects in your base class (or in one of your table's
> >classes) and tell Sweet to use that instead.
> 
> Looking at the C::D::Sweet code, is not clear to me what option is 
> that. The code seems hardwired to the name Join_Retrieve.

By "trunk" I mean in svn:

http://dev.catalyst.perl.org/file/trunk/Class-DBI-Sweet/

=item sql_method

This sets the name of the sql fragment to use as previously set by a
C<set_sql> call.  The default name is "Join_Retrieve" and the associated
default sql fragment set in this class is:

    __PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
    SELECT __ESSENTIAL(me)__%s
    FROM   %s
    WHERE  %s
    SQL

You may override this in your table or base class using the same name and CDBI::Sweet
will use your custom fragment, instead.

If you need to use more than one sql fragment in a given class you may create a new
sql fragment and then specify its name using the C<sql_method> attribute.

The %s strings are replaced by sql parts as described in L<Ima::DBI>.  See
"statement_order" for the sql part that replaces each instance of %s.

In addition, the associated statment for COUNT(*) statement has "_Count"
appended to the sql_method name.  Only "from" and "where" are passed to the sprintf
function.

The default sql fragment used for "Join_Retrieve" is:

    __PACKAGE__->set_sql( Join_Retrieve_Count => <<'SQL' );
    SELECT COUNT(*)
    FROM   %s
    WHERE  %s
    SQL

If you create a custom sql method (and set the C<sql_method> attribute) then
you will likely need to also create an associated _Count fragment.  If you do
not have an associated _Count, and wish to call the C<page> method,  then set
C<disable_sql_paging> to true and your result set from the select will be spliced
to return the page you request.

Here's an example.

Assume a CD has_a Artist (and thus Artists have_many CDs), and you wish to
return a list of artists and how many CDs each have:

In package MyDB::Artist

    __PACKAGE__->columns( TEMP => 'cd_count');

    __PACKAGE__->set_sql( 'count_by_cd', <<'');
        SELECT      __ESSENTIAL(me)__, COUNT(cds.cdid) as cd_count
        FROM        %s                  -- ("from")
        WHERE       %s                  -- ("where")
        GROUP BY    __ESSENTIAL(me)__
        %s %s                           -- ("limit" and "order_by")

Then in your application code:

    my ($pager, $iterator) = MyDB::Artist->page(
        {
            'cds.title'    => { '!=', undef },
        },
        {
            sql_method          => 'count_by_cd',
            statement_order     => [qw/ from where limit order_by / ],
            disable_sql_paging  => 1,
            order_by            => 'cd_count desc',
            rows                => 10,
            page                => 1,
        } );

The above generates the following SQL:

    SELECT      me.artistid, me.name, COUNT(cds.cdid) as cd_count
    FROM        artist me, cd cds
    WHERE       ( cds.title IS NOT NULL ) AND me.artistid = cds.artist
    GROUP BY    me.artistid, me.name
    ORDER BY    cd_count desc

The one caveat is that Sweet cannot figure out the has_many joins unless you
specify them in the $criteria.  In the previous example that's done by asking
for all cd titles that are not null (which should be all).

To fetch a list like above but limited to cds that were created before the year
2000, you might do:

    my ($pager, $iterator) = MyDB::Artist->page(
        {
            'cds.year'  => { '<', 2000 },
        },
        {
            sql_method          => 'count_by_cd',
            statement_order     => [qw/ from where limit order_by / ],
            disable_sql_paging  => 1,
            order_by            => 'cd_count desc',
            rows                => 10,
            page                => 1,
        } );



> 
> Line 280 of C::D::Sweet looks like this in the original code:
> 
>     my $sth = $class->sql_Join_Retrieve( $pre_fields, $from, $sql );
> 
> So the name is hardwired. I suppose I could override sql_Join_Retrieve 
> but it would use distinct on every query, not what I want.
> 
> Of course, if Sweet or C::DBI already has this, I would use it.
> 
> Thanks,
> --
> Pedro Melo
> JID: melo at simplicidade.org
> 
> 
> _______________________________________________
> Catalyst mailing list
> Catalyst at lists.rawmode.org
> http://lists.rawmode.org/mailman/listinfo/catalyst
> 

-- 
Bill Moseley
moseley at hank.org