hellosheikh hellosheikh - 3 months ago 8
MySQL Question

Join query in cakephp

Hello I am new in Cakephp and I am having a hard time converting mysql queries in cakephp.I have this query which I want to convert into Cakephp syntax

SELECT *
FROM trip
JOIN countries ON trip.departure_country_id = countries.id
WHERE countries.country_name LIKE "eng%"


This is what I have tried so far

class Trip extends AppModel
{
public $useTable = 'trip';
public $primaryKey = 'trip_id';

public $belongsTo = array(
'User' => array(
'className' => 'User',
'foreignKey' => 'user_id',
'fields' => array('User.user_id','User.first_name','User.last_name','User.profile_image','User.country','User.phone_no')

),
'departure_country' => array(
'className' => 'Country',
'foreignKey' => 'departure_country_id',
),
'arrival_country' => array(
'className' => 'Country',
'foreignKey' => 'arrival_country_id',
)
);

public function getLocationBasedTrips($country){
return $this->find('all', array(
'conditions' => array(
'OR' => array('Country.country_name Like' => '%'.$country.'%')
),
));
}
}

Answer

Moved some brackets around from the previous answer. Try this:

 $this->find('all', array(
            'joins' => array(
                array(
                    'table' => 'countries',
                    'alias' => 'Country',
                    'conditions' => array(
                        'Trip.departure_country_id = Country.id'
                    )
                )
            ),
                'conditions' => array(
                    'Country.country_name LIKE' => "%$country%"
                )
            )
        );
Comments