Laravel雄辩地说“有”和“哪里”有多个表格

Question

I've 3 tables on my project:

1. Mall
2. Shop
3. Product

I've a page to search for products in the whole database. I need an option where they can search the product by the mall name. So I built my code like this:

$query = Product::with('shop', 'shop.mall');

if (!empty($data["keyword"])) {
    $query = $query->where(function($q) use($data) {
         $q->where('shop.mall.name', 'LIKE', '%' . $data["keyword"] . '%')->orWhere('shop.mall.keyword', 'LIKE', '%' . $data["keyword"] . '%');
    });
}

But it is showing this error:

Column not found: 1054 Unknown column 'shop.mall.name' in 'where clause'

Is there any problem with my query? Thank you

Answer 1

To search within relation use whereHas(), it creates subquery and returns data filtered in shop.mall.

    $query = Product::with('shop', 'shop.mall');

    if (!empty($data["keyword"])) {
        $query = $query->whereHas('shop.mall',function($q) uses ($data){
            $q->where('name', 'LIKE', '%' . $data["keyword"] . '%')->
              orWhere('keyword', 'LIKE', '%' . $data["keyword"] . '%');
        });
    }