Require help with SQL query logic.

JamesBrauman

I'm in WAY over my head here :eek:

I have three tables, jokedata, picturedata and ratings. They are set out as such:

//JOKEDATA
   id   |   joketitle   |   jokecategory  |  joke  |  datesubmitted  |  views

//PICTUREDATA
   id   |   picturetitle   |   picturefilename   |   datesubmitted   |   views

//RATINGS
   id   |   relative_id   |   content_type   |   rating   |   remote_address

As you can see, jokedata and picturedata are both basically the same. They both hold a unique id for that particular item (id), the title of the item (joketitle or picturetitle) and some other information relevant to that particular table.

The table RATINGS is designed to store all ratings for all content (both jokedata and picturedata). Each rating has unique id (id), relative id (which relates to id of either jokedata or picturedata), content type (which can be equal to either 'joke' or 'picture' - this is to specify which item table we are working with, because although ID is unique for each table, we can have the same id in both tables i.e id 100 in jokedata and id 100 in picturedata), rating (which is the rating out of 5 for that item) and remote address (which lets me filter ratings by unique ip's etc).

Say I needed to get a list of the top 10 jokes, ordered by thier average rating. This is the query I have developed so far, although bear with me as I have VERY little mysql experience:

SELECT jokedata.id, jokedata.joketitle FROM jokedata JOIN ratings ON (jokedata.id=ratings.relative_id) WHERE ratings.content_type = 'joke' ORDER BY (SUM(ratings.rating) / COUNT(ratings.rating)) DESC LIMIT 10

That query should select the top 10 jokes, ordered by thier average rating using data from the 'ratings' table, and I should have returned the id of the joke and the title of the joke. I need this information to display in an ordered list like this:

Joke #1: ID-[jokedata.id] TITLE-[jokedata.joketitle]
Joke #2: ID-[jokedata.id] TITLE-[joekdata.joketitle]
etc...

Originally what I had was two extra fields for 'jokedata' and 'picturedata' which were 'totalrating' and 'totalrates'. This would allow me to get the average rating by dividing 'totalrating' by 'totalrates' and therefore order the data by rating, HOWEVER this was a weak solution as I could not implement ip-filtering, or delete individual ratings if needed.

Sorry for the long post, I just wanted to make it clear what I required, I'm hoping that someone will be able to help me out here (I've got no chance on my own! :p)

Thanks heaps.

James.

jazz_snob

You had the right idea just not the correct way to phrase it in SQL:

select jokedata.id, jokedata.joketitle, 
          sum(ratings.rating)/count(ratings.rating) as average 
from jokedata
inner join ratings 
           on ratings.content_type = 'joke' and ratings.relative_id = jokedata.id
group by jokedata.id
order by average desc
limit 10

Get the average as part of the "select" portion of the query and group by the pkey.

I would encourage you do add an index on the ratings table:

alter table ratings add index(content_type, relative_id)

which will help keep the query fast as number of records grows. Note that the order the columns are defined in the index MUST MATCH the order they appear in the query.

JamesBrauman

jazz_snob;10885716 wrote:
You had the right idea just not the correct way to phrase it in SQL:
select jokedata.id, jokedata.joketitle, 
          sum(ratings.rating)/count(ratings.rating) as average 
from jokedata
inner join ratings 
           on ratings.content_type = 'joke' and ratings.relative_id = jokedata.id
group by jokedata.id
order by average desc
limit 10
Get the average as part of the "select" portion of the query and group by the pkey.

I would encourage you do add an index on the ratings table:
alter table ratings add index(content_type, relative_id)
which will help keep the query fast as number of records grows. Note that the order the columns are defined in the index MUST MATCH the order they appear in the query.

Thanks for the quick reply, I'll test it out and see how it goes.

JamesBrauman

Thanks, works great.