view release on metacpan or  search on metacpan

lib/Class/DBI/Sweet.pm  view on Meta::CPAN

    { offset => 0 }

=item page

Specifies the current page in C<page>. Defaults to 1 if unspecified.

    { page => 1 }

=item prefetch

Specifies a listref of relationships to prefetch. These must be has_a or
might_haves or Sweet will throw an error. This will cause Sweet to do
a join across to the related tables in order to return the related object
without a second trip to the database. All 'Essential' columns of the
foreign table are retrieved.

    { prefetch => [ qw/some_rel some_other_rel/ ] }

Sweet constructs the joined SQL statement by aliasing the columns in
each table and prefixing the column name with 'sweet__N_' where N is a
counter starting at 1.  Note that if your database has a column length limit 
(for example, Oracle's limit is 30) and you use long column names in
your application, Sweet's addition of at least 9 extra characters to your
column name may cause database errors.

=item use_resultset_cache

Enables the resultset cache. This is a little experimental and massive gotchas
may rear their ugly head at some stage, but it does seem to work pretty well.

For best results, the resultset cache should only be used selectively on
queries where you experience performance problems.  Enabling it for every
single query in your application will most likely cause a drop in performance
as the cache overhead is greater than simply fetching the data from the
database.

=item profile_cache

Records cache hits/misses and what keys they were for in ->profiling_data.
Note that this is class metadata so if you don't want it to be global for
Sweet you need to do

    __PACKAGE__->profiling_data({ });

in either your base class or your table classes to taste.

=item disable_sql_paging

Disables the use of paging in SQL statements if set, forcing Sweet to emulate
paging by slicing the iterator at the end of ->search (which it normally only
uses as a fallback mechanism). Useful for testing or for causing the entire
query to be retrieved initially when the resultset cache is used.

This is also useful when using custom SQL via C<set_sql> and setting
C<sql_method> (see below) where a COUNT(*) may not make sense (i.e. when
the COUNT(*) might be as expensive as just running the full query and just slicing
the iterator).

=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,
        } );


=item statement_order

Specifies a list reference of SQL parts that are replaced in the SQL fragment (which
is defined with "sql_method" above).  The available SQL parts are:

    prefetch_cols from where order_by limit sql prefetch_names

The "sql" part is shortcut notation for these three combined:

    where order_by limit

Prefecch_cols are the columns selected when a prefetch is speccified -- use in the SELECT.
Prefetch_names are just the column names for use in GROUP BY.

This is useful when statement order needs to be changed, such as when using a
GROUP BY:

=back

=head2 count

Returns a count of the number of rows matching the criteria. C<count> will
discard C<offset>, C<order_by>, and C<rows>.

    $count = MyApp::Article->count(%criteria);

=head2 search

Returns an iterator in scalar context, or an array of objects in list
context.

    @objects  = MyApp::Article->search(%criteria);

    $iterator = MyApp::Article->search(%criteria);

=head2 search_like

As search but adds the attribute { cmp => 'like' }.

=head2 page

Retuns a page object and an iterator. The page object is an instance of
L<Data::Page>.

    ( $page, $iterator )
        = MyApp::Article->page( $criteria, { rows => 10, page => 2 );

    printf( "Results %d - %d of %d Found\n",
        $page->first, $page->last, $page->total_entries );

=head2 pager

An alias to page.

=head2 retrieve_all

Same as C<Class::DBI> with addition that it takes C<attributes> as arguments,
C<attributes> can be a hash or a hashref.

    $iterator = MyApp::Article->retrieve_all( order_by => 'created_on' );

=head2 retrieve_next