lundi 29 août 2016

Laravel 5. DB::select working principles

Here is the raw SQL query:

    $best_price = DB::select('select id,product_id, shop_name, product_name, brand, weight_volume, min(price)

    FROM (
            select
    p.id, p.product_id, p.shop_id, s.shop_name, pr.product_name, pr.brand, coalesce(pr.weight, pr.volume) weight_volume, p.price

    from prices p
    inner join (
            select p1.id price_id,p1.product_id id, max(p1.created_at) maxed_date

    from prices p1

    where product_id= ?

    group by p1.id, p1.product_id, p1.shop_id) grouped on grouped.maxed_date=p.created_at and grouped.price_id=p.id
    join products pr on pr.id=p.product_id
    join shop_names s on s.id=p.shop_id

    group by p.id, p.product_id, p.shop_id) grouped

    GROUP BY product_id,shop_name', [$request->product]);

In DBeaver I get result of one line with min(price) of "Metro C&C".

But if I do dd($best_price) I get the following result:

array:2 [▼
  0 => {#158 ▼
    +"id": 46
    +"product_id": 69
    +"shop_name": "Metro C&C"
    +"product_name": "Cream"
    +"brand": ""Tastyland""
    +"weight_volume": 0.22
    +"min(price)": 300.0
  }
  1 => {#160 ▼
    +"id": 47
    +"product_id": 69
    +"shop_name": "Klopshop"
    +"product_name": "Cream"
    +"brand": ""Tastyland""
    +"weight_volume": 0.22
    +"min(price)": 300.0
  }
]

And if I do

foreach($best_price as $best_price_id) {
            return $best_price_id->shop_name;
        };

I received the following result "Metro C&C".

I have the following questions:

  1. How does Laravel understand, that there are two results with minimum price of 300(actually this is the result that I expect) while DBeaver provides only one? How should I change the SQL query to get result of 2 rows?

  2. Why does foreach return only the first row?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire