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

Тема в разделе "Базы данных", создана пользователем verfaa, 3 ноя 2013.

Модераторы: latteo
  1. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    Итак, требуется создать что-то вроде рейтинга топ-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
     
  2. Stas-P

    Stas-P

    Регистр.:
    7 сен 2013
    Сообщения:
    165
    Симпатии:
    125
    Поточнее можно.
    Тебе надо вывести именно те фотографии за которые не голосовал именно данный пользователь?
    или фотографии за которые ещё никто не голосовал?
     
  3. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    Именно ту фотографию, за которую не голосовал текущий пользователь в текущем месяце (время голосования за фото хранится в поле date таблицы top100_votes).
    Т.е. в начале скрипта я авторизую пользователя:
    Код:
    $user_id = auth_user(); // Получаю id текущего пользователя
    А затем id пользователя можно использовать в выборке далее
     
  4. esche

    esche

    Регистр.:
    9 авг 2009
    Сообщения:
    360
    Симпатии:
    243
    Код:
    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 нравится это.
  5. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    ок спасибо, а как понять фрагмент кода
    Код:
    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
     
  6. esche

    esche

    Регистр.:
    9 авг 2009
    Сообщения:
    360
    Симпатии:
    243
    verfaa нравится это.
  7. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    esche, я так понимаю в запросе код
    Код:
    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. Для каких полей ещё нужно добавить индексы?
     
  8. esche

    esche

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

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