Cakephp3 - foreach inside foreach

[bichomen]

Hello,

I’m going crazy with this query, I have 3 tables (Users, Cities and Images) I want to show the users grouped by cities, where the user’s image and data are shown, also the city with the image of the city must be shown , An example:

City1 (image of the city), Name, etc.
User 1: (user image), Name, etc.
User 2: (user’s image), Name, etc.
User 3: (user image), Name, etc.
User 4: (user image), Name, etc.
User 5: (user’s image), Name, etc.
User 6: (user image), Name, etc.

City1 (image of the city), Name, etc.
User 1: (user image), Name, etc.
User 2: (user’s image), Name, etc.
User 3: (user image), Name, etc.
User 4: (user image), Name, etc.
User 5: (user’s image), Name, etc.
User 6: (user image), Name, etc.

City1 (image of the city), Name, etc.
User 1: (user image), Name, etc.
User 2: (user’s image), Name, etc.
User 3: (user image), Name, etc.
User 4: (user image), Name, etc.
User 5: (user’s image), Name, etc.
User 6: (user image), Name, etc.

City1 (image of the city), Name, etc.
User 1: (user image), Name, etc.
User 2: (user’s image), Name, etc.
User 3: (user image), Name, etc.
User 4: (user image), Name, etc.
User 5: (user’s image), Name, etc.
User 6: (user image), Name, etc.

Usuarios table:

id | nombre | idFoto | idCiudad

Ciudades Table:

id | nombre | idFoto

Imagenes table

id | imagen

The city and user images are in the same table: Images

Controller:

SELECT c.id, p.nombre, c.idFoto, u.id, a.nombre, a.idFoto
FROM usuarios a
LEFT JOIN imagenes i ON u.idFoto = i.id
LEFT JOIN ciudades e ON e.idFoto = i.id
LEFT JOIN ciudades c ON u.idCiudad = c.id
WHERE NOT isnull( c.nombre )
ORDER BY c.nombre ASC 

The query would be something similar to this, is that okay? How do I do it? I do not know how to do it

[Graziel]

so you have something like, in CitiesTable

$this->belongsTo('Images')->setForeignKey('idFoto');
$this->hasMany('Users')->setForeignKey('idCiudad');

UsersTable

$this->belongsTo('Images')->setForeignKey('idFoto');
$this->hasOne('Cities')->setForeignKey('idCiudad');

and then the query would be:

