SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order games by rating / views / favorites (was "Need Help with Advanced Query")

    I can't wrap my head around the MySQL needed to accomplish this so I am going to ask this to those who are much better around here. I am trying to create a 'featured' games query that functions better than my previous one.

    The relevant information to the table and columns used:
    Table: games
    - id
    - title
    - userid
    - rates_num
    - rates_sum
    - views

    Table: favorites
    - game

    I would like to have the query return, say, 12 rows that are sorted based on the above information. The 'rates_num' is the number of people who have rated the game, while the 'rates_sum' is the sum of all the rating scores. I included the 'userid' column here because I need this included: GROUP BY `userid`

    The query I am trying to accomplish would read something like this:
    Select games, order by (rating/views/num_favorites with some sort of randomization so each time its run, its fairly unique)

    The whole favorites part is not necessary, I just thought it would be nice. The games table has ~200 rows.

    I hope I have been at least somewhat clear. Any help is perfect, I know basic queries but this is way above my head. Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, still a bit in doubt

    could you give a few sample rows of data please, and the results from them (not necessary to construct 12 rows, i just need to see how the two tables are related)

    also, if you sort by rating and/or views, how is this supposed to be reconciled with a random sort?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    About the random rows I am on the go so Ill have to do that later.

    The random part is my problem. That part I could do probably without mysql though so for now a query excluding any randomness would work.

    Possibly giving a preference to the most recently published games would allow for it to constantly change. There is a column in the games table called 'published'.

    I will try and explain more here later.

  4. #4
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, so basically the query would select games based on their views and ratings.

    If you could, sorting them also based on the number of people who have added it as a favorite would be great.

    The favorites table is related in that the `game` column refers to the id of the game that was added as a favorite. So each row in favorites that has the same `game` column would be relevant.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by proph3t
    Alright, so basically the query would select games based on their views and ratings.
    based on those two values how?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict proph3t's Avatar
    Join Date
    Jun 2004
    Location
    Arizona
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most views and highest rating, sorry, I should have been more specific.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select id
         , title
         , userid
         , rates_num
         , rates_sum
         , views
      from games
    order 
        by views + rates_sum
         , rates_num
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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