Thursday, March 16, 2017

Many to many relationship returns empty set

I have a following table structure in my existing database :
tbl_Grzyby (ID, Nazwa, ...) - main table,
tblk_TypGrzyba (ID, TypGrzyba, OrderNo) - lookup table,
tblm_TypGrzyba (ID_Grzyb, ID_TypGrzyba) (many-to-many table).

I've created a grzyby Eloquent model class, which has a custom property, defined as:

class grzyby extends Model
{
    protected $table = 'tbl_Grzyby';

    public function typ_grzyba() {
        return $this->belongsToMany('\App\Lookups\typ_grzyba', 'tblm_TypGrzyba','ID_Grzyb', 'ID_TypGrzyba');
    }

}

I've also created the lookup table model, defined as:

class typ_grzyba extends Model {
    protected $table = 'tblk_TypGrzyba';

    public function grzyby() {
        return $this->belongsToMany('\App\grzyby', 'tblm_TypGrzyba','ID_Grzyb', 'ID_TypGrzyba');
    }

}

When I try with tinker to get record from main with ID=67, like:

$main_record=\App\grzyby::where('ID',67)->first();

it returns me all the details for that record fine. Returning all lookups runs also fine:

$types = \App\Lookups\typ_grzyba::all();

However, if I try to do the following:

$main_record_types = $main_record->typ_grzyba;

tinker returns me an empty collection, despite record existing in the many to many table: record EXISTS in the many-to-many table

When I run the query log it outputs me following SQL query:

select `tblk_TypGrzyba`.*, `tblm_TypGrzyba`.`ID_Grzyb` as `pivot_ID_Grzyb`, `tblm_TypGrzyba`.`ID_TypGrzyba` as `pivot_ID_TypGrzyba` from `tblk_TypGrzyba` inner join `tblm_TypGrzyba` on `tblk_TypGrzyba`.`id` = `tblm_TypGrzyba`.`ID_TypGrzyba` where `tblm_TypGrzyba`.`ID_Grzyb` is null

If I run that query against the database it returns me empty result, however, if I change the where clause manually to

where `tblm_TypGrzyba`.`ID_Grzyb` = 67

I'm getting the expected results in phpMyAdmin.

What's the reason of it and how I can fix that?



via Adrian K.

Advertisement