I'm working on a rights verification system. In order to verify that the user has access to the documents, I make a request to the database There are 2 cases when you can get access:

1. The user is in the access zone of this document (for example, a user in the chat where the document was published)

select count(*) > 0 from Document 
   left join Chat
   left join ChatUser
   left join User
   left join Resource
   ...
where ...

2. The document is public

select count(*) > 0 from Document 
   left join User
   left join Resource
   left join ...
   ...
where ...

I see 2 solutions

1. Make a request covering both cases (union)

pros of such a solution -> 1 request to the database

cons of such a solution -> every time in 70% of cases I make an extra 5 joins, which can hit the performance of the query in the database

2. Make 2 requests

First make a request whether the document is in the chat.

If false -> then make 1 more request

Tell me what algorithm is used in such cases?

CodePudding user response：

If you have one-to-one relation then join queries should be used.

Join queries with one-to-many relation will lead to more memory usage with redundant data.

If you don't have memory usage issue then also you should use join queries because in most of the cases, join queries are faster than multiple queries.