$users = $this->Users->find()->contain(['Images', 'Cities' => ['Images'])
    ->where(['Users.nombre IS NOT' => null])->orderAsc('Users.nombre')->all();

you should get all data you need using it but need to transform a bit to fit your needs for that you can probably use https://book.cakephp.org/3.0/en/core-libraries/collections.html#Cake\Collection\Collection::groupBy

[bichomen]

Hello,

First of all thank you, you are helping me a lot, but some things, if group only shows a result:

City1 - User1, City2 - User1, City3 - User1, …

then ->groupBy(''); is for collections, for querys it would be ->group(['']);

https://book.cakephp.org/3.0/en/orm/query-builder.html

Then there is the problem of the photo of the city that is also in images, I still do not know how to recover it.

[Graziel]

yeah i thought it would be easy to manipulate the query if given this way but nope, instead you can try

$cities = $this->Cities->find()->contain(['Images', ['Users' => function($q) {
   return $q->where(['Users.nombre IS NOT' => null])
                  ->orderAsc('Users.nombre')
                  ->contain(['Images']);
}]])->all();

the problem with it is main query wont be ordered by Users.nombre just the users in cities.

if you want your query that you gave in OP try using https://book.cakephp.org/3.0/en/orm/query-builder.html#adding-joins

[bichomen]

It does not work, it gives the following error:

Error: Unsupported operand types
File …/vendor/cakephp/cakephp/src/ORM/EagerLoader.php
Line: 441

I put the whole query:

Ciudades = Poblaciones

$poblaciones = TableRegistry::get('Poblaciones');
        $poblaciones = $poblaciones->find()
                                         ->contain([
                                                    'Imagenes',
                                                    ['Users' => function($q) {
                                                      return $q->where(['Users.nombre IS NOT' => null])
                                                               ->orderAsc('Users.nombre')
                                                               ->contain(['Imagenes']);
                                                      }
                                                    ]
                                                   ]);
        $apellidos = $poblaciones->func()->concat(['Users.primer_apellido' => 'identifier', ' ', 'Users.segundo_apellido' => 'identifier']);
        $year = $poblaciones->func()->year(['Users.fnacimiento' => 'identifier']);
        $poblaciones->select([
                                 'Poblaciones.id','Poblaciones.poblacion','Poblaciones.provincia','Poblaciones.foto','Poblaciones.lat','Poblaciones.lng',
                                 'Imagenes.imagen',
                                 'Users.id','Users.genero','Users.nombre','Users.foto',
                                 'apellidos' => $apellidos,
                                 'year' => $year
                                ])
                       ->orderAsc('Poblaciones.poblacion')
                       ->all();

[Graziel]

oh try to remove square braces from around Users

'Imagenes', 'Users' => function($q) ...

[bichomen]

$poblaciones = TableRegistry::get('Poblaciones');
        $poblaciones = $poblaciones->find()
                                         ->contain([
                                                    'Imagenes',
                                                    'Users' => function($q) {
                                                      return $q->select(['Users.id','Users.genero','Users.nombre','Users.foto'])
                                                                    ->where(['Users.nombre IS NOT' => null])
                                                                    ->orderAsc('Users.nombre')
                                                                    ->contain(['Imagenes']);
                                                      }
                                                   ]);
        $apellidos = $poblaciones->func()->concat(['Users.primer_apellido' => 'identifier', ' ', 'Users.segundo_apellido' => 'identifier']);
        $year = $poblaciones->func()->year(['Users.fnacimiento' => 'identifier']);
        $poblaciones->select([
                                 'Poblaciones.id','Poblaciones.poblacion','Poblaciones.provincia','Poblaciones.foto','Poblaciones.lat','Poblaciones.lng',
                                 'Imagenes.imagen',
                                 'apellidos' => $apellidos,
                                 'year' => $year
                                ])
                       ->where(['Users.lugar IS NOT' => null])
                       ->orderAsc('Poblaciones.poblacion')
                       ->all();
$this->set('poblaciones',$poblaciones)

2 Problems:
1 How to group Poblaciones?
2 The images of the users are not shown:

Notice (8): Trying to get property of non-object [APP/Template/Element/poblaciones.ctp, line 29]

Line 29:
$pictureUser = $poblacion->Users->imagen->imagen;

[Graziel]

1. not sure what you mean by ‘group Poblaciones’ group how?
2. shoule be $poblacion->user->imagen->imagen you can always check how variable structure looks like in debugKit variables tab, or just by plainly dd$var);

[bichomen]

not sure what you mean by ‘group Poblaciones’ group how?

@bichomen

City1 (image of the city), Name, etc.
User 1: (user image), Name, etc.
User 2: (user’s image), Name, etc.
User 3: (user image), Name, etc.
User 4: (user image), Name, etc.
User 5: (user’s image), Name, etc.
User 6: (user image), Name, etc.

Now it’s only showing me 38 records that is total of cities but I should show 188 which is the total number of users, it is showing one user per city

[Graziel]

if you get only 1 user pre city you’ve set up wrong association, should be in CitiesTable:

$this->hasMany('Users')->setForeignKey('idCiudad');

[bichomen]

I solved it, first rename the foreign keys in the tables according to the conventions of Cakephp, I returned to generate the tables, I was automatically created the relationships, later to be able to do a foreach inside another foreach, I did it with an if () to be able to meet the criteria of the first foreach, so first I looked for the cities and then the people and in the if I compared ciudad_id with Ciudades.id. And in this way it works.

This does not seem to me the most correct way to work, since I force the loop to go through all the records in the table looking for those that fulfill the condition.

But having been impossible to do it in a single consultation (I do not know if because you can not, or because I have not known how to do it) I have not had any choice.

Thanks for the help