Laravel whereIn AND whereIn?-CodePudding

So I am trying to display duplicate results based on two fields, they have to both match. I have an Expense model and I want to grab only the results where the date AND amount are the same, and return those results. For example, what I have so far:

$results = Expense::where(function ($query) {
        $query->whereIn('amount', function ($q) {
            $q->select('amount')->from('expenses')->groupBy('amount')->havingRaw('count(*) > 1');
        })->whereIn('date', function ($q) {
            $q->select('date')->from('expenses')->groupBy('date')->havingRaw('count(*) > 1');
        });
    })
    ->get();

This does show me duplicate results BUT also where ONLY the amount OR date match, but I need both the amount AND the date to match. The below gives me the same results:

Expense::whereIn('date', array_column(DB::select('select date from expenses group by date having count(*) > 1'), 'date'))
    ->whereIn('amount', array_column(DB::select('select amount from expenses group by amount having count(*) > 1'), 'amount'))
    ->get());

So, for example, in my database I have the following expenses:

Name: Expense 1
Amount: 500
Date: 2022-04-16

Name: Expense 2
Amount: 500
Date: 2022-04-16

Name: Expense 3
Amount: 500
Date: 2022-04-15

The third expense should not be returned but it does get included in the results. Any pointers to get me in the right direction?

CodePudding user response：

Although it's not using whereIn, as you stated in the question, you also have the possibility to group the rows by two columns.
So, you can tell Eloquent to select the two columns you're interested in and also a count aggregate, then group by those two columns and ask for only the ones having count greater than 1.

$results = Expense:select('amount','date', DB::raw('COUNT(*) as `count`'))
    ->groupBy('amount', 'date')
    ->havingRaw('COUNT(*) > 1')
    ->get();

Following the example:

Expense 1

Amount: 500
Date: 2022-04-16

Expense 2

Amount: 500
Date: 2022-04-16

Expense 3

Amount: 500
Date: 2022-04-15

The expected results would be:

Amount: 500
Date: 2022-04-16
Count: 2