0

I am using Laravel with MongoDB and jenssegers package. I have a collection called ProductDetails in which I have three fields for which I need to retrieve the average value: price, margin and weight. I can make three different queries but I would like to get that in one query. I think it would be cleaner. I tried using raw expressions but I cannot make it work. I can't even retrieve the average of a single value. I have seen many people using mongoDB aggregation but I don't see why and how it would be useful here.

Here's what I'd love to achieve:

$productDetails = ProductDetails::select('AVG(price) as avg_price','AVG(margin) as avg_margin','AVG(weight) as avg_weight')
  ->where('id', '=', $id)
  ->where('active', '=', true)
  ->get();

It works when I try to retrieve the values (not the average) but the select does not work with AVG() which is why I wanted to use raw expressions

This is an unfruitful attempt:

  $productDetails = ProductDetails::raw(function($collection)
  {
    return $collection ->find([
      'name' => 'First product',
      'avg_price' => ['$avg' => "price"]
    ]);

  })
Thbwun
  • 323
  • 1
  • 3
  • 14

1 Answers1

0

you just have to use selectRaw :

$productDetails = ProductDetails::selectRaw('AVG(price) as avg_price, AVG(margin) as avg_margin, AVG(weight) as avg_weight')
  ->where('id', '=', $id)
  ->where('active', '=', true)
  ->get();

be careful about the ' location, it should be around the hole statement in select raw expressions.

but :

 ->where('id', '=', $id)

this statment will make the query return only one row, I think you mean

 ->where('product_id', '=', $id)

is not?

OMR
  • 11,736
  • 5
  • 20
  • 35
  • I get an `Illegal offset type` error on the `get()`. Even if I only keep the `selectRaw` and the `get` and remove the where conditions. It should indeed be product_id ! – Thbwun Nov 24 '21 at 14:11
  • mmm, I have tested the code I sent to you about selectRaw, other things I will let the decision to you – OMR Nov 24 '21 at 14:17