2

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
Improve this question
2
  • You have an error? Commented Oct 16, 2016 at 18:41
  • Nope, there is no error. But order is not correct. Commented Oct 16, 2016 at 19:05

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I can't do that because I'm getting lots of information with this query. I've updated sql query to orjinal.
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.
Well, I updated this query because it gave me sytnax error. But this isn't working, giving me 0 result :(
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.

Improve this answer

1 Comment

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

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.