php - Get room for each user in chat room application considering users access level
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:
I can guess reasonable answers to these questions. In the end, the query will look something like this:
This assumes that the creator has a row in
This query should work: