[Dbix-class] DBIx and persistence or object caching?

[Steve Francia]

Since all doesn't take any parameters, but search does you can do what I am asking for with search.

Add the prefetch to search while searching for nothing and then apply the all. 

$c->model('vidDB::video')->search( {}, { prefetch => [qw/ owner /] } )->all();

Seems like a roundabout approach, is there a better way to do it? 
Is there any reason the all method doesn't accept parameters?



-----Original Message-----
From: Steve Francia <steve at takkle.com>
Sent: Wed, May 23, 2007 3:17 pm
To: dbix-class at lists.rawmode.org
Subject: [Dbix-class] DBIx and persistence or object caching?

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/I am new to catalyst and DBIx. I am writing a small app that currently
has two tables and one relationship.

In running :
$c->model('vidDB::video')->all

Certain SQL queries are run multiple times:

    SELECT me.video_id, me.title, me.rating, me.owner_id, me.length,
    me.cr_date, me.location FROM video me:
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '1'
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '3'
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '2'
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '1'
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '2'
    SELECT me.user_id, me.username, me.password, me.first_name,
    me.last_name FROM user me WHERE ( ( ( me.user_id = ? ) ) ): '1'

Obviously this is not ideal. I have been searching the web and reading
the manuals, but so far I haven't found a way to turn on persistence.
The multiple queries are a result of one user "owning" multiple videos,
but I would think it could keep that information in the object so it
doesn't need to query the database again, at least during the same page
request. Can anyone direct me in how to do this? Ideally I would like to
use mem cache for this, but any cache would be better than repeated queries.

video is defined as :

    # Load required DBIC stuff
    __PACKAGE__->load_components(qw/PK::Auto Core/);
    # Set the table name
    __PACKAGE__->table('video');
    # Set columns in table
    __PACKAGE__->add_columns(qw/video_id title rating owner_id length
    cr_date location/);
    # Set the primary key for the table
    __PACKAGE__->set_primary_key(qw/video_id/);

    #
    # Set relationships:
    #

    # has_one():
    #   args:
    #     1) Name of relationship, DBIC will create accessor with this name
    #     2) Name of the model class referenced by this relationship
    #     3) Column name in *foreign* table
    __PACKAGE__->has_one(owner => 'vidDB::user', { 'foreign.user_id' =>
    'self.owner_id' } );


and user is defined as :

    # Load required DBIC stuff
    __PACKAGE__->load_components(qw/PK::Auto Core/);
    # Set the table name
    __PACKAGE__->table('user');
    # Set columns in table
    __PACKAGE__->add_columns(qw/user_id username password first_name
    last_name/);
    # Set the primary key for the table
    __PACKAGE__->set_primary_key(qw/user_id/);

    #
    # Set relationships:
    #

    # has_many():
    #   args:
    #     1) Name of relationship, DBIC will create accessor with this name
    #     2) Name of the model class referenced by this relationship
    #     3) Column name in *foreign* table
    __PACKAGE__->belongs_to(video => 'vidDB::video', 'user_id');

Thanks.