Select most viewed profiles

[kishan112]

Hi, I'm working on a simple dating website with member profiles.
I need to do something really simple, but I'm too tired to do this..

There is a profile table, login table and a profile_view table:

profiles:
profile_id, name, picture, etc..

login:
session_id, username, time, etc..

profile_view
view_id, profile_id, session_id

I want to display 15 most viewed profiles. What is the query I should use?

[ppetree]

You have two choices:

1) the profiles need to have a `count` field that gets updated everytime a profile is read then when you want the most viewed you can get the results based on the MAX(count) ORDERY BY count DESC

2) Create an alternate logging table and every time a profile is viewed you can add an entry. If you add a timestamp you can default that to CURRENT_TIMESTAMP and then you can do most viewed today, most viewed this week, most viewed this month, most viewed this year, most viewed ever.

[kishan112]

I tried this:

SELECT * 
FROM `profiles` 
LEFT JOIN `profiles_view` ON `profile`.`id` = `profiles_view`.`profile_id` 
AND `profiles_view`.`id` 
IN (
SELECT `id` 
FROM `profiles_view` 
GROUP BY `profile_id` 
ORDER BY COUNT( * ) DESC 
LIMIT 0 , 10
)

This is the error

MySQL said:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How can I rewrite this?

[ppetree]

SELECT * FROM `profiles` LEFT JOIN `profiles_view` ON `profile`.`id` = `profiles_view`.`profile_id` AND `profiles_view`.`id` IN (SELECT `id` FROM `profiles_view` GROUP BY `profile_id` ORDER BY COUNT( * ) DESC LIMIT 0 , 10)SELECT * 
FROM `profiles` 
LEFT JOIN `profiles_view` ON `profile`.`id` = `profiles_view`.`profile_id` 
AND `profiles_view`.`id` 
IN (
SELECT `id` 
FROM `profiles_view` 
GROUP BY `profile_id` 
ORDER BY COUNT( * ) DESC 
) LIMIT 0 , 10

Move the LIMIT outside the subquery and make it a part of the main query... we'll that will fix your error message anyway but I think you might encounter a different error.

[kishan112]

It doesn't work

With this one i get the id's of the lop 10 visited profiles

SELECT `profiel_id` 
FROM `profiles_view` 
GROUP BY `profiel_id` 
ORDER BY COUNT( * ) DESC LIMIT 10

Now I have to select these id's from the profiles table.. I don't want to use php for this one, so 1 single query would be great

[ppetree]

I think you want something like this...

SELECT p.profile, p.age, p.sex .....
FROM profiles AS p
JOIN (
  SELECT * 
  FROM profiles_view
  GROUP BY profile_id
) AS a ON p.id=a.profile_id
ORDER BY a.MAX(count) DESC;";