1

I have several tables like this

A posts table

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | timestamp       | YES  |     | NULL    |                |
| updated_at | timestamp       | YES  |     | NULL    |                |
| created_by | bigint          | NO   |     | NULL    |                |
| title      | text            | NO   |     | NULL    |                |
| post       | longtext        | NO   |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

A comments table

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | timestamp       | YES  |     | NULL    |                |
| updated_at | timestamp       | YES  |     | NULL    |                |
| comment    | longtext        | NO   |     | NULL    |                |
| post_id    | bigint          | NO   |     | NULL    |                |
| created_by | bigint          | NO   |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

A users table and more

I want to select posts and users and also get the comment count for each post. How can I implement this in one SQL query?

I tried the following query by trying to pass the comment_count as a virtual column. But it is failing.

How can I fix this? Thanks

SELECT     `post_meta`.`category_id`,
           `post_status`.`status_id`    AS `status_code`,
           `status_codes`.`status_name` AS `status_name`,
           `status_codes`.`color`       AS `status_color`,
           `categories`.`NAME`          AS `category_name`,
           `categories`.`slug`          AS `category_slug`,
           `categories`.`color`         AS `category_color`,
           `posts`.`title`              AS `post_title`,
           `posts`.`created_at`         AS `post_date`,
           `posts`.`id`                 AS `post_id`,
           `0`                          AS `comment_count`,
           `users`.`NAME`               AS `user_name`,
           `users`.`id`                 AS `user_id`
FROM       `posts`
INNER JOIN `post_meta`
ON         `posts`.`id` = `post_meta`.`post_id`
LEFT JOIN  `post_status`
ON         `posts`.`id` = `post_status`.`post_id`
LEFT JOIN  `status_codes`
ON         `post_status`.`status_id` = `status_codes`.`id`
INNER JOIN `categories`
ON         `post_meta`.`category_id` = `categories`.`id`
INNER JOIN `users`
ON         `users`.`id` = `posts`.`created_by` limit 50)

1 Answer 1

1

You could do this as a giant group by query, but in this case it's easier to just use a correlated subquery

I strongly recommend you use aliases, as shown

SELECT     pm.`category_id`,
           ps.`status_id`   AS `status_code`,
           sc.`status_name` AS `status_name`,
           sc.`color`       AS `status_color`,
           cat.`NAME`       AS `category_name`,
           cat.`slug`       AS `category_slug`,
           cat.`color`      AS `category_color`,
           p.`title`        AS `post_title`,
           p.`created_at`   AS `post_date`,
           p.`id`           AS `post_id`,
           (
             SELECT COUNT(*)
             FROM `comments` AS c
             WHERE c.`post_id` = p.`id`
           )                AS `comment_count`,
           u.`NAME`         AS `user_name`,
           u.`id`           AS `user_id`
FROM       `posts` AS p
INNER JOIN `post_meta` AS pm
ON         p.`id` = pm.`post_id`
LEFT JOIN  `post_status` AS ps
ON         p.`id` = ps.`post_id`
LEFT JOIN  `status_codes` AS sc
ON         ps.`status_id` = sc.`id`
INNER JOIN `categories` AS cat
ON         pm.`category_id` = cat.`id`
INNER JOIN `users` AS u
ON         u.`id` = p.`created_by`
limit 50

You can also pre-group it, and then do a normal join

SELECT     pm.`category_id`,
           ps.`status_id`   AS `status_code`,
           sc.`status_name` AS `status_name`,
           sc.`color`       AS `status_color`,
           cat.`NAME`       AS `category_name`,
           cat.`slug`       AS `category_slug`,
           cat.`color`      AS `category_color`,
           p.`title`        AS `post_title`,
           p.`created_at`   AS `post_date`,
           p.`id`           AS `post_id`,
           c.`comment_count`,
           u.`NAME`         AS `user_name`,
           u.`id`           AS `user_id`
FROM       `posts` AS p
INNER JOIN `post_meta` AS pm
ON         p.`id` = pm.`post_id`
LEFT JOIN  `post_status` AS ps
ON         p.`id` = ps.`post_id`
LEFT JOIN  `status_codes` AS sc
ON         ps.`status_id` = sc.`id`
INNER JOIN `categories` AS cat
ON         pm.`category_id` = cat.`id`
INNER JOIN `users` AS u
ON         u.`id` = p.`created_by`
INNER JOIN (
    SELECT c.`post_id`,
           COUNT(*) AS `comment_count`
    FROM `comments` AS c
    GROUP BY
           c.`post_id`
) c
ON         c.`post_id` = p.`id`
limit 50
1
  • Thank you this was a life saver
    – rksh
    Aug 29, 2021 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.