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

Need Help
1

Hi,

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 ?

(All I want to do is : SELECT DISTINCT 'model' FROM 'rubriques' )

2

try ->distinct('model') and remove the select()

3

When I do :

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

It returns a Database Error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 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

SQL Query:

SELECT Rubriques.id AS Rubriques__id, … , Rubriques.model AS Rubriques__model FROM rubriques Rubriques GROUP BY model

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

Still the Database Error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 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

SQL Query

SELECT Rubriques.id AS Rubriques__id, …, Rubriques.model AS Rubriques__model FROM rubriques Rubriques GROUP BY model

6

This doesn’t make sense… What is doing the group by? Did you post the whole PHP code which generates the query?

7

image
image1920×4698 429 KB

8

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.
How can I keep the callback and make work my query “distinct” ?

9

So you are using the Translate behavior… This makes things more complicated…

I have no app running currently with the Translate behaviour set up, but what kind of strategy are you using? Are you using EAV or Shadow Tables for translation?

10

I use EAV for translation

11

Is what you are seeking actually something where you need the translations as part of your query?

12

Yes, in a way, as it is a generic code for my CMS.

13

I believe the faster solution for you would be to adjust your SQL mode and remove the only_full_group_by (which is default for MySQL as far as I remember)

1 Like
14

Thanks, but I found another way.

The problem was that $rubriquesModels was used to create associations and from Cakephp 5.1.0 an exception is raised for association with duplicate names, I just now check with $table->associations()->has() before adding association.