Laravel whereIn AND whereIn?

Question

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?

I think you could grouBy two columns. Try `Expense:select('amount','date', DB::raw('COUNT(*) as `count`')) ->groupBy('amount', 'date') ->havingRaw('COUNT(*) > 1') ->get();` — porloscerros Ψ, Apr 16 '22 at 17:03
I actually did not think of this and it is not `exactly` what I was looking for BUT it is a good solution and perhaps in the end even works better than what I wanted. So if you can post answer I can accept it :) — Hardist, Apr 16 '22 at 18:06
Ok, I can write the answer if it solves your problem. But keep in mind that you will only get the amount and date data, that is, you will not get the id of the duplicate rows, if that is what you are looking for — porloscerros Ψ, Apr 16 '22 at 19:23
True, but they are not explicitly needed. Would be nice to get them as well but if not, no worries — Hardist, Apr 17 '22 at 10:27

score 1 · Accepted Answer · answered Apr 17 '22 at 11:00

1

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

answered Apr 17 '22 at 11:00

porloscerros Ψ

4,808
2
11
20

Thanks, accepted this because it is the closest to what I need I think. I am going to play around with collection groupBy's later just to check if I can get a better solution for my use case, although I wanted to prevent that because that way I have to get all expenses from the database first and then grouping them etc. But atm I do have a working solution thanks to your answer. – Hardist Apr 17 '22 at 11:49
1

@Hardist after the comments i was wondering how to get the id of the duplicates. I'm writing from my phone now, but passing the query from this answer to eloquent might be a solution https://stackoverflow.com/a/41754936/7498116 – porloscerros Ψ Apr 17 '22 at 12:32

Laravel whereIn AND whereIn?

1 Answers1