1. Приветствуем Вас на неофициальном форуме технической поддержки XenForo на русском языке. XenForo - коммерческий форумный движок от бывших создателей vBulletin, написанный на PHP.

1.2.x SQL запрос на выборку user_id по ip

Тема в разделе "Для новичков", создана пользователем Gauda, 02.04.2014.

Загрузка
  1. Gauda

    Gauda Местный

    Регистрация:
    15.03.14
    Сообщения:
    5
    Симпатии:
    0
    Версия XF:
    1.2.3
    Всем доброго времени суток.

    Есть задача:
    По IP, полученному из внешнего источника найти в базе (таблица xf_ip), кто из пользователей (поле user_id) с этим IP (поле ip) проявлял наибольшую активность, тоесть встречается максимальное количество раз.

    Помогите пожалуйста составить запрос, чтобы в результате получилось:
    ip1 user_id1
    ip2 user_id2
    ip3 user_id3


    где user_id - это ID пользователя, который встречается в таблице xf_ip с этим IP максимальное количество раз)

    Запрос должен иметь такой вид, чтобы по нему проверять не один IP а сразу список адресов без выполнения повторных запросов.

    Заранее большое спасибо.
     
  2. infis

    infis Местный

    Регистрация:
    27.06.11
    Сообщения:
    5 966
    Симпатии:
    3 548
    Версия XF:
    1.5.9
    Ну, как-то, так:
    Код:
    SELECT user_id,ip,count(action) as counts FROM xf_ip WHERE ip=искомый_ип GROUP BY ip,user_id ORDER BY counts DESC
    При этом первой строкой будет строка с максимальным количеством совпадений связки user_id+ip в таблице.
    --- добавлено : 2 апр 2014 в 04:05 ---
    Можно добавить LIMIT 1 в конце, тогда всегда будет возвращаться только одна строка.

    Список адресов проверять одним запросом не стОит. Лучше по одному. Дело в том, что группировка с подсчетом совпадений уже является функций агрегирования. Дополнительно еще одну функцию агрегации (MAX в вашем случае) можно использовать только для вложенного запроса. Как минимум, с точки зрения производительности это не будет хорошим вариантом.
     
    Последнее редактирование модератором: 09.04.2014
    Mirovinger и Gauda нравится это.
  3. Gauda

    Gauda Местный

    Регистрация:
    15.03.14
    Сообщения:
    5
    Симпатии:
    0
    Версия XF:
    1.2.3
    Сейчас происходит так:
    $array = $this->fetchAllKeyed('SELECT ip, user_id FROM `xf_ip` GROUP BY ip, user_id ORDER BY ip ASC', 'ip');

    Затем в массиве $array ищутся IP и по ним возвращается user_id, но как-то криво построен запрос и неверно возвращает результат



    Мне в результате запроса необходима не 1 строка, а массив IP адресов, где для любого IP адреса user_id = наиболее встречающийся для этого IP
     
    Последнее редактирование: 02.04.2014
  4. infis

    infis Местный

    Регистрация:
    27.06.11
    Сообщения:
    5 966
    Симпатии:
    3 548
    Версия XF:
    1.5.9
    По идее должен быть такой запрос:
    Код:
    SELECT user_id, ip, max(counts) as maxcounts
    FROM (
        SELECT user_id, ip, count(*) as counts
        FROM xf_ip
        GROUP BY user_id, ip
    ) v_1
    GROUP BY ip;
    
    Проблема в том, что этот запрос - очень тяжеловесный, а потому серьезно нагрузит сервер. Более того, phpmyadmin, к примеру, у меня вообще на этом запросе помирает (отваливается по таймауту).
     
    Gauda и Mirovinger нравится это.
  5. Gauda

    Gauda Местный

    Регистрация:
    15.03.14
    Сообщения:
    5
    Симпатии:
    0
    Версия XF:
    1.2.3
    Подскажите тогда пожалуйста, как можно наиболее правильно получить от 50 до 100 результатов определения user_id по ip? А то 100 запросов на страницу мне кажется, многовато.
     
  6. infis

    infis Местный

    Регистрация:
    27.06.11
    Сообщения:
    5 966
    Симпатии:
    3 548
    Версия XF:
    1.5.9
    Наиболее правильно будет создать отдельную таблицу для хранения количества совпадений. Соответственно, потребуется написать плагин, который будет заниматься наполнением этой таблицы, а также дальнейшей обработкой результатов, включая отображение или вывод данных куда-либо.
    Можно еще попытаться оптимизировать это под кеш.

    В любом случае строить запросы по одной таблице - это плохо по производительности. Перекладывать частичную обработку на скрипт - тоже ничего хорошего. Поэтому оптимально будет пользоваться дополнительной таблицей.

    P.S. Любопытства ради. А для чего все это?
     
    Mirovinger нравится это.
  7. Gauda

    Gauda Местный

    Регистрация:
    15.03.14
    Сообщения:
    5
    Симпатии:
    0
    Версия XF:
    1.2.3
    Для того, чтобы сопоставить пользователей форума с пользователями из другой базы данных, в которой на данный момент единственным идентификатором по которому вообще можно сделать сопоставление является IP адрес. После получения идентификатора при выводе результатов из внешней базы, уже можно будет использовать аватары, и указатели на профили пользователей форума.

    Пруфы кидаю в личку, т.к не знаю практикуется ли тут выкладывать прямые ссылки.
     
  8. infis

    infis Местный

    Регистрация:
    27.06.11
    Сообщения:
    5 966
    Симпатии:
    3 548
    Версия XF:
    1.5.9
    Судя по всему, отталкиваться от IP большого смысла не имеет. Надо все же объединять пользователей по никам и другим идентификаторам. Тогда все будет работать значительно быстрее и предсказуемо. Если опираться на IP, то и доля ошибок будет, и запросов масса получается.

    Что мешает сопоставить ники или иные ID пользователей? Если это будет крутиться в пределах одной базы данных, то и запрос будет банальным джойном.
     
    Gauda нравится это.

Поделиться этой страницей