CakePHP find method with JOIN

Question

Hi   I need to do the following query using the CakePHP find method   SELECT   FROM  messages  INNER JOIN users ON messages from   users id WHERE messages to   4 ORDER BY messages datetime DESC   Basically I have    messages table with a Message model users table with User model   and want to retrieve information from both tables in one query  The users id field is the same as the messages from field  so that s what the join is on   I am doing it in my MessagesController so it would need to be something like    this- gt Message- gt find      Thanks

User · Answer

services    this- gt Service- gt find  all   array               limit    gt 4               fields    gt  array  Service     ServiceImage                   joins    gt  array                  array                           table    gt   services images                            alias    gt   ServiceImage                            type    gt   INNER                            conditions    gt  array                           ServiceImage service id    gt  Service id                                                                                                 It goges to array is null

User · Answer

There are two main ways that you can do this   One of them is the standard CakePHP way  and the other is using a custom join   It s worth pointing out that this advice is for CakePHP 2 x  not 3 x   The CakePHP Way  You would create a relationship with your User model and Messages Model  and use the containable behavior   class User extends AppModel       public  actsAs   array  Containable        public  hasMany   array  Message       class Message extends AppModel       public  actsAs   array  Containable        public  belongsTo   array  User        You need to change the messages from column to be messages user id so that cake can automagically associate the records for you   Then you can do this from the messages controller    this- gt Message- gt find  all   array       contain    gt  array  User        conditions    gt  array           Message to    gt  4             order    gt   Message datetime DESC        The  other  CakePHP way  I recommend using the first method  because it will save you a lot of time and work   The first method also does the groundwork of setting up a relationship which can be used for any number of other find calls and conditions besides the one you need now   However  cakePHP does support a syntax for defining your own joins   It would be done like this  from the MessagesController    this- gt Message- gt find  all   array       joins    gt  array          array               table    gt   users                alias    gt   UserJoin                type    gt   INNER                conditions    gt  array                   UserJoin id   Message from                                      conditions    gt  array           Message to    gt  4             fields    gt  array  UserJoin      Message           order    gt   Message datetime DESC        Note  I ve left the field name messages from the same as your current table in this example   Using two relationships to the same model  Here is how you can do the first example using two relationships to the same model   class User extends AppModel       public  actsAs   array  Containable        public  hasMany   array           MessagesSent    gt  array               className     gt   Message                foreignKey    gt   from                        public  belongsTo   array           MessagesReceived    gt  array               className     gt   Message                foreignKey    gt   to                       class Message extends AppModel       public  actsAs   array  Containable        public  belongsTo   array           UserFrom    gt  array               className     gt   User                foreignKey    gt   from                       public  hasMany   array           UserTo    gt  array               className     gt   User                foreignKey    gt   to                       Now you can do your find call like this    this- gt Message- gt find  all   array       contain    gt  array  UserFrom        conditions    gt  array           Message to    gt  4             order    gt   Message datetime DESC

User · Answer

Otro example  custom Data Pagination for JOIN  CODE in Controller CakePHP 2 6 is OK    this- gt SenasaPedidosFacturadosSds- gt recursive   -1         Filtro      where   array           joins    gt  array              array                   table    gt   usuarios                    alias    gt   Usuarios                    type    gt   INNER                    conditions    gt  array                       Usuarios usuario id   SenasaPedidosFacturadosSds usuarios id                                               array                   table    gt   senasa pedidos                    alias    gt   SenasaPedidos                    type    gt   INNER                    conditions    gt  array                       SenasaPedidos id   SenasaPedidosFacturadosSds senasa pedidos id                                               array                   table    gt   clientes                    alias    gt   Clientes                    type    gt   INNER                    conditions    gt  array                       Clientes id cliente   SenasaPedidos clientes id                                                       fields   gt array               SenasaPedidosFacturadosSds                  Usuarios usuario id                Usuarios apellido nombre                Usuarios senasa establecimientos id                Clientes id cliente                Clientes consolida doc sanitaria                Clientes requiere senasa                Clientes razon social                SenasaPedidos id                SenasaPedidos domicilio entrega                SenasaPedidos sds                SenasaPedidos pt ptr                      conditions   gt array               Clientes requiere senasa   gt 1                     order    gt   SenasaPedidosFacturadosSds created DESC            limit   gt 100             this- gt paginate    where         Get datos      data    this- gt Paginator- gt paginate        exit debug  data      OR Example 2  NOT active conditions    this- gt SenasaPedidosFacturadosSds- gt recursive   -1         Filtro      where   array           joins    gt  array              array                   table    gt   usuarios                    alias    gt   Usuarios                    type    gt   INNER                    conditions    gt  array                       Usuarios usuario id   SenasaPedidosFacturadosSds usuarios id                                               array                   table    gt   senasa pedidos                    alias    gt   SenasaPedidos                    type    gt   INNER                    conditions    gt  array                       SenasaPedidos id   SenasaPedidosFacturadosSds senasa pedidos id                                               array                   table    gt   clientes                    alias    gt   Clientes                    type    gt   INNER                    conditions    gt  array                       Clientes id cliente   SenasaPedidos clientes id                        Clientes requiere senasa   1                                                       fields   gt array               SenasaPedidosFacturadosSds                  Usuarios usuario id                Usuarios apellido nombre                Usuarios senasa establecimientos id                Clientes id cliente                Clientes consolida doc sanitaria                Clientes requiere senasa                Clientes razon social                SenasaPedidos id                SenasaPedidos domicilio entrega                SenasaPedidos sds                SenasaPedidos pt ptr                        conditions   gt array                 Clientes requiere senasa   gt 1                       order    gt   SenasaPedidosFacturadosSds created DESC            limit   gt 100             this- gt paginate    where         Get datos      data    this- gt Paginator- gt paginate        exit debug  data

[php] CakePHP find method with JOIN

Examples related to php

Examples related to sql

Examples related to cakephp

Examples related to join