Laravel Livewire search and filter through relationships

Viewed 1618

Sorry for my bad english. This is my first post here and I'm a kind of noob in developpment..

Thanks to a lot of reading and some tutorials, I managed to build a small Laravel application.

For various reasons, I decided to include Livewire and it's great.

However, I'm facing some issues in searching and filtering entries from different models and relationships.

Image of what I'm trying to do

I have a model called Seance and I can search through Grade, Theme and Product models (2, 3 & 5 on the image).

My Seance Model :

class Seance extends Model

{ use SoftDeletes;

protected $guarded = [];


public function product()
{
    return $this->belongsTo('App\Product');
}

public function theme()
{
    return $this->belongsTo('App\Theme');
}

public function specialty()
{
    return $this->belongsTo('App\Specialty');
}

public function grade()
{
    return $this->belongsTo('App\Grade');
}

public function courses()
{
    return $this->hasMany('App\Course');
}

public function category()
{
    return $this->belongsTo('App\Category')->withTrashed();
}

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function specialties(): BelongsToMany
{
    return $this->belongsToMany('App\Specialty', 'seance_specialty', 'seance_id', 'specialty_id');
}

public function scopeSearch($query, $term)
{
    $term = "%$term%";
    $query->where(function($query) use ($term){
        $query->where('id', 'like', $term)
              ->orWhere('name', 'like', $term)
              ->orWhere('slug', 'like', $term)
              ->orWhere('subtitle', 'like', $term)
              ->orWhere('description', 'like', $term)
              ->orWhereHas('grade', function($query) use ($term){
                $query->where('name', 'like', $term);

            })
              ->orWhereHas('product', function($query) use ($term){
                $query->where('name', 'like', $term);

            })
              ->orWhereHas('theme', function($query) use ($term){
                $query->where('name', 'like', $term);

            })
              ->orWhereHas('category', function($query) use ($term){
                $query->where('name', 'like', $term);

            });
    });

}

}

But I would like to search through the point 4 that is related to 3 and through the point 6 that is related to 5 and I don't know how to do that.

The point 3 is a Model called Theme and it belongs to a Model called Discipline (point 4)

public function discipline()
{
    return $this->belongsTo('App\Discipline');
}

The point 5 is a model called Produt and it belongs to a model called Coursetype (point 6)

public function coursetype()
{
    return $this->belongsTo('App\Coursetype');
}

I know there is join to do somewhere but I don't exactly know where...

Here's my Livewire component :

use WithPagination;
protected $paginationTheme = 'bootstrap';

public $search = "";
public $paginate = 5;
public $selectedGrade = null;
public $selectedProduct = null;
public $selectedCategory = null;
public $selectedTheme = null;
public $selectedDiscipline = null;

public function render()
{
    return view('livewire.seances-table', [
        'seances' => Seance::with('product', 'theme', 'specialty', 'grade', 'courses', 'category', 'specialties')
        ->when($this->selectedGrade, function($query){
            $query->where('grade_id', $this->selectedGrade);
        })
        ->when($this->selectedProduct, function($query){
            $query->where('product_id', $this->selectedProduct);
        })
        ->when($this->selectedCategory, function($query){
            $query->where('category_id', $this->selectedCategory);
        })
        ->when($this->selectedTheme, function($query){
            $query->where('theme_id', $this->selectedTheme);
        })
        ->when($this->selectedDiscipline, function($query){
            $query->where('discipline_id', $this->selectedDiscipline);
        })
        ->search(trim($this->search))
        ->paginate($this->paginate),
        'grades' => Grade::all(),
        'products' => Product::all(),
        'categories' => Category::all(),
        'themes' => Theme::all(),
        'disciplines' => Discipline::all()
    ]);
}

Also, I would like to filter through the Disciplines (point 4) but I get this error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'discipline_id' in 'where clause' 

I know why I'm getting this error, but I don't know how to fix it.

Sorry if all this is not very clear.

Any help would be much appreciated.

Thanks you all and cheers from France

1 Answers

since discipline_id is under themes table which is related to Seance model.

I suggest doing

$query->where('theme.discipline_id', $this->selectedDiscipline);
Related