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
_______________________________________________
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/[email protected]/
