How to use `distinct()` in a query in Cakephp5?

Question

I'm looking for the way to avoid duplicates in a query in Cakephp5.

Here is my table rubriques :

id | ... | model | ...
======================
1  | ... | CustomRubriques | ...
2  | ... | CustomRubriques | ...
3  | ... | CustomRubriques | ...
4  | ... | CustomRubriques | ...

Here is my query :

$rubriquesModels = $this->Rubriques
                            ->find()
                            ->select('model')
                            ->distinct()
                            ->all()
                            ->extract('model');

debug($rubriquesModels->toArray());

Here is the result of the query :

 (int) 0 => 'CustomRubriques',
 (int) 1 => 'CustomRubriques',
 (int) 2 => 'CustomRubriques',
 (int) 3 => 'CustomRubriques',

Why doesn't distinct() remove the duplicates ?

--EDIT--

It seems the problem comes from my RubriquesTable where I do :

// in RubriquesTable.php
 public function beforeFind(Event $event, SelectQuery $query, ArrayObject $options, $primary)
    {
       $query
            ->enableAutoFields()
            ->find('translations');
            
       return $query;
    }

When I remove this callback, my query with “distinct” works well.

Don't know why does the code of my callback beforeFind() make my query ->distinct() not working... (?)

The parameters to distinct() are optional, but I've always used it like ->distinct('model'). — Greg Schmidt
– Greg Schmidt, Commented Oct 2, 2024 at 13:54
@GregSchmidt it returns a "Database Error" when I do ->distinct('model')... — Oliv
– Oliv, Commented Oct 2, 2024 at 14:03
Surely there are more specifics about that error than just "Database Error"? — Greg Schmidt
– Greg Schmidt, Commented Oct 2, 2024 at 16:08
@GregSchmidt SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cms-infoliv5.Rubriques.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by — Oliv
– Oliv, Commented Oct 2, 2024 at 17:08
Seems Cake is using GROUP BY instead of DISTINCT to implement this, and it's doing it in a way that wants the id to be in the selected fields. You might try ->select(['id', 'model']) to see if that resolves it; you're throwing away everything else with the extract call anyway, and id is a very small extra bit of data to pull across. — Greg Schmidt
– Greg Schmidt, Commented Oct 2, 2024 at 20:11

cuongvv · Accepted Answer · 2024-10-06 12:45:21Z

0

change ->distinct() to ->distinct(['Rubriques.model'])

answered Oct 6, 2024 at 12:45

cuongvv

164 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oliv Over a year ago

thanks but the problem is not coming from here. I've edited my question.

cuongvv Over a year ago

maybe it will left join to i18n table too.

Collectives™ on Stack Overflow

How to use `distinct()` in a query in Cakephp5?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related