Select only fields from a table, that are public

Question

I want to implement a little "rights" management and only show fields in a resultset, that are set to public. The name of the field and if its public or private is stored in a 2nd table.

"select user.id, user.firstname, user.lastname, user.email 
 from users where (user.id = '30')"

Now the table rights would look like

id / user_id / fieldname / type
1 / 30 / users.lastname / private
2 / 30 / users.firstname / public
3 / 30 / users.eMail / private
4 / 31 / users.lastname / private 
(...)

Is there a way to put this in one query ? My other option is to run a query and loop through with PHP which I dont think is very nice.

I have something like this in mind:

Select users.firstname if (select typ from rights where user_id = users.id and fieldname =     
'users.firstname') = 'public', users.lastname if (select typ from rights where user_id = 
users.id and fieldname = 'users.lastname') = 'public'

So the result will be the whole row, but the fields that are set to private will contain no data.

http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join all about joining — jahanvi Kashyap, May 18 '14 at 09:24

score 1 · Answer 1 · answered May 18 '14 at 09:22

1

JOIN the tables:

SELECT U.id, U.firstname, U.lastname, U.email 
FROM users U JOIN
     rights R ON U.id=R.user_id
WHERE R.type='public'

Read more about JOINs here.

If you want to check the user_id:

SELECT U.id, U.firstname, U.lastname, U.email 
FROM users U JOIN
     rights R ON U.id=R.user_id
WHERE R.type='public'
  AND U.id=30

answered May 18 '14 at 09:22

Raging Bull

18,593
13
50
55

Thanks, I tried this but now it will skip the whole row in case that theres just one private field. I just want fields to be null that are set to private. In this case, the query should i.e. still return the firstname but lastname should be null. – user2942586 May 18 '14 at 09:44
@user2942586: Try with `LEFT JOIN` instead of `JOIN`. – Raging Bull May 18 '14 at 09:46
This seems to invert the behaviour. I always get the whole result row or nothing :( – user2942586 May 18 '14 at 09:49
@user2942586: If you want to select all records from rights, you can use `RIGHT JOIN`. – Raging Bull May 18 '14 at 09:50
I dont get how mysql should know which field is private or not. There misses a relation between the fieldnames – user2942586 May 18 '14 at 09:50
@user2942586: Can you show an example of what you are after? – Raging Bull May 18 '14 at 09:51
I try to express it with pseudo code: SELECT U.id, U.firstname, U.lastname only if (rights.fieldname "users.lastname" = "public"), U.email (...) I want to set specific columns to null in my resultrow when the field is set to private. – user2942586 May 18 '14 at 09:58
Its propably just not possible – user2942586 May 18 '14 at 09:58
@user2942586: You want to join the tables with lastname rather than user id? – Raging Bull May 18 '14 at 10:00
I want the following result in case in the rights table the fieldname="field.username" for user 1 is set to public: 1 / myfirstname / mylastname in case its set to private, the result shall be 1 / myfirstname / null – user2942586 May 18 '14 at 10:02
Something like: Select users.firstname if (select typ from rights where user_id = users.id and fieldname = 'users.firstname') = 'public', users.lastname if (select typ from rights where user_id = users.id and fieldname = 'users.lastname') = 'public' – user2942586 May 18 '14 at 10:10
@user2942586: Please edit your question and add this to it. Commenting the required result makes it hard to read. – Raging Bull May 18 '14 at 10:13
Thanks for helping me to get to the solution. I propably explained it badly in my first approach. – user2942586 May 18 '14 at 10:31

Ryx5 · Accepted Answer · 2014-05-18T11:58:42.390

1

I read a bit your comments, you can do this :

SELECT U.id
    , U.firstname
    , CASE WHEN R.type IS NOT NULL THEN U.lastname ELSE NULL END as lastname
    , U.email 
FROM users U 
    LEFT JOIN rights R 
        ON U.id=R.user_id
        AND R.type='public'

EDIT According to last question update

SELECT U.id
    , CASE WHEN RFirstName.user_id IS NOT NULL THEN U.firstname ELSE NULL END as firstname
    , CASE WHEN RLastName.user_id IS NOT NULL THEN U.lastname ELSE NULL END as lastname
    , CASE WHEN REmail.user_id IS NOT NULL THEN U.email ELSE NULL END as email 
FROM users U 
    LEFT JOIN rights RFirstName
        ON U.id=RFirstName.user_id
        AND RFirstName.type='public'
        AND RFirstName.fieldname = 'users.firstname'
    LEFT JOIN rights RLastName
        ON U.id=RLastName.user_id
        AND RLastName.type='public'
        AND RLastName.fieldname = 'users.lastname'
    LEFT JOIN rights REmail
        ON U.id=REmail.user_id
        AND REmail.type='public'
        AND REmail.fieldname = 'users.eMail'

edited May 18 '14 at 11:58

answered May 18 '14 at 10:13

Ryx5

1,366
8
10

Not quite actually. What happens, when I want to do the same with the firstname and eMail field ? All fields might have different settings. – user2942586 May 18 '14 at 10:42
You can apply all your settings using `CASE WHEN .. THEN .. ELSE .. END`, and if you have different rules "public/hidden/etc" you can add some left join to ur rights table. Or you can remove R.type='public' and specify it in `CASE WHEN R.type = 'public' THEN lastname ELSE NULL END` and do the "same" for other rules – Ryx5 May 18 '14 at 10:46
Yes, but it must lookup R.type for each field. The rights table has one row for each fieldname for each user. So the right for users.lastname in example would be stored in one own row in the rights table. Currently, there just needs to be one row in the rights table which has a field set to public, then everything would be public. Ill update the question with more examples. – user2942586 May 18 '14 at 11:07
Edited the answer, hope it will help. – Ryx5 May 18 '14 at 12:04
Yes, that it. Thank you again! I am wondering how the performance will be when I fill in a couple of million testdatasets :) – user2942586 May 18 '14 at 12:30
3 left join like this is not performance killer, but you should redesign your database logic if this is more huge than you showed up :D – Ryx5 May 18 '14 at 13:06

Select only fields from a table, that are public

2 Answers2