Home / Questions / 320526
Accepted
Juan Pablo B
Asked: 2020-01-09 09:57:51 +0800 CST 2020-01-09 09:57:51 +0800 CST

Filter by whereHas doesn't work

  • 772

I have in laravel a model called cropGathering that has a hasMany relationship with cropGatheringTransaction. The latter has a tons attribute, so what I need is to fetch all the cropGatherings, where the sum of the tons of their transactions is greater than 0.

In the cropGathering model I have the relationship:

 public function cropGatheringTransactions()
    {
        return $this->hasMany(CropGatheringTransaction::class);
    }

What I tried and had no success was the following:

  public function all($perPage, $orderBy = 'id', $order = 'desc')
    {
        return CropGathering::whereHas('cropGatheringTransactions', function ($q) {
                $q->groupBy('crop_gathering_transaction.id')
                    ->havingRaw('SUM(crop_gathering_transaction.tons) > 0');
            })
            ->orderBy($orderBy, $order)
            ->paginate($perPage);
    }

I also tried the following, I defined in the cropGathering model an append:

protected $appends = ['tons'];

where I get the sum of their transactions:

 public function getTonsAttribute()
    {
        return $this->cropGatheringTransactions()->sum('tons');
    }

And when testing:

CropGathering::sum('tons', '>', 0)

tells me:

Undefined column: 7 ERROR: column \"tons\" does not exist

laravel

1 Answers

  1. Best Answer
    public function all($perPage, $orderBy = 'id', $order = 'desc')
{
    $data = CropGathering::whereHas('cropGatheringTransactions', function ($query) {
                $query->select('crop_gathering_transaction.id')
                      ->selectRaw('SUM(crop_gathering_transaction.tons) AS Total')
                      ->groupBy('crop_gathering_transaction.id')
                      ->havingRaw('Total > 0');
            })->orderBy($orderBy, $order)->paginate($perPage);

     return $data;
}

    It seems to me that the first query you show is misspelled;

    Should have:

    • One selectthat takes at least the one idfrom the table to be able to group later
    • Later with the use of selectRawwe write the aggregation function to sum the columntons
    • Once that is done, we group by the column idso we make sure that the records that match this value are merged into a single row
    • We use havingRawto filter by the alias that we gave to the aggregation function, which is Total (remember that if the value to compare comes from an aggregation function, then we will use HAVING)
    • Except for some other comment, it seems to me that in your query you cannot group as you intend since you have not put selectthat column in it.

    Regarding the issue of pagination, you should check the doc. official , since it mentions that pagination operations on results that require agroupBy()

    • 2