I have this query wrote in Eloquent :
return TypeModel::with(['apt' => function($q) use ($id){ $q->leftJoin('build_apt', function($join) use($id){ $join->on('build_apt.id_apt', '=', 'apt.id_apt'); $join->on('build_apt.id_build', '=', DB::raw($id->id_build)); }) }]) ->get() ->toJson();
Equivalent SQL :
SELECT * FROM `apt` LEFT JOIN `build_apt` ON `build_apt`.`id_apt` = `apt`.`id_apt` AND `build_apt`.`id_build` = 1 WHERE `apt`.`id_typeapt` in (1, 2, 3, 4, 5)
I have the result I need except one thing, the id given is null :
[ {"id_typeapt":1,"apt":[ {"id_apt":null,"image_apt":"apt_1.png"}, {"id_aptitude":null,"image_apt":"apt_2.png"} ] ]
How can I force it to look for the id from the table "apt" and not giving me "null" as a result?
Thanks you!
EDIT : Where clause is coming from the with
public function apt(){ return $this->hasMany(AptModel::class, 'id_typeapt', 'id_typeapt'); }
EDIT2 :
id_apt
was crushed by the other id_apt
with a simple rename I could retrieve the id :
return TypeModel::with(['apt' => function($q) use ($id){ $q->leftJoin('build_apt', function($join) use($id){ $join->on('build_apt.id_apt', '=', 'apt.id_apt'); $join->on('build_apt.id_build', '=', DB::raw($id->id_build)); }) }])->select(DB::raw("*, apt.id_apt as id_apt); }])
#php #mysql #sql #laravel