Erhan Kılıç Erhan Kılıç - 1 month ago 11
MySQL Question

Mysql - Order By Sql Query

I want to order my query with total participants, total comments and total likes. But this isn't working. Any solutions?

And this query will be very hard for server when users number increased, is there any suggestion to optimize query? Thanks.

SELECT P.*,
distance_in_meters_lat_lng(P.post_latitude, P.post_longitude, :latitude, :longitude) as distance,
U.user_id,
U.user_name,
U.user_lastName,
U.user_photo,
LT.time_hour,
LC.category_name,
(SELECT COUNT(*) FROM posts_likes PL WHERE PL.post_id = P.post_id) as likes,
(SELECT COUNT(*) FROM comments C WHERE C.post_id = P.post_id) as comments,
(SELECT COUNT(*) FROM posts_likes PL2 WHERE PL2.post_id = P.post_id AND PL2.user_id = :user_id) as like_status,
(SELECT COUNT(*) FROM post_participants PC WHERE PC.post_id = P.post_id) as participants,
(SELECT COUNT(*) FROM post_participants PC2 WHERE PC2.post_id = P.post_id AND PC2.user_id = :user_id) as participant_status,
(SELECT user_id FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as winner,
(SELECT user_name FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as name,
(SELECT user_lastName FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as lastName,
(SELECT user_photo FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as photo
FROM posts P
INNER JOIN users U USING(user_id)
LEFT JOIN lot_times LT USING(time_id)
LEFT JOIN lot_categories LC USING(category_id)
WHERE P.user_id NOT IN (:list)
AND P.post_type = 'lot'
ORDER BY participants, likes, comments DESC
LIMIT 0, 20

Answer

At last I found answer myself.

SELECT P.*,
           distance_in_meters_lat_lng(P.post_latitude, P.post_longitude, :latitude, :longitude) as distance,
           U.user_id,
           U.user_name,
           U.user_lastName,
           U.user_photo,
           LT.time_hour,
           LC.category_name,
           (SELECT COUNT(*) FROM posts_likes PL WHERE PL.post_id = P.post_id) as likes,
           (SELECT COUNT(*) FROM comments C WHERE C.post_id = P.post_id) as comments,
           (SELECT COUNT(*) FROM posts_likes PL2 WHERE PL2.post_id = P.post_id AND PL2.user_id = :user_id) as like_status,
           (SELECT COUNT(*) FROM post_participants PC WHERE PC.post_id = P.post_id) as participants,
           (SELECT COUNT(*) FROM post_participants PC2 WHERE PC2.post_id = P.post_id AND PC2.user_id = :user_id) as participant_status,
           (SELECT user_id FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as winner,
           (SELECT user_name FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as name,
           (SELECT user_lastName FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as lastName,
           (SELECT user_photo FROM post_participants PC3 WHERE PC3.post_id = P.post_id AND is_winner = 1) as photo
    FROM posts P
    INNER JOIN users U USING(user_id)
    LEFT JOIN lot_times LT USING(time_id)
    LEFT JOIN lot_categories LC USING(category_id)
    WHERE P.post_type = 'lot'
    ORDER BY participants DESC, likes DESC, comments DESC
    LIMIT 0,20