Помогите с SQL запросом для выборки фотографий за которые ещё не голосовали

[verfaa]

Итак, требуется создать что-то вроде рейтинга топ-100 на сайте.
Имеется таблица users_photos, в ней фотографии пользователей, она имеет вид
id (id записи, auto_increment)
foto_id (id фото)
foto_path (путь к фото)
... и т.п.

Также имеется таблица top100_votes - в ней оценки пользователей за фотографии, она имеет вид
id (id записи, auto_increment)
foto_id (id фото, за которое проголосовали)
user_id (id пользователя который проголосовал)
mark (оценка)
date (datetime - время голосования)

Задача: допустим пользователь голосует, нам нужно выбрать фото из таблицы users_photos, за которое ещё не голосовал текущий пользователь в этом месяце.
Т.е. нужно выбрать foto_id из таблицы users_photos, которого еще нет в таблице top100_votes (при условии, что user_id из таблицы top100_votes равен текущему пользователю и datetime равна текущему месяцу)
Помогите написать SQL запрос пожалуйста.
БД MySQL

 

[Stas-P]

Поточнее можно.
Тебе надо вывести именно те фотографии за которые не голосовал именно данный пользователь?
или фотографии за которые ещё никто не голосовал?

 

[verfaa]

Именно ту фотографию, за которую не голосовал текущий пользователь в текущем месяце (время голосования за фото хранится в поле date таблицы top100_votes).
Т.е. в начале скрипта я авторизую пользователя:

$user_id = auth_user(); // Получаю id текущего пользователя

А затем id пользователя можно использовать в выборке далее

 

[esche]

SELECT foto_id FROM users_photos
WHERE foto_id NOT IN
(SELECT foto_id FROM top100_votes WHERE user_id=:user_id AND date > UNIX_TIMESTAMP()-3600*24*30)

Условия по дате подогнать под себя (календарный месяц, интервал и тд)
При необходимости, в конце добавить LIMIT 0,... (если нужны первые ??? фоток)

 

[verfaa]

ок спасибо, а как понять фрагмент кода

user_id=:user

?
Для чего двоеточие?

И ещё, если усложнить запрос:
Также есть таблица users, которая содержит поля
id (id записи, auto_increment)
user_id (id пользователя)
gender_id (пол пользователя)
country_id (страна пользователя)

В скрипт приходят из формы переменные $_GET['gender_id'], $_GET['country_id'] - страна и город
Задача: нужно выбрать 1 фото (принадлежащее пользователю из страны $_GET['country_id'] и пола $_GET['gender_id']) из таблицы users_photos, за которое ещё не голосовал текущий пользователь в текущем месяце.
Помогите написать SQL запрос пожалуйста.
БД MySQL

 

[esche]

Для чего двоеточие?

Для просмотра ссылки Войди или Зарегистрируйся
можно просто экранированную $переменную подставить.

 

[verfaa]

Для просмотра ссылки Войди или Зарегистрируйся, я так понимаю в запросе код

UNIX_TIMESTAMP()-3600*24*30

выберет данные за последние 30 дней.
А как выбрать за текущий месяц? Т.е. например сегодня 4 ноября, следовательно нужно выбрать данные начиная с 1 ноября, т.е. за неполные 4 дня.

И ещё на другом форуме подкинули такой вариант?

SELECT * FROM users_photos, top100_votes, users
WHERE users.gender_id = $gender AND users.country_id = $country
AND top100_votes.user_id  = users.user_id
AND users_photos.id <> top100_votes.foto_id

Какой вариант SQL-запроса лучше использовать? И на какие поля добавлять индексы? Я конечно добавлю индекс по полю foto_id для поля таблицы top100_votes. Для каких полей ещё нужно добавить индексы?

 

[esche]

Какой вариант SQL-запроса лучше использовать?

Если результат будет одинаковым (в смысле, логика не нарушена, в чём я сомневаюсь, т.к. не увидел временного ограничения в предложенном варианте), рекомендую вариант без перемножения таблиц (т.е. свой), хоть и с вложенным подзапросом. На практике же проще провести замеры.
По поводу индексов - Explain в помощь...
Для моего варианта помимо индекса на foto_id есть смысл добавить top100_votes (user_id, date)

А как выбрать за текущий месяц? Т.е. например сегодня 4 ноября, следовательно нужно выбрать данные начиная с 1 ноября, т.е. за неполные 4 дня.

Условие по времени, вроде как, вообще не должно корректно сработать в Вашем случае, т.к. дата в datetime (судя из первого поста), а UNIX TIMESTAMP возвращает целое число. Я бы в PHP при помощи Для просмотра ссылки Войди или Зарегистрируйся вычислил нужную дату (в формате datetime или timestamp) и сравнивал уже с ней