Finding duplicate of a relationship

Hey every one, I need to fetch every duplicate via a relationship depending on some criteria.

if:

User -> has many emails

I need to return the user object of every duplicate emails

ex:

User1 has 2 emails : [email protected] and test2@test.ca

User2 has 1 email: test3@test.ca

User3 has 2 emails: test2@test.ca and test3@test.ca

I need to show a table that would show each email that has a duplicate in an other object

[email protected]
    - User1
    - User3

[email protected]
    - User2
    - User3

I also have additional criteria on the user object to filter like User was activated between 2 dates

I tried stuff like this and it does not fail but does not work

User::whereHas('userEmails', function ($userEmails) use ($accountId) {
            $userEmails->whereIn('email', function ($query) {
                $query->select('email')->from('user_email')->groupBy('email')->havingRaw('count(*) > 1');
            });
        })
->whereBetween('user.created_at', [$startDate, $endDate])
->whereAccountId($accountId)
->get();

In other words: I need to find all user that have one or more email in common and group them by email making sure a user does not show up twice for the same email if he has twice the same email.

PS: This example looks dumbs because i should no allow a user to have twice the same email but this is not representative of the real code and real model names im working with. I just changed them to post it publictly and not show real life code

Sergiu17

8 years ago

Level 60

I can help you with duplicated emails.

So, you should have 2 models, User.php and Email.php

// Mail.php
public function users() {
    $this->hasMany(User::class);
}

$mails = Mail::with('users')->get();

foreach($mails as $mail):
    if(count($mail->users) > 1):
        foreach($mail->users as $user)
            echo $user->name;
        endforeach;
    endif;
endforeach;

I didn't test it, but it should work

AlexandrePilon

8 years ago

Level 2

@Sergiu17 That wouldn't work because the duplicate emails are not linked to many users there are juste many instances where a user is linked to an email that is the same value but is not the same record in the database. So in my example there are 2 db entries of test2@test.ca and 2 of test3@test.ca.

Please or to participate in this conversation.