A separate table for managing the
relationships. One column for the manager and one for employee.
Then you end up with a query like this.
Select field1,field2 FROM table1 inner
join relationships on table1.creator_user_id = relationships.employee WHERE
relationships.manager = ?
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On
Behalf Of David Wall
Sent: Friday, May 01, 2009 12:49
PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Handling large
number of OR/IN conditions
We have a database report function that seemed clean
when the number of users was small, but as the number of users grow, I was
wondering if anybody had any good ideas about how to handle OR or IN for
SELECTs.
The general scenario is that a manager runs reports that list all records that
were created by users under his/her oversight. So, when the number of
users is small, we had simple queries like:
SELECT
field1, field2 FROM table1 WHERE creator_user_id = 'U1' OR creator_user_id =
'U2';
But when there are thousands of users, and a manager has oversight of 100 of
them, the OR construct seems out of whack when you read the query:
WHERE creator_user_id = 'U1' OR creator_user_id =
'U2' ... OR creator_user_id =
'U99' OR creator_user_id = 'U100'
I know it can be shortened with IN using something like, but don't know if it's
any more/less efficient or a concern:
WHERE creator_user_id IN ('U1', 'U2', ...., 'U99', 'U100)
How do people tend to handle this sort of thing? I suspect manager reports
against their people must be pretty common. Are there any good tricks on
how to group users like this? Unfortunately, group membership changes
over time, and users may report to more than one manager and thus belong to
more than one group, so we can't just have a 'creator_group_id' attribute that
is set and then query against that.
Thanks,
David
