Как выбрать уникальные записи, заставить работать groupBy после union?

Question

[Максим Тимофеев]

Есть таблица (чат) id | from | to | text | datetime
И связанная user

Пробовал через union, но дубли убираются только до union:

код

public function getSended(){
        return $this->hasMany(Chat::className(),['from'=>'id']);
    }

    public function getReceived(){
        return $this->hasMany(Chat::className(),['to'=>'id']);
    }

    public function getDialogs(){
        return $this->getSended()->select('id,to,to as userid')->groupBy('userid')->union($this->getReceived()->select('id,from,from as userid')->groupBy('userid'))->with('toUser');
    }

получается запрос

SELECT COUNT(*) FROM ((SELECT `id`, `to`, `to` AS `userid` FROM `chat` WHERE `from`=1 GROUP BY `userid`) UNION ( SELECT `id`, `from`, `from` AS `userid` FROM `chat` WHERE `to`=1 GROUP BY `userid` )) `c`

грубо говоря 2,3,4 соединяются с 2,4,5 и получаю 2,3,4,2,4,5 вместо желаемого, 2,3,4,5. groupBy после union не пашет. Заклинило меня наглухо, нужен хелп.

Пока вышел из ситуации так:

адский идиотизм

public function getDialogs(){
        $ids = $this->getSended()->select('to as userid')->groupBy('userid')->union($this->getReceived()->select('from as userid')->groupBy('userid'))->asArray()->all();
        return self::find()->andWhere(['id'=>ArrayHelper::getColumn($ids,'userid')])->groupBy('id');
    }

но это же кома

Comments

[Дмитрий]

День добрый.
А если так?

$ids = $this->getSended()->select('to as userid')->distinct()->groupBy('userid')->union($this->getReceived()->select('from as userid')->distinct()->groupBy('userid'))->asArray()->all();

[Максим Тимофеев]

slo_nik: А какая разница, тот же эффект будет

[Максим Федоров]

Максим Тимофеев: а можете описать задачу которую пытаетесь решить? точнее что Вы хотите получить в результате выборки

[Максим Тимофеев]

Максим Федоров: получить список уникальных пар to | from. Это чат, так что там id собеседников, нужно выбрать список диалогов, в которых участвует текущий пользователь. То есть или to или from должны быть равны известному id. При чем выбрать не все диалоги, а только 1 последнюю пару. И избежать дублей.

Answer 1

[Максим Федоров]

То есть по факту у Вас есть таблица сообщений между пользователями и Вам необходимо выбрать последнее сообщение в рамках каждого диалога.
Я вижу тут три варианта решения:
Вариант 1.
Если считать что последнее сообщение - это сообщение с большим идентификатором, тогда все последние сообщения по диалогу можно получить так

SELECT
	*
FROM
	chat
LEFT JOIN (
	SELECT
		max(id) AS message_id,
		IF (`to` = 1, `from`, `to`) AS `collocutor_id`
	FROM
		chat
	GROUP BY
		`collocutor_id`
) AS m ON chat.id = m.message_id
WHERE
	m.message_id IS NOT NULL

или так

SELECT
	*
FROM
	chat
WHERE
	id IN (
		SELECT
			max(id) AS message_id
		FROM
			chat
		GROUP BY
		IF (`to` = 1, `from`, `to`)
	)

Вариант 2.
Если считать что последнее сообщение - это сообщение с большей датой, тогда все последние сообщения по диалогу можно получить так

SELECT
	chat.*
FROM
	chat
LEFT JOIN (
	SELECT
		max(datetime) AS datetime,

	IF (`to` = 1, `from`, `to`) AS `collocutor_id`
	FROM
		chat
	GROUP BY
		`collocutor_id`
) AS m ON (
	m.`collocutor_id` = chat.`to`
	OR m.`collocutor_id` = chat.`from`
)
AND chat.datetime < m.datetime
WHERE
	m.datetime IS NULL

Но тут стоит понимать что если в рамках одного диалога совпадет дата и время - будет баг. Хотя и вероятность этого думаю крайнемала

Вариант 3.
Перепроектировать базу данных, а именно создать отдельную таблицу содержащую информацию о диалогах и работать непосредственно с ней. Это бы позволило избежать лишних запросов к БД