How to retrieve rows with similar columns to a previously fetched one?

Question

I have some cars and I want to check whether there are other cars similar to one of them or not.

So I have:

$car = Car::where('car_id', $carId)->first();
if($car)
{
    $duplicateCar = Car::where('car_code', $car->car_code)->first();
    if($duplicateCar)
    {
        //Do sth
    }
}

However my conditions for the second query are more complex.

I want to fetch all rows and check whether column1, column2 and other columns are same as the desired car, if yes $conditionTrueCount to increment and if $conditionTrueCount is upper than 5 so it is duplicate.

So I have:

$car = Car::where('car_id', $carId)->first();
if($car)
{
    $cars = Car::all();
    foreach($cars as $car2)
    {
        $conditionTrueCount = 0;
        if($car->car_code == $car2->car_code)
            $conditionTrueCount++;
        if($car->column1 == $car2->column1)
            $conditionTrueCount++;
        if($car->column2 == $car2->column2)
            $conditionTrueCount++;
        if($car->column3 == $car2->column3)
            $conditionTrueCount++;
        if($car->column4 == $car2->column4)
            $conditionTrueCount++;
        if($car->column5 == $car2->column5)
            $conditionTrueCount++;
        if($car->column6 == $car2->column6)
            $conditionTrueCount++;

        if($conditionTrueCount > 5)
        {
            //It is duplicate, do something!
        }
    }
}

So you want to count how many cars have the same `car_code`? — hungrykoala, Jun 18 '18 at 06:17
Instead `$cars = Car::all();` and the loop, you can try something like `Car::where('car_code', $car->car_code)->andWhere('column1', $car->column1)->andWhere('column2', $car->column2)->(.......)->first();` then check with if statement `$cars` — codtex, Jun 18 '18 at 06:19
how many cars have the same `car_code`, how many cars have the same `column2`, how many cars have the same `column3` and so on... and if the number of `$conditionTrueCount` gets upper than 5 so it's duplicate. — kodfire, Jun 18 '18 at 06:20
@codtex By the one you said each one that gets true will be included in the result collection however I want if the true conditions are more than 5 to fetch. Is it possible? — kodfire, Jun 18 '18 at 06:21
You can use the in_array function. See: https://stackoverflow.com/questions/2426557/array-unique-for-objects — Ronald, Jun 18 '18 at 06:42
Why not use a `GROUP BY key1, key2....key5` and do a `select count(*) as num, ... having count(*)>NUMBER`? — web-nomad, Jun 18 '18 at 08:34

yivi · Accepted Answer · 2018-06-18T13:21:38.840

Wouldn't make more sense to make the query in the DB, instead of checking all the elements manually in the script?

$cars = Car::where(function($query) use ($car) {
                         $query->where('column1', '=',  $car->column1)
                               ->orWhere('column2', '=', $car->column2)
                               ->orWhere('column3', '=', $car->column3)
                               ->orWhere('column4', '=', $car->column4)
                               ->orWhere('column5', '=', $car->column5)
                               ->orwhere('car_code', '=', $car->car_code);
                     })
              ->where('car_id', '!=', $car->car_id)->get();

This should get all the cars that match one or more of these columns with the car you are starting with, excluding the car_id you've already got.

Just tested this and it works as expected. This would be a bit more efficient than getting all the cars and just loop them around...

If you want to count the number of "matches", to get a degree of "similarity", you could do it with an SQL sentence as:

select car_id, car_name,
  CASE WHEN column1 = ? THEN 1 ELSE 0 END + 
  CASE WHEN column2 = ? THEN 1 ELSE 0 END +
  CASE WHEN column3 = ? THEN 1 ELSE 0 END +
  CASE WHEN column4 = ? THEN 1 ELSE 0 END +
  CASE WHEN column5 = ? THEN 1 ELSE 0 END sumConds
FROM cars
HAVING sumConds < ?

Writing this "eloquently":

$cars2 = Car::selectRaw("car_id, car_name,
                         CASE WHEN column1 = {$car->column1} THEN 1 ELSE 0 END +
                         CASE WHEN column2 = {$car->column2} THEN 1 ELSE 0 END +
                         CASE WHEN column3 = {$car->column3} THEN 1 ELSE 0 END +
                         CASE WHEN column4 = {$car->column4} THEN 1 ELSE 0 END +
                         CASE WHEN column5 = {$car->column5} THEN 1 ELSE 0 END sumConds")
            ->having("sumConds",  "<",  5)
            ->having("sumConds", ">", 0)
            ->get();

(Get all the "similar" car ids and car names, with at least one degree of similarity, and get the similarity degree as well).

I guess that from here you can get a full working solution adjusted to your use-case.

As I mentioned for the 4th time for all users providing this answer and deleted after some time so my comment on their posts are removed. Anyways in your query it will check each column and if one of them gets true it will return that HOWEVER I want to count true conditions and if the number of true conditions is more than 5 so it's true and go to next operation. if first second third fourth fifth columns match return true and if first second third fourth columns match don't. — kodfire, Jun 18 '18 at 10:56
I've updated the answer with an SQL query that will produce what I think you want. Converting this to an eloquent query is up to you. @kodfire Obviously sumConds holds the "similarity index" in this case, and the last parameter is the maximum similarity to have, if you want to filter by that. — yivi, Jun 18 '18 at 11:30
@kodfire Finally updated the answer to include the same query going through Eloquent. You should be able to go on from this. Let me know if this helps you — yivi, Jun 19 '18 at 15:51
GREAT! Thanks @yivi for your efforts. BTW is there any function in MySQL same as `similar_text` in php? One of my conditions should check for its similarity with a column. — kodfire, Jun 19 '18 at 15:58

How to retrieve rows with similar columns to a previously fetched one?

1 Answers1