CodingForums.com - Resolved Most active query

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Resolved Most active query (http://www.codingforums.com/showthread.php?t=286261)

Most active query

Hello. I have a problem and i cant find solution i have this query:

Code:

SELECT st.id,st.typ_statusu,st.blog_nadpis_slug,st.nick,st.status,st.youtube,st.shared_from_id,st.image_big,st.time,tu.profile_picture_statuses as profilovka,

sl.status_id as idecko,COUNT(sl.id) AS magnitude

FROM status_likes as sl



INNER JOIN statuses as st

ON sl.status_id=st.id



INNER JOIN users As tu

ON st.nick=tu.nick



WHERE st.time>1355005482



GROUP BY sl.status_id

ORDER BY magnitude DESC

                                           LIMIT 50

Little be slow but works good... In this query i select statuses in certain time to past ...and then to each status i am counting the number of likes. To end of query i select just some info(picture) from another query as you can see.

But my problem is:

I need to remake the query to not only count likes... but also count comments from another table! And then order them.

I absolutly lost...

I tried something like this but this is junk:

Code:

SELECT st.id,st.typ_statusu,st.blog_nadpis_slug,st.nick,st.status,st.youtube,st.shared_from_id,st.image_big,st.time,tu.profile_picture_statuses as profilovka,

sl.status_id as idecko,COUNT(sl.id) AS magnitude, COUNT(co.id) AS magnitude2

FROM status_likes as sl



INNER JOIN statuses as st

ON sl.status_id=st.id



INNER JOIN users As tu

ON st.nick=tu.nick



INNER JOIN comments as co

ON co.status_id=st.id



WHERE st.time>1355005482



GROUP BY sl.status_id

ORDER BY magnitude DESC

                                           LIMIT 50

Photo of tables i think i dont have to upload because from code is clear:
likes: collum with status_id(which is ID of status from status table...)
comments: the same system.

IF SOMEONE COULD HELP I WOULD BE REALLY GLAD! :/

So noone know how to do it? :/ :P

Have you tried a subquery on the new counter field?

Quote:

Originally Posted by jerry62704 (Post 1308459)

Have you tried a subquery on the new counter field?

No dont know how you think it. Could u please try it? I still try to get result but i cant and cant :(

But maybe there is another way... what i need is to select statuses from 24h back to example. And i need to order them by MOST ACTIVE (so sum the top liked and top commented ) and order :/

try to change
COUNT(co.id)
to
COUNT(distinct co.id)

In your second query, that might make it do a thing, doh I don't guarantie nothing.

Note allso: INNER is useless, exact same thing will be in result if you don't use it at all.

Doesnt look like you are familiare with LEFT JOIN.

Note that that would show you allso records where there is no comments at all.

Quote:

Originally Posted by BubikolRamios (Post 1308545)

try to change
COUNT(co.id)
to
COUNT(distinct co.id)

In your second query, that might make it do a thing, doh I don't guarantie nothing.

no it will not help :P And btw: i finallllllyyyyyy afffteeer three days have DONE IT!! :) ah i am so happy :P My friend helped me out. The problem was ... ehm it was all wrong :)