CODEWITHSUNDEEP
mysql
Maurício Giordano
Maurício Giordano

Reputation: 3276

SQL two one-to-many relations - count records

I have a feed table, feedComment table and feedLike table.

I need, in one query, select all feeds from the database with the number of comments and likes.

This is what I have so far:

SELECT
            `feed`.`id` AS `feedID`,
            `feed`.`companyID`,
            `feed`.`eventID`,
            `feed`.`memberID` AS `personID`,
            `feed`.`title`,
            `feed`.`text`,
            `feed`.`shares`,
            UNIX_TIMESTAMP(`feed`.`date`) AS `date`,
            COUNT(`feedComment`.`id`) AS `comments`
FROM `feed`
LEFT JOIN `feedComment`
ON `feedComment`.`feedID` = `feed`.`id`
GROUP BY `feed`.`id`

This works with feedComment and feedLike apart. If I try to use both of them, it doesn't work.

How do I accomplish that?

Upvotes: 1

Views: 62

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Given what you have so far, you could add an inline view to return the count from feedLike.

 SELECT `feed`.`id` AS `feedID`,
        `feed`.`companyID`,
        `feed`.`eventID`,
        `feed`.`memberID` AS `personID`,
        `feed`.`title`,
        `feed`.`text`,
        `feed`.`shares`,
        UNIX_TIMESTAMP(`feed`.`date`) AS `date`,

        IFNULL(`countLike`.`cnt`,0) AS `likes`,

        COUNT(`feedComment`.`id`) AS `comments`
   FROM `feed`
   LEFT
   JOIN `feedComment`
     ON `feedComment`.`feedID` = `feed`.`id`

   -- outer join to inline view (MySQL derived table)
   LEFT
   JOIN (
          SELECT `feedLike`.`feedID`
               , COUNT(`feedLike`.`id`) AS `cnt`
            FROM `feedLike`
           GROUP BY `feedLike`.`feedID`
        ) `countLike`
     ON `countLike`.`feedID` = `feed`.`id`

  GROUP BY `feed`.`id`

The query between the parens will be executed, and the results will be what MySQL calls a "derived table". We assign that derived table a name... countLike. To the outer query, it's just like countLike was an actual table.

The IFNULL() function is used in the outer select to replace a possible NULL value with a zero. It's not mandatory we do this, but usually folks like to get a "zero" returned as a count, rather than a NULL.

Note that there can be significant performance issues with derived tables. (The latest version of MySQL addresses some of those problems, such as the absence of indexes on the derived table.)

This same approach can be applied to getting the count from the feedComment table as well. Especially if there's no other need to group by feed.id.

 SELECT feed.id                    AS `feedID`
      , feed.companyID
      , feed.eventID
      , feed.memberID              AS `personID`
      , feed.title
      , feed.text
      , feed.shares
      , UNIX_TIMESTAMP(feed.date)  AS `date`
      , IFNULL(countLike.cnt,0)    AS `likes`
      , IFNULL(countComment.cnt,0) AS `comments`
   FROM feed
   LEFT
   JOIN ( SELECT c.feedID
               , COUNT(1) AS cnt
            FROM feedComment c
           GROUP BY c.feedID
        ) countComment
     ON countComment.feedID = feed.id
   LEFT
   JOIN ( SELECT l.feedID
               , COUNT(1) AS cnt
            FROM feedLike l
           GROUP BY l.feedID
        ) countLike
     ON countLike.feedID = feed.id

DISTINCT

As an entirely different approach, you could have a query generate the partial cross-product (every row from FeedLike for a given feedID matched to every row from FeedComment), and then count "distinct" id values. But this approach has the potential to generate a fairly sizable (and performance killing) intermediate result. (If a given feed has 30 rows feedLike and 30 rows in feedComment, that's a total of 1 x 30 x 30 = 900 rows for a single feed.

I purposefully leave this example incomplete, to discourage this as a general practice. But I include it because it does demonstrate another approach: 

 SELECT feed.id
      , ...
      , COUNT(DISTINCT feedLike.ID)
      , COUNT(DISTINCT feedComment.ID)
   FROM feed
   LEFT
   JOIN feedLike
     ON ...
   LEFT
   JOIN feedComment
     ON ...
  GROUP BY feed.id

Upvotes: 1

Omar Martinez
Omar Martinez

Reputation: 439

Why not use subquerys? (Works on Sql Server at least)

Select `feed`.`id` AS `feedID`,
        `feed`.`companyID`,
        `feed`.`eventID`,
        `feed`.`memberID` AS `personID`,
        `feed`.`title`,
        `feed`.`text`,
        `feed`.`shares`,
        UNIX_TIMESTAMP(`feed`.`date`) AS `date`,
        (Select count(`feedComment`.`id`) from feedComment Where commentid = F.id) as 'Comment',
        (Select count('feedLike'.'id') from feedLike Where likeid = F.id) as 'Something else'
From feed F

If you just need the count I dont see why use join

Upvotes: 1

Related Questions