[Dbix-class] problem with many to many relashionship.

[Ferruccio Zamuner]

Hello,

I was trying to display a page with a report about users, their roles and so on.

my @users = $c->model('FlipperDB::Users')->search({},{prefetch=>{'user_roles'=>'role_id'},order_by=>'last_name'});
my @roles = $c->model('FlipperDB::Roles')->search({},{order_by=>'id'});
foreach my $usr (@users) {
    $form->field(name=>'roles_'.$usr->id, 
                 options=>[ map { [ $_->id, $_->role ] } @roles], 
                 value=>[ map { $_->id } $usr->roles], multiple=>1);
}

But this makes a query for each user to find its roles:

   705:SELECT me.id, me.username, me.password, me.email_address, me.first_name, me.last_name, me.active, me.level, user_roles.user_id, user_roles.role_id, role_id.id, role_id.ro\
le FROM users me LEFT JOIN user_roles user_roles ON ( user_roles.user_id = me.id )  JOIN roles role_id ON ( role_id.id = user_roles.role_id ) ORDER BY last_name, user_roles.user_\
id:
   706:SELECT me.id, me.role FROM roles me ORDER BY id:
   737:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '4'
   753:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '4'
   799:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '2'
   815:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '2'
   861:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '3'
   877:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '3'
   923:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '5'
   939:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '5'
   985:SELECT role_id.id, role_id.role FROM user_roles me  JOIN roles role_id ON ( role_id.id = me.role_id ) WHERE ( me.user_id = ? ): '1'

The Users.pm is like as the Catalyst tutorial, I've only added one line:

__PACKAGE__->many_to_many('roles' => 'user_roles', 'role_id');



I've shown this on IRC dbix-class support group and castaway has helped me:

The modified version:

foreach my $usr (@users) {
    $form->field(name=>'roles_'.$usr->id, 
                 options=>[ map { [ $_->id, $_->role ] } @roles], 
                 value=>[ map { $_->role_id->id } $usr->user_roles], multiple=>1);
}

generates following queries list:

   237:SELECT me.id, me.username, me.password, me.email_address, me.first_name, me.last_name, me.active, me.level, user_roles.user_id, user_roles.role_id, role_id.id, role_id.ro\
le FROM users me LEFT JOIN user_roles user_roles ON ( user_roles.user_id = me.id )  JOIN roles role_id ON ( role_id.id = user_roles.role_id ) ORDER BY last_name, user_roles.user_\
id:
   238:SELECT me.id, me.role FROM roles me ORDER BY id:


just those queries wanted.

The $user->roles() is used by Catalyst::Plugin::Authorization::Roles and ACL and I see a lot time the same query like:

  10:SELECT me.role FROM roles me LEFT JOIN user_roles user_roles ON ( user_roles.role_id = me.id ) WHERE ( me.role IN ( ? ) AND user_roles.user_id = ? ): 'admin', '1'
 110:SELECT me.role FROM roles me LEFT JOIN user_roles user_roles ON ( user_roles.role_id = me.id ) WHERE ( me.role IN ( ? ) AND user_roles.user_id = ? ): 'admin', '1'
1350:SELECT me.role FROM roles me LEFT JOIN user_roles user_roles ON ( user_roles.role_id = me.id ) WHERE ( me.role IN ( ? ) AND user_roles.user_id = ? ): 'admin', '1'

It's normal that each time the authorization is checked on the same request for the same user, it fires query to the db?

Castaway suggested to write down this report and I thank you castaway again.


Bye,               \fer