Question 300721 in Laravel Question posted in
The official Laravel Documentation can be found here.

Find group and sorting data in laravel

I have two tables, one is products and the other is varients.
In product table I store product basic data such as name, image, brand, etc. In varients table I store variants of product such as 1kg, 2kg, 5kg, etc

varients Table

id product_id varientName price
1 1 2kg 100
2 1 1kg 50
3 1 5kg 480
4 2 250g 25
5 2 100g 10
6 2 500g 50

products Table

id Name
1 rice
2 tea

I want to fetch data from both table such as – one product variant which one has lowest price.

I try this:

$bestSellingProduct = DB::table('products')
       ->join('varients', 'products.id', '=', 'varients.product_id')   
        ->select('products.product_id','products.name',,'varients.varientName','varients.id','varients.price')       
        ->orderBy('varients.varientPrice','ASC')
        ->groupby('products.id')
        ->get();

I want to fetch products and their variants, such as rice 1kg (50) and tea 100g (50).

Tags:

2

Answers


  1. 0

    Basic SQL for finding minimum value and also other columns of the min value record can be achieved using window function:

    SELECT T.ID, T.NAME, T.VARIENTNAME, T.PRICE 
FROM
(SELECT P.ID, P.NAME, V.VARIENTNAME, V.PRICE,
       ROW_NUMBER() OVER (PARTITION BY P.ID ORDER BY V.PRICE) RN
FROM PRODUCTS P 
     JOIN VARIENTS V ON P.ID = V.PRODUCT_ID) T
WHERE T.RN = 1
    Login or Signup to reply.
  2. 0

    If you don’t want to use Eloquent then do it like this.

    $bestSellingProduct = DB::table('products')
    ->join('variants', 'products.id', '=', 'variants.product_id')
    ->select(
        'products.product_id',
        'products.name',
        'variants.variantName',
        'variants.id',
        'variants.price',
        DB::raw('MIN(variants.price) as lowest_price')
    )
    ->groupBy('products.id')
    ->having('variants.price', '=', 'lowest_price')
    ->get();

    and if want to use Eloquent then do it like this and I assume that you have the all the relationships already there.

    $bestSellingProducts = Product::with(['variants' => function ($query) {
    $query->orderBy('price')->limit(1);
}])->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search