0

I have this query:

update posts 
set rank = (SELECT FIND_IN_SET( CONCAT(points, '_', comments_count, '_', created_at), 
    (SELECT GROUP_CONCAT( CONCAT(points, '_', comments_count, '_', created_at) ORDER BY points desc, comments_count desc, created_at desc ) 
FROM (select *, COUNT(comments.id) as comments_count from posts) as posts_rankings INNER JOIN comments ON comments.post_id = posts_rankings.id)))

I want get count of comments on every post and next orderBy with this count. How I can do this? Now me get error:: unknown column comments.id in field list

My tables structure:

enter image description here

enter image description here

I calculate rank of post depending on order the points, comments_count and created_at.

Dronaxer
  • 97
  • 1
  • 1
  • 10
  • Do you have column `id` in `posts` table? – Harshil Doshi Feb 14 '19 at 10:00
  • This subquery makes no sense `select *, COUNT(comments.id) as comments_count from posts` You can't count from a different table than the one you're selecting from. – Barmar Feb 14 '19 at 10:12
  • The subquery also needs to be correlated with the main query, so you get the rank of that row. – Barmar Feb 14 '19 at 10:19
  • @Barmar can you post answer with example ? Thanks. – Dronaxer Feb 14 '19 at 10:40
  • I started to write an answer, but I couldn't finish it because I don't really understand what you're counting and ranking. I don't know your table definitions so I can't tell how everything should be joined. – Barmar Feb 14 '19 at 10:42
  • You also need to change the subquery to a join, see https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Barmar Feb 14 '19 at 10:43
  • The subqueries with `COUNT()` and `GROUP_CONCAT()` both need to use `GROUP BY`, but I don't know what you're grouping. – Barmar Feb 14 '19 at 10:44
  • @Barmar I updated a question. Please see and post answer.. – Dronaxer Feb 14 '19 at 10:46

1 Answers1

2

You can't select the grouped and non-grouped values in the same subquery. You need two subqueries, one to do the GROUP_CONCAT() of all the posts, and another to get the value for each specific post and get its rank in the concatenated list.

UPDATE posts AS p
JOIN (
    SELECT p1.id, FIND_IN_SET(CONCAT_WS('_', p1.points, p1.comments_count, p1.created_at), all_post_counts) AS rank
    FROM (
        SELECT p.id, p.created_at, p.points, IFNULL(COUNT(c.id), 0) AS comments_count
        FROM posts AS p
        LEFT JOIN comments AS c ON c.post_id = p.id
        GROUP BY p.id
    ) AS p1
    CROSS JOIN (
        SELECT GROUP_CONCAT(CONCAT_WS('_', p1.points, p1.comments_count, p1.created_at) ORDER BY points desc, comments_count desc, created_at desc) AS all_post_counts
        FROM (
            SELECT p.id, p.created_at, p.points, IFNULL(COUNT(c.id), 0) AS comments_count
            FROM posts AS p
            LEFT JOIN comments AS c ON c.post_id = p.id
            GROUP BY p.id
        ) AS p1
    ) AS p3
) AS p4
ON p.id = p4.id
SET p.rank = p4.rank

Make sure you set group_concat_max_len large enough to hold all the results in that subquery.

Barmar
  • 741,623
  • 53
  • 500
  • 612