Форум PHP программистов > Вывод сгруппированных записей в GROUP BY

[ Поиск ] - [ Пользователи ] - [ Календарь ]

Полная Версия: Вывод сгруппированных записей в GROUP BY

xxxLOGIATxxx

5.11.2011 - 07:43

Зарашиваем данные, которые группируются с помощью свойства GROUP BY

    $sql = "
          SELECT MESSAGES.*, USERS.*
          FROM se_messages MESSAGES
          JOIN se_users USERS
          ON MESSAGES.message_authoruser_id = USERS.user_id 
          OR MESSAGES.message_owneruser_id = USERS.user_id 		  
          WHERE message_owneruser_id = {$user->user_info['user_username']}
          OR message_authoruser_id = {$user->user_info['user_username']}
          GROUP BY USERS.user_id 
          ORDER BY MESSAGES.message_id DESC";

Как теперь вместе с данными вывести количество записей сгруппированных в каждой группе? Мне что-то подсказывает что нужно дорабатывать запрос с помощью COUNT(*), но мне трудно представить как это работает, в интернете внятного ответа не нашёл.

Спустя 1 час, 39 минут, 42 секунды (5.11.2011 - 08:23) johniek_comp написал(а):

Отличный сайт по SQL там найдешь ответ.
ТЫЦ

Спустя 2 минуты, 10 секунд (5.11.2011 - 08:25) imbalance_hero написал(а):

xxxLOGIATxxx
Допиши в select - COUNT(*) и посмотри результат.

Спустя 2 часа, 2 минуты, 54 секунды (5.11.2011 - 10:28) Placido написал(а):

GROUP BY необходимо использовать совместно с функциями-агрегатами (а в вашем запросе их нет).
Замените

SELECT MESSAGES.*, USERS.*

на

SELECT COUNT(*), USERS.*

Спустя 1 час, 50 минут, 5 секунд (5.11.2011 - 12:18) imbalance_hero написал(а):

Placido
Прям необходимо? Не всегда использовать группировку стоит ТОЛЬКО из-за агрегатов.

Спустя 1 час, 22 минуты, 23 секунды (5.11.2011 - 13:41) sergeiss написал(а):

Цитата (imbalance_hero @ 5.11.2011 - 13:18)

Прям необходимо? Не всегда использовать группировку стоит ТОЛЬКО из-за агрегатов.

Если говорить про MySQL, то там GROUP BY несколько нелогичный временами. В Postgre более четко. Там на самом деле группировка имеет смысл только при использовании функций агрегации.
И кстати, в том же Постгре можно делать свои функции агрегации

Спустя 9 часов, 2 секунды (5.11.2011 - 22:41) imbalance_hero написал(а):

Есть таблица юзеров, есть таблица фотографий. Фотографий может быть несколько.
Если через LEFT JOIN , то мы получим несколько записей, а нам надо получить лишь одну . То GROUP BY `id` и отсеиваем остальные фотки, получаем - 1 юзер-1 фото. И всё получается, без агрегации.

Спустя 1 день, 8 часов, 28 минут, 40 секунд (7.11.2011 - 07:09) xxxLOGIATxxx написал(а):

Ввёл COUNT(*), но так и не понял как вывести количество записей для каждой отдельной группы, ведь COUNT отобразит просто общий результат полученных данных, верно? А мне нужно отобразить количества записей в каждой группе, созданной через GROUP BY и занести их в переменную через цикл WHILE. Вот сама функция, COUNT дописал:

  function user_dialogue_list()
  {
    global $database, $user;
	
    $dialogues = NULL;
	

    $sql = "
          SELECT COUNT(*), MESSAGES.*, USERS.*
          FROM se_messages MESSAGES
          JOIN se_users USERS
          ON MESSAGES.message_authoruser_id = USERS.user_id 
          OR MESSAGES.message_owneruser_id = USERS.user_id 		  
          WHERE message_owneruser_id = {$user->user_info['user_username']}
          OR message_authoruser_id = {$user->user_info['user_username']}
          GROUP BY USERS.user_id 
          ORDER BY MESSAGES.message_id DESC";
		    
    $resource = $database->database_query($sql);	
		  
    $dialogues = array();
    while( $user_info = $database->database_fetch_assoc($resource) )
    {
        if ($user_info['user_id'] != $user->user_info['user_username'])
          {
            $dialogue_user = new SEUser();
            $dialogue_user->user_info['user_id']        = $user_info['user_id'];
            $dialogue_user->user_info['user_username']  = $user_info['user_username'];
            $dialogue_user->user_info['user_photo']     = $user_info['user_photo'];
            $dialogue_user->user_info['user_fname']     = $user_info['user_fname'];
            $dialogue_user->user_info['user_lname']     = $user_info['user_lname'];  	  		  	  
            $dialogue_user->user_displayname();
            $dialogues[] =& $dialogue_user;
            unset($dialogue_user);
        }
    }		  
	  // RETURN ARRAY
	  return $dialogues;

 }

