Cant get distinct rows in a find list

1

Hi,
How can I rewrite this cakephp3 query so I can see list of suburbs that are distinct? Currently I get repeating suburbs in the list and groupBy doesnt work for this situation in the newer mysql.

 $suburb = $this->Students->find('list')
             ->select(['Students.id','Students.address_suburb'])
          ->where(['Students.address_suburb !=' => '','Students.student_inactive' => 0])
     // ->group(['Students.address_suburb'])->autoFields(true)
        ->order(['Students.address_suburb' => 'ASC'])
         ->hydrate(false);
3

You cant use a groupBy as the mysql version (NEWER) WONT allow code like this.
This is why I am asking for an alternative.
// ->group([‘Students.address_suburb’])->autoFields(true)

What type of response is this anyway?

4

I removed my previous comment because I tried and for me on list the group is working fine.

5

no your code wont work on the newer mysql version. My code above worked fine but you cant use groupBy like this anymore on the newer mysql that is why I go the problem.

I am wanting another way to get a list of items and remove the duplicates from the find.

6

Then run find('all') and use collection

7

can i have an example please as the docs you pointed me too only shows a very basic idea of what to do

8 
  This works
 $suburbs = $this->Students->find('list', [
             'keyField' => 'address_suburb',
                'valueField' => 'address_suburb'
            ])
       ->distinct()      
       ->where(['Students.address_suburb !=' => '','Students.student_inactive' => 0])
        ->order(['Students.address_suburb' => 'ASC'])      
       ->hydrate(false);