FAQ
Hi,

I needed Class::DBI::Sweet search() to use SELECT DISTINCT in one
project I'm working on.

Attached it's a patch that adds a new search attribute, distinct, that
forces the distinct keyword.

I'm using it like this:

return EFO::M::CDBI::Cursos->search({
'estado' => 3,
'events.formador_id' => $self->id,
'events.data_inicio' => \'>= now()',
},
{
order_by => 'nome',
distinct => 1,
}
);

I don't know if other people need this, but it was very useful to me,
specially when your search uses other joined tables via a one-to-many
relationship.

thanks
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sweet.diff
Type: application/octet-stream
Size: 802 bytes
Desc: not available
Url : http://lists.rawmode.org/pipermail/catalyst/attachments/20051107/e1a8534e/sweet.obj
-------------- next part --------------

--
Pedro Melo
JID: melo@simplicidade.org

Search Discussions

  • Bill Moseley at Nov 7, 2005 at 6:34 pm

    On Mon, Nov 07, 2005 at 04:56:03PM +0000, Pedro Melo wrote:
    Hi,

    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.


    --
    Bill Moseley
    moseley@hank.org
  • Pedro Melo at Nov 7, 2005 at 10:39 pm
    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.

    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@simplicidade.org
  • Bill Moseley at Nov 7, 2005 at 11:29 pm

    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@simplicidade.org


    _______________________________________________
    Catalyst mailing list
    Catalyst@lists.rawmode.org
    http://lists.rawmode.org/mailman/listinfo/catalyst
    --
    Bill Moseley
    moseley@hank.org

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedNov 7, '05 at 5:51p
activeNov 7, '05 at 11:29p
posts4
users2
websitecatalystframework.org
irc#catalyst

2 users in discussion

Pedro Melo: 2 posts Bill Moseley: 2 posts

People

Translate

site design / logo © 2022 Grokbase