Resolved Most active query

[Trki]

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

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:

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! :/

[Trki]

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

[jerry62704]

Have you tried a subquery on the new counter field?

[Trki]

Originally Posted by jerry62704

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 :/

[BubikolRamios]

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.

[BubikolRamios]

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.

[Trki]

Originally Posted by BubikolRamios

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