On May 1, 2009, at 10:49 AM, David Wall wrote:
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.
Sounds like a job for a two column table that lists manager and report.
select table1.field1, table2.field2 from table1, reports where
table1.creator_user_id = reports.peon and reports.overlord = 'bob'
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
