Friday 6 September 2019

Eloquent: Composite WhereIn condition

Is there a clean way to do a composite WHERE ... IN () condition with Eloquent/laravel.

The query result would be :

SELECT * FROM `table` WHERE (relation_type, relation_id) IN (('App\\Model', 1),('App\\Model', 3))

As you can see, that would be helpful for a single query fetch of an entity with polymorphic relation linked to 5 other models.

My current solution would be pure MySQL:

//Example :array of array with Model name & id
$couples = [
    ['relation_type' => 'App\\Model', 'relation_id' => 1],
    ['relation_type' => 'App\\ModelTwo', 'relation_id' => 2],
    ['relation_type' => 'App\\ModelThree', 'relation_id' => 5],
    ['relation_type' => 'App\\ModelTwo', 'relation_id' => 20],
    //...
    ['relation_type' => 'App\\Model', 'relation_id' => 999],
];
$query = "SELECT * FROM table WHERE ('relation_type', 'relation_id') IN (("
        .implode('),(', array_map(function ($entry) {
            return "'".$entry['relation_type']."',".$entry['relation_id']; //I know , in relation_type the '\' needs to be escaped. 
        }, $couples))
        ."))";
$results = \DB::select($query);
}



from Eloquent: Composite WhereIn condition

No comments:

Post a Comment