laravel join and where condition on two tables with pagination

Question

I have this:

Products

+--------------+--------------+
| id  | name   | description  |
+--------------+--------------+
| 1   | shirt  |  some desc   |
| 2   | shirt_b|  some desc   |
| 3   | shoe   |  some desc   |
| 4   | shirt_c|  some desc   |
+--------------+--------------+

Product_metas

--------------+---------+--------+
| product_id  |  color  |  price |
--------------+---------+--------+
|      1      |  black  |  2000  |
|      1      |  red    |  6000  |
|      1      |  brown  |  8000  |
|      2      |  black  |  6000  |
|      2      |  green  |  4000  |
|      3      |  blue   |  7000  |
|      4      |  red    |  9000  |
|      4      |  blue   |  2000  |
|      4      |  black  |  8000  |
--------------+--------+---------+

user wants to search with product name and price range like this :
keyword : "shirt" , min-price: "5000", max-price: "10000"

and result of this search should be like this (with pagination):

[
    0 : [
            Name : shirt
            Desc : some desc
            metas [
                0 : [red   , 6000],
                1 : [brown , 8000],
            ]
        ],
    1 : [
            Name : shirt_b
            Desc : some desc
            metas [
                0 : [black   , 6000],
            ]
        ],
    2 : [
            Name : shirt_c
            Desc : some desc
            metas [
                0 : [red     , 9000],
                1 : [black   , 8000],
            ]
        ],
]

Controller:

$products = Product::where('name', 'like', '%' . $request->input('keyword') . '%')
    ->whereHas('metas', function($query) use ($request) {
        $query->whereBetween('price', [
            $request->input('price_min'),
            $request->input('price_max')
        ]);
    })
    ->paginate(10);

return view('/home' , compact(['products']));

view:

<div>
    {!! $products->render() !!}
    @forelse($products as $product)
        <ul>
            <li><strong>Name : </strong>{{ $product->name }}</li>
            <li><strong>Desc : </strong>{{ $product->desc }}</li>
            <ul>
                @forelse($product->metas()->get() as $meta)
                    <li><strong>Color : </strong>{{ $meta->color }}<strong> --> $ </strong>{{ $meta->price }}</li>
                @empty
                    <h3>no meta</h3>
                @endforelse
            </ul>
        </ul>
    @empty
        <h3>no data</h3>
    @endforelse
</div>

how i should query in controller and show them in view with pagination ?

Answer 1

As said in comments, whereHas just filters results based on the callback. But the rest of the comment was wrong. To include the values from the other table, use with as well as whereHas, not instead of it.

$products = Product::where('name', 'like', '%' . $request->keyword . '%')
    ->whereHas('metas', function($query) use ($request) {
        $query->whereBetween('price', [$request->price_min, $request->price_max]);
    })
    ->with('metas')
    ->paginate(10);

return view('/home' , compact('products'));

Assuming your Product and Meta models have proper relationships, this will generate the following SQL queries, returning the results you want:

select * from `products` where `name` like '%shirt%' and exists (select * from `product_metas` where `products`.`id` = `product_metas`.`product_id` and `price` between 5000 and 1000);
select * from `product_metas` where `product_metas`.`product_id` in (1, 2, 4)

Not ideal that it runs two queries, but that's Laravel.

In your layout, no need to use the relation method. Just use the property instead:

@forelse($product->metas as $meta)

Answer 2

Define models and raletions. In Products model:

        public function metas(){       
            return $this->hasMany('ProductMetas');
        }

Then

$products = Products::with('metas')->where('name',$name)->where('price','=>', $LB)->where('price','<=',$UB)->simplePaginate(15);
return View::make('view.name', compact('products'));

Remember to get name, and price bounds from input.