How to Change the Sequence of 'Joins' in Cakephp

How to change the sequence of 'joins' in CakePHP?

Finally I figured out how to do that:

$this->LevelOne->unbindModel(array('belongsTo' => array('LevelTwo')));
$this->LevelOne->find('all', array(
    'joins' => array(
          array(
             'table' => 'level_two',
             'alias' => 'LevelTwo',
             'type' => 'LEFT',
             'conditions' => array(
                  'LevelTwo.id = LevelOne.level_two_field_id'
              )
          ),
          array(
             'table' => 'level_three',
             'alias' => 'LevelThree',
             'type' => 'LEFT',
             'conditions' => array(
                  'LevelThree.id = LevelTwo.level_three_field_id'
              )
          )
     )
));

How to write order and limit within cakephp joins array

To get last newest posting_from you must use 'group_by' => 'employee_personal_id' instead of 'limit' => '1' and 'order' => 'EmployeePosting.posting_from DESC' will remain the same in the same array.

CakePHP find method with JOIN

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->Message->find('all', array(
    'contain' => array('User')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => '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->Message->find('all', array(
    'joins' => array(
        array(
            'table' => 'users',
            'alias' => 'UserJoin',
            'type' => 'INNER',
            'conditions' => array(
                'UserJoin.id = Message.from'
            )
        )
    ),
    'conditions' => array(
        'Message.to' => 4
    ),
    'fields' => array('UserJoin.*', 'Message.*'),
    'order' => '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' => array(
            'className'  => 'Message',
            'foreignKey' => 'from'
         )
    );
    public $belongsTo = array(
        'MessagesReceived' => array(
            'className'  => 'Message',
            'foreignKey' => 'to'
         )
    );
}

class Message extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array(
        'UserFrom' => array(
            'className'  => 'User',
            'foreignKey' => 'from'
        )
    );
    public $hasMany = array(
        'UserTo' => array(
            'className'  => 'User',
            'foreignKey' => 'to'
        )
    );
}

Now you can do your find call like this:

$this->Message->find('all', array(
    'contain' => array('UserFrom')
    'conditions' => array(
        'Message.to' => 4
    ),
    'order' => 'Message.datetime DESC'
));

Change join contain() condition in Cakephp

I am considering that you have belongsTo relationship between the two model friends and animals.

So you just have to change the foreignKey name in the association, so update the association in your Friends model like

$this->belongsTo('Animals', [
    'foreignKey' => 'animal1_id',  //Here the column name you want to use for join
    'className'=>'Animals',
    'joinType' => 'INNER'
]);

EDIT(for comment query):-
If you want to select other tables, you can achieve this in two ways

In controller

$this->loadModel('OtherModelName');   //This will load your model to $this object
$other_table_result = $this->OtherModelName->find('all')->toArray();

Method to use raw SQL queries,

$sql = "SELECT * FROM users"; 
$query = $this->connection->prepare($sql); 
$query->execute(); 
$result = $query->fetchAll();

Correct way to define order in CakePHP 2

Your first two examples should be the correct way of using order in CakePHP 2. You'll find examples of using order in the official docs under Retrieving Your Data.

So you should be either using:-

'order' => ['Model2.field_1', 'Model3.field_2']

Or:-

'order' => ['Model2.field_1' => 'ASC', 'Model3.field_2' => 'ASC']

Both would be interpreted the same. The documentation for Models explicitly describes the available formats for order.

I suspect that if these are not working in your query then there is another issue not shown here. I believe that when Cake is generating the query it will ignore order statements that aren't relevant to the query. For example, if you are trying to order on a has-many contain or you are using pagination and trying to order of a column that hasn't been configured correctly (see docs of pagination ordering).

---

Related Topics