Search HasManyThrough

Question

I want to display all kelompoks that have the same kantors based on search keyword.

i had 3 tables

Kantor

id

nama

Gugus

id

nama

id_kantor

Kelompok

id 

nama

id_gugus

here is my controller :

$kelompok=kelompok::with('bidang', 'kantor')->whereHas('kantor', function($q) use ($key) {
            $q->where('nama', 'like', '%'.$key.'%');
        })->orWhereHas('bidang', function($q) use ($key) {
            $q->where('nama', 'like', '%'.$key.'%');
        })->orWhere('nama', 'like', '%'.$key.'%')->get();

and my model :

Kelompok.php

public function bidang()
    {
        return $this->belongsTo('App\gugus', 'id_gugus');
    }

    public function kantor()
    {
        return $this->hasManyThrough('App\kantor', 'App\gugus', 'id_kantor', 'id_gugus', 'id', 'id');
    }

Gugus.php

public function kantor() { return $this->belongsTo('App\kantor', 'id_kantor'); }
public function kelompok()
{
  return $this->hasMany('App\kelompok', 'id_gugus');
}

Kantor

public function gugus()
{
    return $this->hasMany('App\gugus', 'id_kantor');
}

here is the output (error) i currently got :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'nama' in where clause is ambiguous (SQL: select * from `kelompoks` where exists (select * from `kantors` inner join `guguses` on `guguses`.`id` = `kantors`.`id_gugus` where `kelompoks`.`id` = `guguses`.`id_kantor` and `nama` like %gov%) or exists (select * from `guguses` where `kelompoks`.`id_gugus` = `guguses`.`id` and `nama` like %gov%) or `nama` like %gov%)

sorry for my bad english, hope you can understand it :D

Try using `tablename.columnname` instead of only column name and it should work fine. — Prafulla Kumar Sahu, Feb 12 '20 at 06:18
Posted as answer, try using like that I have done in answer and tell me, if you are still facing any issue. — Prafulla Kumar Sahu, Feb 12 '20 at 06:24

score 0 · Answer 1 · answered Feb 12 '20 at 08:58

I've solved it, i end up using nested wherehas, not pretty but get the job done.

$kelompok=kelompok::with('bidang')->whereHas('bidang', function($q) use ($key) {
        $q->where('nama', 'like', '%'.$key.'%');
    })->orWhereHas('bidang', function($q) use ($key){
        $q->whereHas('kantor', function($q) use ($key){
            $q->where('nama', 'like', '%'.$key.'%');
        });
    })->orWhere('nama', 'like', '%'.$key.'%')->get();

Search HasManyThrough

1 Answers1