Mysql - Order By Sql Query

Question

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

You have an error?

ScaisEdge
– ScaisEdge

2016-10-16 18:41:50 +00:00
Commented Oct 16, 2016 at 18:41 — ScaisEdge
– ScaisEdge, Commented Oct 16, 2016 at 18:41
Nope, there is no error. But order is not correct.

Erhan Kılıç
– Erhan Kılıç

2016-10-16 19:05:33 +00:00
Commented Oct 16, 2016 at 19:05 — Erhan Kılıç
– Erhan Kılıç, Commented Oct 16, 2016 at 19:05

marc_s · Accepted Answer · 2017-06-04 21:03:11Z

1

This query is really big so subqueries would definitely take time, here you should use direct.

Hope this helps

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,
    COUNT(PL.*) as likes,
    COUNT(C.*) as comments,
    COUNT(PL2.*) as like_status,
    COUNT(PC.*) as participants,
    COUNT(PC2.*) as participant_status,
    PC3.user_id as winner,
    PC3.user_name as name,
    PC3.user_lastName as lastName,
    PC3.user_photo as photo,
FROM
    posts P
JOIN 
    posts_likes PL USING (post_id)
JOIN 
    comments C USING (post_id)
JOIN  
    posts_likes PL2 ON (PL2.post_id = P.post_id AND PL2.user_id = :user_id)
JOIN 
    post_participants PC USING (post_id)
JOIN 
    post_participants PC2 ON (PC2.post_id = P.post_id AND PC2.user_id = :user_id)
JOIN 
    post_participants PC3 ON (PC3.post_id = P.post_id AND is_winner = 1)
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

edited Jun 4, 2017 at 21:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 16, 2016 at 19:04

Faizan Younus

8031 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erhan Kılıç Over a year ago

I can't do that because I'm getting lots of information with this query. I've updated sql query to orjinal.

Faizan Younus Over a year ago

This query is really big so subqeries would definitely take time, here you should use direct join. I am updating my comment, that might help you.

Erhan Kılıç Over a year ago

Well, I updated this query because it gave me sytnax error. But this isn't working, giving me 0 result :(

Darshan Mehta · Accepted Answer · 2016-10-16 18:46:39Z

0

As you are using join in your query anyway, I would recommend joining comments and likes table and get the counts in select, eg.:

select p.id, count(l.id) as like_count, count(c.id) as comment_count
from post p
left join likes l on p.id = l.post_id
left join comments c on p.id = c.post_id
order by like_count desc, comment_count desc;

Here is the SQL Fiddle.

answered Oct 16, 2016 at 18:46

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

1 Comment

Erhan Kılıç Over a year ago

Well, I can't because I'm getting lots of informations. I've updated sql query.

Erhan Kılıç · Accepted Answer · 2016-10-29 09:24:52Z

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

Collectives™ on Stack Overflow

Mysql - Order By Sql Query

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related