Спустя 1 час, 9 минут, 30 секунд (7.11.2011 - 08:19) Placido написал(а):

Попробуй использовать "WITH ROLLUP"

GROUP BY `поле 1`, `поле 2` WITH ROLLUP

Спустя 11 минут, 8 секунд (7.11.2011 - 08:30) imbalance_hero написал(а):

withroll - это общая сумма, а COUNT - будет числа сгруппированные.

Спустя 19 минут, 2 секунды (7.11.2011 - 08:49) xxxLOGIATxxx написал(а):

Дело в том, что мне нужен не общий результат полученный из базы, мне нужно получить числа сгруппированных записей в каждой группе. При выводе должно отобразиться примерно следующее:

Группа 1 - 5 результатов в группе
Группа 2 - 6 результатов в группе
Группа 3 - 12 результатов в группе

Т.е. GROUP BY должен работать как обычно, разделить данные на группы и вывести по одному результату, получается что мне нужно при подсчёте как бы частично проигнорить свойство GROUP BY чтобы подсчитать количество результатов на каждую группу. Запутался уже...

Да уж, задачка

Спустя 7 минут, 3 секунды (7.11.2011 - 08:56) sergeiss написал(а):

Вместо COUNT(*) напиши COUNT(USERS.user_id). Должно быть то, что надо.

Спустя 18 минут, 57 секунд (7.11.2011 - 09:15) xxxLOGIATxxx написал(а):

Цитата (sergeiss @ 7.11.2011 - 05:56)

Вместо COUNT(*) напиши COUNT(USERS.user_id). Должно быть то, что надо.

Ну мы ведь таким образом просто посчитаем то, что уже считается и снова получим один общий результат, ведь группировка идёт по тем же USERS.user_id...

Как это дело правильно вывести в WHILE? Нам ведь нужен цикл результатов, а я пока что сам даже одиночный не напишу

Спустя 3 минуты, 25 секунд (7.11.2011 - 09:18) sergeiss написал(а):

Ты проверь сначала, что тебе рекомендуют, а потом будешь разглагольствовать

Спустя 8 минут, 55 секунд (7.11.2011 - 09:27) xxxLOGIATxxx написал(а):

Цитата (sergeiss @ 7.11.2011 - 06:18)

Ты проверь сначала, что тебе рекомендуют, а потом будешь разглагольствовать :)

Ну запрос то может и правильный, но я не пойму как организовать получение результатов в цикле :blink:

    while( $user_info = $database->database_fetch_assoc($resource) )
    {
		if ($user_info['user_id'] != $user->user_info['user_username'])
		{



          ///  как то так?
          $dialogue_userz = $database->database_num_rows($resource);
          echo $dialogue_userz;
          /// с таким кодом у меня несколько раз отобразилось общее количество групп



          $dialogue_user = new SEUser();
          $dialogue_user->user_info['user_id']        = $user_info['user_id'];
          $dialogue_user->user_info['user_username']  = $user_info['user_username'];
          $dialogue_user->user_info['user_photo']     = $user_info['user_photo'];
          $dialogue_user->user_info['user_fname']     = $user_info['user_fname'];
          $dialogue_user->user_info['user_lname']     = $user_info['user_lname'];  	  		  	  
          $dialogue_user->user_displayname();
          $dialogues[] =& $dialogue_user;
          unset($dialogue_user);
		}
    }

Спустя 7 минут, 57 секунд (7.11.2011 - 09:35) sergeiss написал(а):

Ты сделай запрос, посмотри на результат и скажи, что получил. У тебя есть прога работы с БД?
А потом будешь думать, как взять результат. Потому что на данный момент, как я понял, у тебя еще нет нужного результата, из которого можно было бы брать данные.

