CODEWITHSUNDEEP
phplaravellaravel-5eloquent
itsliamoco
itsliamoco

Reputation: 1048

Laravel Eloquent - Most Popular Query

I have two tables, Products and Transactions. A Product can have many Transactions.

I am trying to get all products ordered by the number of Transactions in a given period of time even if a product has 0 transactions.

Products.php (model)

public function scopePopular($builder, $from, $to)
{
    return $builder
        ->withCount('transactions')
        ->whereHas('transactions', function ($transaction) use ($from, $to) {
            $transaction
                ->whereDate('created_at', '>', $from)
                ->whereDate('created_at', '<', $to);
        })
        ->orderBy('transactions_count', 'desc');
}

My problem with the above code is that not all products are returned, only products that have a transaction.

How do I return all products ordered by number of transactions in a given date period?

Upvotes: 1

Views: 495

Answers (1)

Julien METRAL
Julien METRAL

Reputation: 1974

The problem is that you are searching for products that have transactions between the two dates with the whereHas So to handle that, you can define a query for your withCount statement :

public function scopePopular($builder, $from, $to)
{
    return $builder
        ->withCount(['transactions' => function ($transaction) use ($from, $to) {
            $transaction
                ->whereDate('created_at', '>', $from)
                ->whereDate('created_at', '<', $to);
        }])
        ->orderBy('transactions_count', 'desc');
}

Upvotes: 2

Related Questions