CODEWITHSUNDEEP
phpyii2
Geoff
Geoff

Reputation: 6629

Yii2 model relationship where not null

I would like to get all records in a relationship where its not null

so my table are

tbl_truck
    id, name

 tbl_checks
   id
   truck_id //foreign key from tbl_truck table id

So in my query i have

$query = TblTrucksModel::find()
         ->leftJoin('tbl_checks','tbl_trucks.id = tbl_checks.truck_id')
         ->where() //here add the condition

So basically i would like to fetch only the id's from tbl_truck which are also existent in tbl_checks

Nb: TblTrucksModel represents the tbl_trucks table

How do i go on about this.

Upvotes: 1

Views: 1948

Answers (3)

Muhammad Omer Aslam
Muhammad Omer Aslam

Reputation: 23740

If you intend not to show the trucks from the tbl_truck table that have no associated record in the tbl_checks and only show records that have an association then you should use innerJoin rather than leftJoin as left join will show all the records from the tbl_truck showing the truck_id as NULL for those records that have no association or matching truck_id in the tbl_checks 

TblTrucksModel::find()
         ->innerJoin('tbl_checks','tbl_trucks.id = tbl_checks.truck_id')

You don't need an extra where clause in this case.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Should be using the operator sintax 

    $query = TblTrucksModel::find()
     ->leftJoin('tbl_checks','tbl_trucks.id = tbl_checks.truck_id')
     ->where(['not', ['tbl_trucks.id' => null]])

Upvotes: 1

Naim Malek
Naim Malek

Reputation: 1184

Try this one.

$query = TblTrucksModel::find();
$query->select('t.*');
$query->from('tbl_truck t');
$query->leftjoin('tbl_checks c','t.id = c.truck_id');
$query->where('c.id is not null');
$result = $query->all();

Upvotes: 1

Related Questions