I have two tables: room and with_access_room
id | name | access_level | image_id --- room user_id | room_id | token --- with_access_room
There are 3 type of rooms:
- Public: everyone can see it (doesn't matter registered or not)
- Private: only registered users see it
- Private with access: only registered user can create and nobody can see it except creator which gets special token to invite other users.
Now in main page I have to show my rooms. Consider I have a user with id = 1. So he can see all public, private (without access token needed) and private rooms (with access) created by him.
In access_level column 0 is public and 1 is private.
id | name | access_level | image_id 1 | aaa | 1 | 1 2 | bbb | 0 | 2 3 | ccc | 1 | 3 4 | ddd | 1 | 4
user_id | room_id | token 1 | 3 | xyz 2 | 4 | zyx
So my user with id = 1 must see these "table"
ID | name | access_level | image_id | token 1 | aaa | 1 | 1 | NULL 2 | bbb | 0 | 2 | NULL 3 | ccc | 1 | 3 | xyz
I am working with Yii 2
Which SQL query or Yii 2 model method with params can give me desired result ?
I don't think you have described enough information to represent the problem. Missing information:
- How is "Private with access" represented in the data?
- Where is the "creator" represented?
- How do we know who is registered?
I can guess reasonable answers to these questions. In the end, the query will look something like this:
select r.* from room r where access_level = 0 or (access_level = 1 and 1 in (select user_id from users u where is_registered = 1) ) or (access_level = 2 and 1 in (select user_id from with_access_room war where war.room_id = r.id) );
This assumes that the creator has a row in