[Dbix-class] Caching of related resultsets

[Bernhard Graf]

On Friday 06 October 2006 18:42, Bernhard Graf wrote:
> I have a user - roles setup similar to
> Catalyst::Manual::Tutorial::Authentication.
>
> My application quite often queries roles for the authenticated user
> ($c->user->obj) within one HTTP query.
>
> For this purpose I defined a method in the DBIC user class like this:
>
> sub has_role {
>     my ($self, $name) = @_;
>     $self->roles->find($role);
> }
>
> To save db access I'd prefer caching $user->roles->all inside the
> user object ($c->user->obj) in the first call of has_role() and
> access roles in subsequent calls to has_role() from this cache.
>
> Is this possible?

Meanwhile I can answer my own question.
DBIx::Class::Manual::FAQ took me on the right track:

    How do I store my own (non-db) data in my DBIx::Class objects? 
	You can add your own data accessors to your classes.

The resulting code looks like this:

(Note: My roles table has only one column "id" that is primary key and
       contains the profile name)

package MyApp::DB::User;

use base 'DBIx::Class';

__PACKAGE__->mk_classaccessor('role_ids_cache');

[...]

sub role_ids {
    my $self = shift;
    return $self->role_ids_cache([map { $_->id} $self->roles->all])
        unless $self->role_ids_cache;
    $self->role_ids_cache;
}

sub has_role {
    my ($self, $name) = @_;
    foreach my $role (@{$self->role_ids}) {
        return 1 if $role eq $name;
    }
    return;
}

Now only the first call to $user->has_role($name) (or $user->role_ids()) 
does an SQL query, subsequent calls fetch from cache.

Note 2: I started with

__PACKAGE__->mk_classaccessor('role_ids');

sub role_ids {
    my $self = shift;
    return $self->_role_ids_accessor([map { $_->id} $self->roles->all])
        unless $self->_role_ids_accessor;
    $self->_role_ids_accessor;
}

sub has_role {
...

but for some reason, that I don't understand my own sub role_ids never 
got called.

-- 
Bernhard Graf