Is it possible to fit complex logic into one database query?

Question

MySQL

Hey!
I'm making some application for educational purposes.
The bottom line is that there is an author, the author writes a post, the post can be viewed by others and commented on.
Either everyone or certain (registered) participants united in groups can view and comment.
The task is to determine whether it is possible to show / accept comments from a specific user.
(application works via REST, so comments should be checked separately from the post itself)
Approximate database structure:
User
id | name | token
Post
id | text | authorID
Group
id | name | authorID
GroupUser
id | user ID | groupID
PostAllowGroup
id | postID | groupID
Comment
id | text | authorID | postID
Next, the algorithm is as follows:
1 we receive a request from the client with a token and postID (for comments)
2 go to User and take the id to which the token corresponds
3 go to PostAllowGroup and select those groupIDs that have our postID
4 go to GroupUser and see if there is there are fields where groupID corresponds to userID from the second paragraph
5 if everything is ok, then we give comments (s) by postID by making a JOIN c User by authorID
Is it possible to fit all this logic into one query to the database?
And will it be faster than making many simple requests?
It's just that at the moment I have enough mind for all this logic, I implement it with a chain of requests (as in an algorithm)
MySQL or Postgres database
Thank you!

Answer 1

My practice shows that it is more correct to break complex queries into several small ones with well-calculated indexes in the database. This scheme often works faster.

Answer 2

Is it possible to fit all this logic into one query to the database?

Yes

And will it be faster than making many simple requests?

Yes

Answer 3

The task is to determine whether it is possible to show / accept comments from a specific user.

This is the task of authorizing the user (determining his rights). Do not confuse it with the task of extracting data (selection). Therefore, from the point of view of architecture, it is better to make at least 2 requests: the first is authorization, the second is a selection.
It depends on the request. But, in your case, optimization is redundant, since you are making an application for educational purposes.
In general, in my opinion, a few small requests are the most suitable option for you.

Answer 4

Much depends on how the indexes are set and how you write the query. If there are a lot of rows in the tables, if the indexes are correctly built, and execution speed is important, and the visibility of the code itself is not needed (I still don’t understand - the query itself is also for educational purposes or just the result of the program), then it’s better to write in one query, of course it is necessary to "polish" it (or to alter request looking at result or to look "query plan"). And if there are few lines and then someone will look / edit your code, then it is better to write small requests with comments.