К тому же, что такое SEUser() - это надо проявить телепатию, да? Для начала сделай проще: просто вывод на экран нужных данных. Чтобы убедиться, что выбрано то, что требовалось.

Спустя 28 минут, 38 секунд (7.11.2011 - 10:04) xxxLOGIATxxx написал(а):

Цитата (sergeiss @ 7.11.2011 - 06:35)

SEUser() - это класс для правильного отображения информации о пользователях из таблицы se_users, user_id которых связан с message_owneruser_id и message_authoruser_id из таблицы se_messages. Всё это нам в запросе не мешает, я его построил как полагается, он собирает нужных мне пользователей и отображает всё как надо:

Имя пользователя1
Фото пользователя1

Имя пользователя2
Фото пользователя2

Но дело в том что я их группирую, чтобы каждый отдельный пользователь отображался один раз я их связал с таблицей se_messages чтобы отобразить сколько у каждого пользователя входящих+исходящих сообщений (суммарно). Должно получится следующее:

Имя пользователя1
Фото пользователя1
Сообщений: 14

Имя пользователя2
Фото пользователя2
Сообщений: 3

Имя пользователя3
Фото пользователя3
Сообщений: 7

Но я смутно представляю себе как это реализовать, я с COUNT толком ещё не работал а тут сразу такая сложная муть.

Цитата

У тебя есть прога работы с БД?

PHPMyAdmin

Цитата

Ты сделай запрос, посмотри на результат и скажи, что получил.

Сейчас в шапке отобразились одни пятёрки - 55555. Т.е., пятёрка это общее число разных пользователей которых мы получили из нашего запроса. Какой запрос сделать?

Вот так сейчас выглядит вся функция:

  function user_dialogue_list()
  {
    global $database, $user;
	
    $dialogues = NULL;
	

    $sql = "
          SELECT COUNT(USERS.user_id), MESSAGES.*, USERS.*
          FROM se_messages MESSAGES
          JOIN se_users USERS
          ON MESSAGES.message_authoruser_id = USERS.user_id 
          OR MESSAGES.message_owneruser_id = USERS.user_id 		  
		  WHERE message_owneruser_id = {$user->user_info['user_username']}
		  OR message_authoruser_id = {$user->user_info['user_username']}
		  GROUP BY USERS.user_id
		  ORDER BY MESSAGES.message_id DESC";
		    
    $resource = $database->database_query($sql);	

    $dialogues = array();
    while( $user_info = $database->database_fetch_assoc($resource) )
    {
		if ($user_info['user_id'] != $user->user_info['user_username'])
		{
	      $dialogue_user = new SEUser();
		  $dialogue_userz = $database->database_num_rows($resource);
		  echo $dialogue_userz;
          $dialogue_user->user_info['user_id']        = $user_info['user_id'];
          $dialogue_user->user_info['user_username']  = $user_info['user_username'];
          $dialogue_user->user_info['user_photo']     = $user_info['user_photo'];
          $dialogue_user->user_info['user_fname']     = $user_info['user_fname'];
          $dialogue_user->user_info['user_lname']     = $user_info['user_lname'];  	  		  	  
          $dialogue_user->user_displayname();
          $dialogues[] =& $dialogue_user;
          unset($dialogue_user);
		}
    }		  
	  // RETURN ARRAY
	  return $dialogues;

 }

Спустя 25 минут, 57 секунд (7.11.2011 - 10:30) sergeiss написал(а):

Цитата (xxxLOGIATxxx @ 7.11.2011 - 11:04)

Какой запрос сделать?

Возьми тот запрос, что вводится в переменную, поставь туда реальные величины вместо переменных, и запусти его в PHPMyAdmin. А уж потом, когда сам по себе запрос будет работать корректно, будешь работать с ним в скрипте.
И вот эти данные, что выдасть PHPMyAdmin, тут и покажи. Или опиши, что получается.

Спустя 1 час, 13 секунд (7.11.2011 - 11:30) xxxLOGIATxxx написал(а):

Цитата

В phpMyAdmin отобразились те же самые результаты, отобразились пользователи + добавилась переменная с COUNT которая отобразила ту же самую загадочную пятёрку, которая говорит о количестве найденных пользователях.

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

Давайте лучше на упрощённом примере, там я уже сам разберусь а то достану тут всех.

