Where In multiple columns

Published 3 months ago by ARCANEDEV

I have a table with something like this:

dummies
  - id
  - morphable_type
  - morphable_id

And i want to query this table with the Eloquent method whereIn(...):

$values = [
    ['type_a', 1],
    ['type_a', 2],
    ['type_b', 1],
    ['type_c', 1],
];

$dummies = Dummy::whereIn(['morphable_type', 'morphable_id'], $values)->get();

Any one has any idea how to achieve that ?

Works fine with a direct SQL but not with Eloquent class.

SELECT * FROM `dummy` 
WHERE (dummy.morphable_type, dummy.morphable_id) 
IN (('type_a', 1), ('type_a', 2), ('type_b', 1), ('type_c', 1))
ARCANEDEV

Hi @JeffreyWay, you need to fix the SQL highlight ;)

shez1983

wow didnt know you could do that - i have just checked whereIn func dont think you can do it that way..

i did find another func that seemed possible

    public function whereColumn($first, $operator = null, $second = null, $boolean = 'and')

while digging through Eloquent.. look for it in code and see what it does maybe it is what you are looking for if not then create a scope on Dummy Model and in it do something like

function scopeSomething($q, $array)
{
     return $q->where(function($q) use ($array) {
        foreach($array as $k => $v){
            $q->whereIn($k, $v);
        }
    });
}

last option is to do whereRaw();

ARCANEDEV

Hi @shez1983, i've just tried the whereRaw()but it's not very eloquent.

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Dummy extends Model
{
    /**
     * @param  array                                          $columns
     * @param  \Illuminate\Contracts\Support\Arrayable|array  $values
     *
     * @return \Illuminate\Database\Query\Builder
     */
    public static function whereInMultiple(array $columns, $values)
    {
        $values = array_map(function (array $value) {
            return "('".implode($value, "', '")."')"; 
        }, $values);

        return static::query()->whereRaw(
            '('.implode($columns, ', ').') in ('.implode($values, ', ').')'
        );
    }
}

So the usage will be something like that:

$values = [
    ['type_a', 1],
    ['type_a', 2],
    ['type_b', 1],
    ['type_c', 1],
];

dd(
    Dummy::whereInMultiple(['morphable_type', 'morphable_id'], $values)->get()
);

Cronix
Cronix
3 months ago (479,720 XP)

Yes, it's open to sql injection since it's not using binding at all.

Cronix
Cronix
3 months ago (479,720 XP)

What's wrong with

$model = SomeModel::where(someCondition);

foreach ($values as $key => $value) {
    $model->where($key, $value);
}

$model->get();
ARCANEDEV

Not the same, try to create the dummies table and check the result.

This is just an easy example but i'm using a complex table with a polymorphic columns.

SQL:

--

CREATE TABLE `dummies` (
  `id` int(10) NOT NULL,
  `morphable_type` varchar(255) NOT NULL,
  `morphable_id` int(10) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--

INSERT INTO `dummies` (`id`, `morphable_type`, `morphable_id`) VALUES
(1, 'user', 1),
(2, 'user', 2),
(3, 'user', 4),
(4, 'user', 5),
(5, 'admin', 1),
(6, 'moderator', 1),
(7, 'admin', 2),
(8, 'user', 3);
ARCANEDEV

Link to posted laravel/internals issue

ARCANEDEV

My latest workaround if anyone is interested: link

Shahrukh4

exactly what I am looking for, Thanks @shez1983

shez1983

@Shahrukh4 dont forget the sql injection others pointed out

Please sign in or create an account to participate in this conversation.