Create MySQL hierarchical recursive query

Question

I have a blog with pages of two types:

Opened pages - everyone can view the page
Closed pages - only followers of the page can view the posts

A follower of a page can be a single user or a group of users. A group can contain single users or a group of users.

How can I query my database in a way that I know if user X can view post Y?

My database is a MySql DB, and I have these tables:

1 entities

  id : type : time_created
  1       1            ...
  2       1            ...
  3       2            ...
  4       2            ...
  5       3            ...
  6       3            ...
  7       4            ...
  8       4            ...
  9       3            ...

2 types

 id : value
  1   post
  2   user
  3   group
  4   page

3 pages

 id : page_id : is_closed : title
  1         7   false       "mordi"
  1         8   true        "cool"

4 entity_relationships

  id : id_one : relationship : id_two
   1        1   "part_of"          7
   2        2   "part_of"          8
   3        3   "member_of"        5
   4         5  "member_of"        6
   5         6  "member_of"        9
   6         9  "follower_of"      8

I need to find that user with entity id 3 can view post with entity id 2 because 3 if a member of group 5 which is a member of 6 which is a member of 6 which is a member of 9 which is a follower of page 8 (a closed page) which post 2 is part of..

Thank you!

I think You are looking for Recursive Common Table Expressions. — Michas, Jun 15 '18 at 10:50
If you have MysQL 8.0+ you can use a recursive CTE otherwise https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Raymond Nijland, Jun 15 '18 at 10:51
But your reference is a pretty simple example for this problem.. I have a more complex query I think — mordicool, Jun 15 '18 at 10:57
"But your reference is a pretty simple example for this problem.. I have a more complex query I think " Try to understand the simple example and extend it so it works for your problem. — Raymond Nijland, Jun 15 '18 at 10:59
I think I'll leave this question as unsolved, so if indeed someone would be able to extend this query.. again, Thanks! — mordicool, Jun 15 '18 at 11:10

Create MySQL hierarchical recursive query

0 Answers0