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

Get Only children from hasmany relation laravel 8

I have two mysql table with HasMany/belongsTo relation like this :

public function Products()
{
    return $this->hasMany(Prices::class, 'product_id');
}

public function Prices()
{
    return $this->belongsTo(Products::class);
}

If i do the following function, i can get the latest price for every product.

public function LatestPrices()
{
    return $this->hasOne(Prices::class, 'product_id')->orderby('date','desc');
}

I can access those data, in a controller, from a foreach loop like this :

$values = Products::with('LatestPrices')->get();
foreach ($values as $value) {
  // Whatever doing here
  echo $value->LatestPrices->price;
}

My questions are please :

  • Can i get only prices without products ? Like an array with only prices from this hasmany relation ?
  • Can i get the sum of the latest prices (Order by date first) of every product without doing a foreach loop ?

Thanks in advance.

Solution:

I’m new to laravel. I’m from the old school php mysql. My main goal was to transform an inner join to something that flows with laravel eloquent. But it seems that laravel eloquent is so slow on queries relation to 2m+ rows tables.
My query is like that :

SELECT SUM(a.sells_value) AS value FROM prices a INNER JOIN products b ON a.product_id = b.id INNER JOIN ( SELECT product_id, MAX(prices.date) as Max_date FROM prices GROUP BY product_id ) c ON a.product_id = c.product_id AND a.date = c.Max_date and ((b.merchand = 1 and b.product_tag = "Something") or (b.merchand = 1 and b.product_tag = "Something"));

The solutions that was proposed by @Wahyu and @Mahdi works well and give the same result, but it take x4 time that foreach loop take :

Products::where([['merchand','=',1],['product_tag','=', 'Something']])->orWhere([['merchand','=',1],['product_tag','=', 'Something']])->with('LatestPrices')->get()->pluck('LatestPrices.prices')->sum();

So, i finally discover that i can put my original query inside DB::select(). Something like that :

DB::select('SELECT SUM(a.sells_value) AS value FROM prices a INNER JOIN products b ON a.product_id = b.id INNER JOIN ( SELECT product_id, MAX(prices.date) as Max_date FROM prices GROUP BY product_id ) c ON a.product_id = c.product_id AND a.date = c.Max_date and ((b.merchand = 1 and b.product_tag = "Something") or (b.merchand = 1 and b.product_tag = "Something"))');

and it work much faster than the foreach loop.

Thank you all for your help.

Tags:

2

Answers


  1. 0

    Can i get only prices without products ? Like an array with only prices from this hasmany relation ?

    Yes, you can modify your LatestPrices() to return only prices by using the select() :

    public function LatestPrices()
{
    return $this->hasOne(Prices::class, 'product_id')
                ->select('price')
                ->orderby('date','desc');
}

    You can retrieve the prices using pluck() :

    $prices = Products::with('LatestPrices')
    ->get()
    ->pluck('LatestPrices.price');

    Can i get the sum of the latest prices (Order by date first) of every product without doing a foreach loop?

    Yes, you can use sum() :

    $total = Products::with('LatestPrices')
    ->get()
    ->pluck('LatestPrices.price')
    ->sum();
    Login or Signup to reply.
  2. 0

    you can use raw-query in with eloquent something like that

    Products::selectRaw("select SUM(price) as total_price from prices where prices.product_id = product.id group by product_id")
->selectRaw("select GROUP_CONCAT(prices) as product_prices from prices where prices.product_id = product.id group by product_id")
->get()

    Please try it, There might be have some minor syntax issue so plz fix them by yourself. I’m trying to gives you an idea to write query that will work in your case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search