StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You probably wouldn't want to do this as a single query, but in theory you could do.</p> <pre><code>SELECT Post.id AS post_id, Post.title AS post_title, Post.body AS post_body, GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS tags, COUNT(Comment.id) AS comment_count FROM Post LEFT JOIN Comment ON Post.id = Comment.id_post LEFT JOIN Post_Tag ON Post.id = Post_Tag.id_post LEFT JOIN Tag ON Tag.id = Post_Tag.id_tag GROUP BY Post.id ORDER BY Post.date ASC </code></pre> <p>I haven't checked this as I don't have access to your data, but it should work. You'll need to manually split the tags, which would appear in the format of "ID|TITLE#ID|TITLE", but that's the only extra processing required.</p> <p>Alternatively, you can avoid the GROUP_CONCAT for tags by splitting this workload between two separate queries:</p> <pre><code>SELECT Post.id AS post_id, Post.title AS post_title, Post.body AS post_body, COUNT(Comment.id) AS comment_count FROM Post LEFT JOIN Comment ON Post.id = Comment.id_post GROUP BY Post.id ORDER BY Post.date ASC </code></pre> <p>From this, you'd store all of the individual post_ids, and use them in a second query as follows:</p> <pre><code>SELECT Tag.id, Tag.title FROM Post_Tag INNER JOIN Tag ON Post_Tag.id_tag = Tag.id WHERE Post_Tag.id_post IN (**comma-separated list of Post IDs**) </code></pre> <p>This means you can do two queries, where otherwise you'd have to do one to get all of the posts, then another for EACH of those posts to retrieve the tags - THAT is an N+1 query, whereas what I propose above is a common way around the issue.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload