Geek Answers Handbook

Yii2: How to write a Mysql query to select records from two tables using left join

I have two tables that I want to join ... for example, camps and cities. I am writing a request in PHP for this ... but I want the same request in Yii2 format ... Please tell me how I can write this request in yii2 ... ??

Here is my request:

SELECT DISTINCT camps.city_id, camps.state_id, cities.city_name 
    from camps 
    LEFT JOIN cities
    ON cities.state_id = camps.state_id
    WHERE camps.state_id = 5
    AND camps.status = '1'
    ORDER BY cities.city_name ASC
+4
source share
4 answers
use yii\db\Query;

$query  = new Query(); 
$query->select
                       (['camps.city_id AS City, camps.state_id AS State, cities.city_name AS Name'
                       ]) 
                      ->from('camps')
                      ->join('LEFT JOIN',' cities','cities.state_id = camps.state_id')
                      ->where('camps.state_id=:id',['id'=>5])
                      ->andWhere('camps.status=:no',['no'=>1]);


 $command   = $query->createCommand();
 $result    = $command->queryAll();
+4
source

Equivalent with ActiveQuerycan be like this:

Camp::find()
    ->select(['city_id', 'state_id', 'city_name'])
    ->distinct()
    ->joinWith('city')
    ->where([
        'state_id' => 5,
        'status' => 1,
    ])
    ->orderBy(['city_name' => SORT_ASC])
    ->all();

And add a relation to the model Camp:

public function getCity()
{
    $this->hasOne(City::className(), ['state_id' => 'state_id']);
}

You can find the documents in the Active Record section :

+1
source 
$query = new Query();
    $query->select(['camps.city_id', 'camps.state_id',   'cities.city_name'])->
        from('cities')->
        innerJoin('camps','cities.state_id = camps.state_id')->
        where(['camps.state_id' => 5,'camps.status' => '1'])->all();
0

, ,

$sql = " SELECT DISTINCT camps.city_id, camps.state_id, cities.city_name 
from camps 
LEFT JOIN cities
ON cities.state_id = camps.state_id
WHERE camps.state_id = 5
AND camps.status = '1'
ORDER BY cities.city_name ASC ";

$query = Yii::$app->db->createCommand($sql)->execute();
0

More articles:


All Articles