Access each user in the chat room application taking into account the level of user access

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.

For example:

room

id   |   name  |  access_level  |  image_id
1    |   aaa   |      1         |     1
2    |   bbb   |      0         |     2
3    |   ccc   |      1         |     3
4    |   ddd   |      1         |     4

with_access_room

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 with_access_room.