There is an API server on node.js express mysql.

The database consists of 4 tables:

твиты, комменты к твиту, хэштеги, твит_хэштег (many-to-many)

Imagine that the client went to the main page, with the client flies a request to receive all the tweets.

Question:

Whether to send him only tweets, and allow him to request the comments of the post himself and on his tags with other requests, or make 3 requests on the server by generating him a response object, ala

 [ { id:1, text:"Hello", comments:[], tags:[] } ... ]

In which comments and tweet tags will already be included?

The problem is that I cannot pull out the desired object as above with one query, because it’s like a table of query results, in the query result table, comments and tags are separate queries, and I don’t know how to do it in mysql

 SELECT p.*, comments( SELECT * FROM COMMENTS WHERE post_id=p.id ) FROM posts
  • why can't you? just do not know how? You can join tables with the help of LEFT JOIN, INNER JOIN using the necessary keys - Vasily Barbashev
  • I don’t know how, well now it looks like this SELECT p. *, c.id as c_id, c.text as c_text, c.owner_id as c_owner, c.created_date as c_date from posts p LEFT JOIN comments c ON c.post_id = p .id - Oleg Andreev
  • the bottom line is that I need to connect the results table with one field in posts - Oleg Andreev
  • that is, the comments field should indicate the query results table for comments - Oleg Andreev
  • and if so SELECT ..., (SELECT * FROM comments WHERE {some condition, for example postId = 4}) as comments FROM posts - Vasily Barbashev

1 answer 1

Relational databases do not use this. This is a curtsey in the direction of NoSQL.

Each query must return exactly one rowset in which the same entities are located . There are exceptions, but they are rare and usually relate to database-specific functionality , and the database client does not always support this.

In PostgreSQL, a column may be of the type "array of values", but in MySQL, as far as I know, there is no such type. So, you still have to do to get comments to make a separate request and create an answer (JSON) at the application level.

And while such queries are a constant number, independent of the results of intermediate queries ( O(1) ), there is nothing to fear.

  • This is what I wanted to say when I worked with Mongo there was no such problem, but I was confused :) But the question remains whether to make 3 requests right away, or let the client make 3 requests for posts, comments, tags, Client writing on React.js - Oleg Andreev
  • @ Oleg Andreev, of course, then break it; react with redux make it easy to organize such a functional. It's easier to do everything asynchronously, and when the user needs it, he will ask for the right thing - Vasily Barbashev
  • @ Oleg Andreev Mongo is not a relational base and it is for other purposes :) Is it worth it to make separate requests it already depends on the use cases in your API: after all, comments are not a direct part of the post, and on the one hand, you can reduce the number of HTTP- queries, but on the other, each such query will potentially pull extra data. The decision is yours, you can not advise here. - D-side
  • Thank you, because I’m writing the client myself, I’m used to sending several requests for such cases, but I didn’t know if I’m doing the right thing (alas, the student is), in this case I’ll create three requests, thanks a lot) - Oleg Andreev
  • @ Oleg Andreev there is no “right choice” here, you need to figure out the consequences in each case and choose the lesser evil :) - D-side