Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    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! :/
    Last edited by Trki; 01-24-2013 at 08:32 PM.

  • #2
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    So noone know how to do it? :/ :P

  • #3
    Senior Coder jerry62704's Avatar
    Join Date
    Oct 2007
    Location
    Springfield, IL
    Posts
    1,100
    Thanks
    13
    Thanked 87 Times in 87 Posts
    Have you tried a subquery on the new counter field?
    .
    .
    ...and gladly would he learn and gladly teach

    Visit www.LiberalsWin.com for humor and the unique Bush/Obama Approval Polls

  • #4
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jerry62704 View Post
    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 :/
    Last edited by Trki; 01-24-2013 at 06:01 PM.

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #7
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BubikolRamios View Post
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •