Выборка одной статьи каждого автора

mihdan · 21 Окт 2012

Дано: 2 таблицы - статьи и авторы. Задача: вывести на экран 6 последних публикаций разных авторов.

Таблица авторов:

PHP:

CREATE TABLE IF NOT EXISTS `crm_persons` ( 
  `id_person` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `surname_ru` varchar(255) NOT NULL, 
  `name_ru` varchar(255) NOT NULL, 
  `patronymic_ru` varchar(255) NOT NULL,   
  `photo_120x90` varchar(200) NOT NULL, 
  `id_country` int(10) unsigned NOT NULL DEFAULT '0', 
  `id_region` int(10) unsigned NOT NULL DEFAULT '0', 
  `id_city` int(10) unsigned NOT NULL DEFAULT '0', 
  `id_subway` int(10) unsigned NOT NULL DEFAULT '0', 
  PRIMARY KEY (`id_person`), 
  KEY `id_country` (`id_country`), 
  KEY `id_region` (`id_region`), 
  KEY `id_city` (`id_city`), 
  KEY `surname_ru` (`surname_ru`), 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Список персоналей'

Таблица статьи:

PHP:

CREATE TABLE IF NOT EXISTS `crm_news` ( 
  `id` bigint(8) NOT NULL AUTO_INCREMENT, 
  `id_rubric` bigint(8) NOT NULL DEFAULT '0', 
  `id_region` varchar(30) NOT NULL DEFAULT '0', 
  `id_theme` varchar(30) NOT NULL DEFAULT '', 
  `id_plot` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Сюжет', 
  `type` int(4) NOT NULL DEFAULT '0', 
  `name_ru` varchar(255) NOT NULL DEFAULT '', 
  `date` datetime NOT NULL, 
  `date_start` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Дата начала показа события', 
  `date_end` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Дата завершения показа события', 
  `id_person` int(10) unsigned NOT NULL DEFAULT '0', 
  `announcement_ru` text NOT NULL, 
  `content_ru` longtext NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY `id_rubric` (`id_rubric`), 
  KEY `id_region` (`id_region`), 
  KEY `id_theme` (`id_theme`), 
  KEY `name_ru` (`name_ru`), 
  KEY `subname_ru` (`subname_ru`), 
  KEY `date` (`date`), 
  KEY `id_person` (`id_person`) 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO

Делаю запрос:

PHP:

SELECT 
                      `p`.`id_person`, 
                      `p`.`photo_120x90`, 
                      CONCAT_WS   (' ', `p`.`name_ru`, `p`.`surname_ru`) AS `author`, 
                      `n`.`id`  AS  `id_new`, 
                      `n`.`name_ru` AS  `name`, 
                      `n`.`announcement_ru` AS  `announcement` 
              FROM        `crm_persons` AS  `p` 
              LEFT JOIN  `crm_news` AS  `n` ON `n`.`id_person` = `p`.`id_person` 
              WHERE       `n`.`type` = 6 
              AND         `p`.`photo_120x90` <> '' 
              AND         `p`.`surname_{$lng}` <> '' 
              AND         `p`.`name_{$lng}` <> '' 
              AND         `n`.`name_{$lng}` <> '' 
              AND         `n`.`announcement_{$lng}` <> '' 
              GROUP BY `n`.`id_person` 

              ORDER   BY  `n`.`date`  DESC 
              LIMIT   6

Выводит 6 публикаций, сгруппированных по автору, но не последние. Что может быть тут не так? Или как сделать правильно?

WMix · 21 Окт 2012

как только ты сгрупировал по id_person, ты получил 6 писателей и их случайными статьями...
нужно сослаться на последнии статьи,

substring(max(concat())),
складываешь дату и к примеру текст новости, выбираешь большее (с максимальной датой), и обрезаешь дату...

PHP:

SELECT
substring(max(concat(`n`.`date`, `n`.`name_ru`)), length(`n`.`date`) ) as `name`,
FROM ...

но так тебе придеться писать на каждое поле из таблицы новости...

пиши 2 запроса отдельно!

mihdan · 21 Окт 2012

ты получил 6 писателей и их случайными статьями

Почему случайные? Я же сортировал их по дате о_О

пиши 2 запроса отдельно

Код править нельзя, можно только запрос допилить.

WMix · 21 Окт 2012

Почему случайные? Я же сортировал их по дате о_О

читай как работает group by... сортировал ты конечный результат то что стало после group by

Код править нельзя, можно только запрос допилить.

делай внутренний запрос...

mihdan · 21 Окт 2012

делай внутренний запрос...

Подзапрос в смысле? Как его организовать?

WMix · 21 Окт 2012

внутренний запрос ищет только индификаторы новостей,
внешний оборачивает данными...

PHP:

SELECT 
                      `p`.`id_person`, 
                      `p`.`photo_120x90`, 
                      CONCAT_WS   (' ', `p`.`name_ru`, `p`.`surname_ru`) AS `author`, 
                      `n`.`id`  AS  `id_new`, 
                      `n`.`name_ru` AS  `name`, 
                      `n`.`announcement_ru` AS  `announcement` 
              FROM        `crm_persons` AS  `p` 
              LEFT JOIN  `crm_news` AS  `n` ON `n`.`id_person` = `p`.`id_person` 
              WHERE ID_NEWS IN (
                  select substring(max(concat(`n`.`date`,ID_NEWS))  as шесть_новостей_разных_авторов
                  from
                  group by
              )

Baton · 21 Окт 2012

PHP:

SELECT * FROM `crm_persons` p INNER JOIN `crm_news` n USING( `id_person` ) 
WHERE n.`id` IN ( 
   SELECT `id` FROM ( 
      SELECT DISTINCT `id`, `id_person` FROM `crm_news` ORDER BY `date` DESC LIMIT 6 
   ) `id` 
)

mihdan · 21 Окт 2012

substring(max(concat(`n`.`date`,ID_NEWS))

Смущает ваша конструкция

WMix · 21 Окт 2012

Baton

SELECT DISTINCT `id`, `id_person`

а какже разные писатели?

WMix · 21 Окт 2012

mihdan написал(а):
Смущает ваша конструкция

понимаю тебя

mihdan · 21 Окт 2012

Baton написал(а):

PHP:

SELECT * FROM `crm_persons` p INNER JOIN `crm_news` n USING( `id_person` ) 
WHERE n.`id` IN ( 
   SELECT `id` FROM ( 
      SELECT DISTINCT `id`, `id_person` FROM `crm_news` ORDER BY `date` DESC LIMIT 6 
   ) `id` 
)

Результат 0 строк

Baton · 21 Окт 2012

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

WMix · 21 Окт 2012

substring(max(concat(`n`.`date`,ID_NEWS))
просто вдумайся в эти строчки, и читая твое задание!...
это и есть стандартное решение данной проблемы!

Baton · 22 Окт 2012

PHP:

SELECT * FROM `crm_persons` p INNER JOIN `crm_news` n USING( `id_person` ) 
WHERE n.`id` IN ( 
   SELECT `id` FROM ( 
      SELECT MAX(`id`) id FROM `crm_news` GROUP BY `id_person` LIMIT 6 
   ) `id` 
)

PS: еще чуть подправил)

WMix · 22 Окт 2012

PHP:

WHERE n.`id` IN ( 
      SELECT MAX(`id`) id FROM `crm_news` GROUP BY `id_person` LIMIT 6 
)

этого достаточно, но не по дате!

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

Baton · 22 Окт 2012

WMix
Какая у тебя версия mysql?
Если я пишу сразу так:

PHP:

 SELECT * FROM `crm_persons` p INNER JOIN `crm_news` n USING( `id_person` ) 
WHERE n.`id` IN (
      SELECT MAX(`id`) id FROM `crm_news` GROUP BY `id_person` LIMIT 6 
)

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Версия мускуля 5.5.6.

То есть не дает использовать LIMIT в IN

Baton · 22 Окт 2012

WMix
Я понял твой финт. Ты хочешь сказать что новость с бОлшим id не обязательно должна быть старшей, если date не присваивается автоматически или может выставляться в ручную. Очень справедливое замечание. Согласен. Но твой финт с конкатенацией мне тоже не нравится, представим что у нас есть новость с id=1, она невидима ее date=0. Админ поднимает эту новость, и поле date становится = 1234567891. То есть по date наша новость соседствует теперь с записями (ORDER BY date DESC):

id | date
1 | 1234567891
4000 | 1234567890
4021 | 1234567889
3980 | 1234567888
3970 | 1234567887
3960 | 1234567886
3950 | 1234567885

... MAX( CONCAT( id, date ) ) ... LIMIT 6 не захватит новости с id=1, т.к. ее id на несколько порядков ниже, хотя date имеет самое большое значение.

---

Кароче, должен признать что я не знаю как сделать это одним запросом, и чтобы без изъянов. Несколькими запросами я бы делал это так:

//выбираем 6 авторов из публикаций (1 запрос)
1. SELECT DISTINCT `id_person` FROM `crm_news` ORDER BY `date` DESC LIMIT 6

//в цикле выбираем публикации (6 запросов)
2. SELECT * FROM `crm_news` WHERE `id_person` = $id_person ORDER BY `date` DESC LIMIT 1

Итого получаем 7 запросов, что многовато. Но `вторые` запросы можно объединить через UNION.

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

WMix · 22 Окт 2012

Но твой финт с конкатенацией мне тоже не нравится, представим что у нас есть новость с id=1, она невидима ее date=0. Админ поднимает эту новость, и поле date становится = 1234567891. То есть по date наша новость соседствует теперь с записями (ORDER BY date DESC):
... MAX( CONCAT( id, date ) ) ...

я предлагал дату поставить вперед и делать мах по дате, если длина поля date различная, дотягиваем до общей длины при помощи LPAD и все работает

при условии что дата это поле которую устанавливает автор - он решает когда статья написанна, максимальное id не соответствует максимальной дате

mihdan · 22 Окт 2012

//выбираем 6 авторов из публикаций (1 запрос)
1. SELECT DISTINCT `id_person` FROM `crm_news` ORDER BY `date` DESC LIMIT 6

//в цикле выбираем публикации (6 запросов)
2. SELECT * FROM `crm_news` WHERE `id_person` = $id_person ORDER BY `date` DESC LIMIT 1

Смысл в том, что надо выбрать статьи, а не авторов вначале. И не у всех статей есть авторство )

mihdan · 22 Окт 2012

WMix написал(а):
PHP:

WHERE n.`id` IN ( SELECT MAX(`id`) id FROM `crm_news` GROUP BY `id_person` LIMIT 6 )

Ошибка лимита вылетает

Выборка одной статьи каждого автора

Новичок

герр M:)ller

Новичок

герр M:)ller

Новичок

герр M:)ller

Новичок

Новичок

герр M:)ller

герр M:)ller

Новичок

Новичок

герр M:)ller

Новичок

герр M:)ller

Новичок

Новичок

герр M:)ller

Новичок

Новичок