Представим таблицу:

Автор | Получатель
------------------------
--id1--|----id2--------
--id2--|----id1--------
--id1--|----id3--------

Мы запрашивает все сообщения, в которых пользователи с id1 и id2 являлись либо авторами, либо получателями, используя свойство GROUP BY по id авторов и получателей. Выводим их id через WHILE и получаем следующее:

id1
id2

Третьего результата нет, поскольку у нас третий id не упоминался в запросе. Итак, мы их вывели.

Вот теперь самое главное: у обоих id было по 2 и 3 совпадения и они сгруппировались в одиночные. Как теперь в цикле вывести по отдельности количество сгруппированных совпадений у каждого id? Должно получится так:

id1, совпадений: 3
id2, совпадений: 2

Мне бы простой, понятный пример и тему можно закрывать. Тема затянулась потому что я не понимаю где надо копать и как вообще это дело можно грамотно реализовать.

Спустя 11 часов, 42 минуты, 51 секунда (7.11.2011 - 23:13) Лена написал(а):

Попробуйте так. Я только идею показываю, я ж не знаю, с каким вы там фреймворком работаете.


 function user_dialogue_list()
  {
    global $database, $user;
    
    $dialogues = NULL;
    

    $sql = "
          SELECT MESSAGES.*, USERS.*
          FROM se_messages MESSAGES
          JOIN se_users USERS
          ON MESSAGES.message_authoruser_id = USERS.user_id 
          OR MESSAGES.message_owneruser_id = USERS.user_id           
          WHERE message_owneruser_id = {$user->user_info['user_username']}
          OR message_authoruser_id = {$user->user_info['user_username']}
          GROUP BY USERS.user_id
          ORDER BY MESSAGES.message_id DESC";
            
    $resource = $database->database_query($sql);    

    $dialogues = array();
    $p = 0;
    while( $user_info = $database->database_fetch_assoc($resource) )
    {
        
        if($p!=$user_info['user_id'])
        {
            $p = $user_info['user_id']	
            $sql_2 = "SELECT COUNT(USERS.user_id) FROM se_users USERS 
                    WHERE USERS.user_id = " . (int)$user_info['user_id'];	
            $resource_2 = $database->database_query($sql_2);
        
            //получаем результат запроса в виде числа
            //$user_info = $database->database_fetch_result или что у вас в этом случае используется...
        }
        
        if ($user_info['user_id'] != $user->user_info['user_username'])
        {
          $dialogue_user = new SEUser();
          $dialogue_userz = $database->database_num_rows($resource);
          echo $dialogue_userz;
          $dialogue_user->user_info['user_id']        = $user_info['user_id'];
          $dialogue_user->user_info['user_username']  = $user_info['user_username'];
          $dialogue_user->user_info['user_photo']     = $user_info['user_photo'];
          $dialogue_user->user_info['user_fname']     = $user_info['user_fname'];
          $dialogue_user->user_info['user_lname']     = $user_info['user_lname'];                        
          $dialogue_user->user_displayname();
          $dialogues[] =& $dialogue_user;
          unset($dialogue_user);
		}
    }		  
	  // RETURN ARRAY
	  return $dialogues;

 }

Если мыслить логически, вы делаете неправильно. Вы сначала группируете, а после вроде как одумываетесь и хотите разгруппировать назад и посчитать, сколько же было сгруппированных строк. Скажите лучше нормальным русским языком, что вы там вытягиваете из базы и что в конечном счете хотите получить на странице. У меня впечатление, что вам группировка с самого начала была не нужна. Вы в конце получаете юзеров и их фото, причем здесь таблица сообщений?

Спустя 7 часов, 14 минут, 4 секунды (8.11.2011 - 06:27) xxxLOGIATxxx написал(а):

Цитата

Вот оно, ну наконец то! Идею понял, скоро всё доделаю.

А таблицу сообщений подключаем чтобы отобразить количество отправленных и полученных сообщений пользователем (суммарно).

Спустя 1 час, 18 минут, 27 секунд (8.11.2011 - 07:45) xxxLOGIATxxx написал(а):

