Can anyone think of better logic for this query.
I have three tables, KBAuth, KBCategory and Groups.....the KBAuth table has the following columns:
kba_grp_id_fk
kba_cat_id_fk
....and basically specifies user restrictions to categories so if a row was:
1
1
....and I was in group one and tried to access the category with ID 1 it would block me.
KBCategory stores the data for each category such as the id, name etc. And finally the Groups table just stores the group id and name.
So, on the main page I want to select all categories with a parent id of NULL but _not_ list categories where the user is blocked. So I have this which works:
my $sth = $DB->table('KBCategory')->select($cond);
while (my $cat = $sth->fetchrow_hashref) {
# Hide the category if the user doesn't have access :)
next if ($DB->table('KBAuth')->count( { kba_cat_id_fk => $cat->{cat_id}, kba_grp_id_fk => $USER->{user_status} } ));
push @loop, $cat;
}
....as you can probably see, I'm selecting the categories and then doing a count on the KBAuth table to see if the category id is listed as being blocked for this user id.
The thing that is bothering me is that if I have 25 top level categories then the code as it is will be doing 25 count queries.
I was wondering if there was a way to make it so there were less queries?
I have three tables, KBAuth, KBCategory and Groups.....the KBAuth table has the following columns:
kba_grp_id_fk
kba_cat_id_fk
....and basically specifies user restrictions to categories so if a row was:
1
1
....and I was in group one and tried to access the category with ID 1 it would block me.
KBCategory stores the data for each category such as the id, name etc. And finally the Groups table just stores the group id and name.
So, on the main page I want to select all categories with a parent id of NULL but _not_ list categories where the user is blocked. So I have this which works:
Code:
my $cond = WO::SQL::Condition->new('cat_parent_id', 'IS', \'NULL'); my $sth = $DB->table('KBCategory')->select($cond);
while (my $cat = $sth->fetchrow_hashref) {
# Hide the category if the user doesn't have access :)
next if ($DB->table('KBAuth')->count( { kba_cat_id_fk => $cat->{cat_id}, kba_grp_id_fk => $USER->{user_status} } ));
push @loop, $cat;
}
....as you can probably see, I'm selecting the categories and then doing a count on the KBAuth table to see if the category id is listed as being blocked for this user id.
The thing that is bothering me is that if I have 25 top level categories then the code as it is will be doing 25 count queries.
I was wondering if there was a way to make it so there were less queries?
