R
R
RZ2017-06-04 20:36:14
Yii
RZ, 2017-06-04 20:36:14

How to select using DISTINCT in yii2(AR)?

Good evening.
Tell me how to make a selection in Yii2 with DISTINCT so that I can get the value of not only those fields that are listed for distinct, but also any others?
For example, I have the following query:

$model = User::find()
    ->select( ['sender_id', 'recipient_id'] )
    ->distinct()
    ->all()

with this choice, I get only unique rows in the database with the sender_id and recipient_id fields, but there are other fields there. How can I get them in such a way that they do not fall under DISTINCT? Tried to add them like addSelect but in that case distinct also affects them.
And another second question! For example, in the table I have two lines with the following IDs:
sender_id = 1 | recipient_id = 2
sender_id = 2 | recipient_id = 1
And in general there can be an unlimited number of such paired strings. So, is it possible to somehow set distinct in such a way that such paired strings are considered as one? And get exactly the row whose primary key is greater

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Maxim Fedorov, 2017-06-05
@sergey_zhuravlev_89

with this choice, I get only unique rows in the database with the sender_id and recipient_id fields, but there are other fields there.

Distinct does not return unique ROWS, it returns unique VALUES of the specified columns, in your case unique combinations of sender_id and recipient_id values.
You would better describe what task you want to solve and what structure of the database, otherwise you get the feeling that you are approaching its solution from the wrong side. Are you trying to display a list of the user's chat partners? or what?
UPD based on the results of the discussion: Your task can be solved in the simplest version as follows:
$expression = new \yii\db\Expression('id IN (SELECT MAX(id) FROM messages WHERE `sender_id` = :user_id OR `recipient_id` = :user_id, GROUP BY IF (`sender_id` = :user_id, `recipient_id`, `sender_id`))',[':user_id' => 3]);
$models = Messages::find()->where($expression);

But in general, I think it makes sense to create a separate table that stores information about dialogs

A
AlikDex, 2017-06-05
@AlikDex

with this choice, I get only unique rows in the database with the sender_id and recipient_id fields, but there are other fields there. How can I get them in such a way that they do not fall under DISTINCT?

This is a contradiction. You either unique or other filters to use.
What is it like? How can two different strings be considered the same?! Think with your head!
For the second question something like:
SELECT `sender_id`, `recipient_id`
FROM `table`
GROUP BY (`sender_id`, `recipient_id`)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question