how to select db records most efficiently ?

[adrianmak]

Let say there are three db table
one table is storing raw data of each channel where channel wth fields id, name, rawdata

another table is user table with fields, id, username, password

the last table is user perms with fields, id, access, which store a user could access what channel of the channel table

sample table of user perms may look like this

id,access
1,"1,2,5,8,9,10"
2,"3,4,7,11"
3,"10,12,18,19,21,"

id is referenced to the id of user table id, and access is referenced to the channel table id which a user could access

how do I , from the access field to select those channel ids from channel table ?

after extracted the id from access fields on by on, a query may look like

where cid=1 or cid=2 or cid=5 or cid=8 or cid=9 or cid=10

It may work but I'm wondering it is not quite efficient

 

[LostKobrakai]

Take an example by looking at multi value fields in processwire for your perms table:

user_id,channel_id
1,1
1,2
1,5
…
2,3
2,4
2,7
…

This allows you to join users and channels together by permissions. No need to parse any strings. This is even compatible to foreign key usage.

And as this is a still a processwire forum: Why not model it by using pages