Check Multiple Eloquent User Model Results and Find Duplicates

35 views Asked by At

I am running various Eloquent queries on our User table to find member matches for the currently logged in member based on various criteria.

One query returns matching members based on the places the currently logged in member most wants to visit vs the places the matching members also most want to visit.

$match1 = User::with(['country', 'cityLocation']);
$match1 = $match1->where(function ($query) use($memberFavoPlaceArrayCleaned) {
    foreach($memberFavoPlaceArrayCleaned as $searchWord){
        if ($searchWord != "") {
            $query = $query->orWhere('favoPlace', 'LIKE', '%'. $searchWord .'%');
        }
    }
    return $query;
});
$match1 = $match1->where("id", "!=", strval($member->id));
$match1 = $match1->where("userSuspended", "!=", 1);
$match1 = $match1->where("meetup", "!=", 0);
$match1 = $match1->where(function ($query) {
    return $query->where('status', '=', 'approved')
    ->orWhere('status', '=', 'approvedfinal');
});
$match1 = $match1->orderBy('id','desc');
$match1 = $match1->skip(0)->take(20);
$match1 = $match1->get();

Another returns members who are located where the currently logged in member wants to visit.

$match2CountryIDs = FbcCoreCountry::where('active', 1);
$match2CountryIDs = $match2CountryIDs->where(function ($query) use($memberFavoPlaceArrayCleaned) {
    foreach($memberFavoPlaceArrayCleaned as $searchWord){
        if ($searchWord != "") {
            $query = $query->orWhere('nicename', '=', $searchWord);
        }
    }
    return $query;
})
->orderBy('nicename')
->get();

// Now return all members in those country IDs
if ($match2CountryIDs->isEmpty()) {
    $match2 = null;
} else {
    $match2 = User::with(['country', 'cityLocation']);
    $match2 = $match2->where(function ($query) use($match2CountryIDs) {
        foreach($match2CountryIDs as $country){
            if ($country->id != "") {
                $query = $query->orWhere('countryId', '=', $country->id);
            }
        }
        return $query;
    });
    $match2 = $match2->where("id", "!=", strval($member->id));
    $match2 = $match2->where("userSuspended", "!=", 1);
    $match2 = $match2->where("meetup", "!=", 0);
    $match2 = $match2->where(function ($query) {
        return $query->where('status', '=', 'approved')
        ->orWhere('status', '=', 'approvedfinal');

    });
    $match2 = $match2->orderBy('id','desc');
    $match2 = $match2->skip(0)->take(20);
    $match2 = $match2->get();
}

I now want to see if any members appear in both $match1 and $match2 as these will be deemed a better match for the current member than those members who appear in only one of the results.

Actually in practice there are more than two queries as we have additional matching criteria. In reality we have $match1, $match2, $match3, $match4, $match5 but the above should be enough to show how the queries are built.

What I need to achieve is as follows:

Check all results against each other and create a new result that includes all returned members with one entry for each member. I also need to add a new column for each member in the result - resultCount - that is a numeric value showing how many results the member was found in - 0, or 1, or 2, or 3, or 4, or 5.

Each member should appear only once in the final result, with all its data, plus the additional value for resultCount.

What is the best way to achieve this in Laravel / Eloquent please?

0

There are 0 answers