Я закончил код. В общем мы получили нужные данные пользователей с которыми переписывался залогиненный пользователь + вывели количество сообщений для всех с кем контактировал залогиненный пользователь. Вот только меня посещают смутные сомнения по поводу правильности этой функции. Ведь в цикле идёт обращение к базе и это просто убьёт сервер, тем более, если база будет большая...Но другого варианта я просто не представляю...что посоветуете?

  function user_dialogue_list()
  {
    global $database, $user;
	
    $dialogues = NULL;
	

    $sql = "
          SELECT MESSAGES.*, USERS.*
          FROM se_messages MESSAGES
          JOIN se_users USERS
          ON MESSAGES.message_authoruser_id = USERS.user_id 
          OR MESSAGES.message_owneruser_id = USERS.user_id 		  
          WHERE MESSAGES.message_owneruser_id = {$user->user_info['user_username']}
          OR MESSAGES.message_authoruser_id = {$user->user_info['user_username']}
          GROUP BY USERS.user_id
          ORDER BY MESSAGES.message_id DESC";
		    
    $resource = $database->database_query($sql);		

    $dialogues = array();
	$p = 0;
	
    while( $user_info = $database->database_fetch_assoc($resource) )
    {
		
      if ( $user_info['user_id'] != $user->user_info['user_username'] )
      {
        $dialogue_user = new SEUser();
        $dialogue_user->user_info['user_id']        = $user_info['user_id'];
        $dialogue_user->user_info['user_username']  = $user_info['user_username'];
        $dialogue_user->user_info['user_photo']     = $user_info['user_photo'];
        $dialogue_user->user_info['user_fname']     = $user_info['user_fname'];
        $dialogue_user->user_info['user_lname']     = $user_info['user_lname'];  	  		  	  
        $dialogue_user->user_displayname();

///		  
        if ( $p != $user_info['user_id'] )
        {
          $p = $user_info['user_id'];
          $sql_2 = "
          SELECT * 
          FROM se_messages 
          WHERE message_authoruser_id = $p AND message_owneruser_id = {$user->user_info['user_username']}
          OR message_owneruser_id = $p AND message_authoruser_id = {$user->user_info['user_username']}";	
          $resource_2 = $database->database_query($sql_2);
          $count = $database->database_num_rows($resource_2);
          $dialogue_user->user_info['count'] = $count;
        }		  
/// выше сам цикл обращения к базе для получения количества отправленных+полученных сообщений

        $dialogues[] =& $dialogue_user;
        unset($dialogue_user);
      }
				
    }		  
	  // RETURN ARRAY
	  return $dialogues;

 }

Спустя 15 минут, 57 секунд (8.11.2011 - 08:01) sergeiss написал(а):

Цитата (xxxLOGIATxxx @ 8.11.2011 - 08:45)

Ведь в цикле идёт обращение к базе и это просто убьёт сервер

Ты про эту строку говоришь?

while( $user_info = $database->database_fetch_assoc($resource) )

если да, то ничего не убъёт сервер :) Это идет обращение к временной таблице, куда были заскладированы результаты твоей выборки. И это единственный вариант получить данные. До сих пор от этого еще ни один сервер не умер, насколько мне известно :)

Даже сейчас, когда ты работаешь с форумом, то при каждой загрузке страницы проходит несколько обращений к БД. И ведь "жив, курилка" (форум то есть) :)

Спустя 16 минут, 49 секунд (8.11.2011 - 08:18) xxxLOGIATxxx написал(а):

sergeiss, умрёт - образно говоря :D Я не про то. Посмотри внимательней как цикл работает, ведь в нём отдельный запрос в БД который будет повторяться в зависимости от количества результатов из первого запроса:


		if ( $p != $user_info['user_id'] )
		{
		  $p = $user_info['user_id'];
		  $sql_2 = "
		  SELECT * 
		  FROM se_messages 
		  WHERE message_authoruser_id = $p AND message_owneruser_id = {$user->user_info['user_username']}
		  OR message_owneruser_id = $p AND message_authoruser_id = {$user->user_info['user_username']}";	
		  $resource_2 = $database->database_query($sql_2);
		  $count = $database->database_num_rows($resource_2);
		  $dialogue_user->user_info['count'] = $count;
		}

Он находится в самом цикле

Спустя 1 час, 2 минуты, 46 секунд (8.11.2011 - 09:21) xxxLOGIATxxx написал(а):

Хотя...с другой стороны ведь я ещё пагинацию мутить буду, так что запросов будет значительно меньше

Быстрый ответ:

Здесь расположена полная версия этой страницы.