how to to make eloquent scope with whereHas like sql query below?

Question

how to to make eloquent scope with whereHas like sql query below

table Property(id, title, slug, category_id, location_id,image)

table Category(id, name, slug)

table City ( id, name, slug)

The simple sql query that i need

Select * from property 
join category on property.category_id=category.id 
join city on property.location_id = city.id
where category.name = $query and city.name=$query

I want to make the eloquent scope in the Property Model

Answer 1

This is easy with relationships.

Based on your query, let's say this is your Property model:

class Property extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function city()
    {
        return $this->belongsTo(City::class, 'location_id');
    }

Now I can write my Eloquent query like this:

$query = 'Example';

$properties = Property::where('name', $query)
    ->whereHas('category', function (Builder $builder) use ($query) {
        $builder->where('name', $query);
    })->get();

Please note that Builder is imported from Illuminate\Database\Eloquent\Builder. You can also add with('category', 'city') to the query above in order to eager load those relationships.