Eloquent WHERE IN using raw query / capsule manager

Question

I want to write this query in eloquent using raw queries :

INSERT INTO table SELECT * FROM other_table WHERE other_table_id IN (1,2,3)

I've tried writing it like this :

use Illuminate\Database\Capsule\Manager as DB;
$insert_db = DB::select("SELECT products_id FROM products WHERE products_id IN (?)", [[265792,265787,265743]])

But this is giving me an empty array as a result.

What is the correct way of writing this using the capsule manager? (I absolutely need to do it using DB::select)

This question is asking the same thing, back in 2013. Back then it was not possible. So did that change today? Can we use WHERE IN (?) in the latest versions of eloquent? Knowing that I NEED to use ? for security, and not concatenate the string or it would be meaningless to use PDO at all.

EDIT :

The question is more about dynamic values. I can't use :

DB::select("SELECT products_id FROM products WHERE products_id IN (?, ?, ?)", [265792,265787,265743]);

Because the array [265792,265787,265743] is being built elsewhere. So imagine the following code

foreach($test as $k => $v) {
    $arr[] = $v['something']
}

DB::select("SELECT products_id FROM products WHERE products_id IN (?)", [[$arr]]);

The syntax is wrong, you want `INSERT INTO ... WHERE SELECT products_id FROM tbl WHERE IN()...`. — Script47, Mar 07 '19 at 11:09
@Script47 yeah I changed the query to have a simpler example, as I failed to rewrite the simplified version of the insert query :-) Kindly take a look — Alexandre Elshobokshy, Mar 07 '19 at 11:10
'*But this is giving me an empty array as a result.*' - Do you have rows which actually exist for those values? — Script47, Mar 07 '19 at 11:11
@Script47 of course, I wouldn't have posted the question otherwise. `SELECT products_id FROM products WHERE products_id IN (265792,265787,265743)` does give me a result. The question is how to get those results using eloquent's PDO — Alexandre Elshobokshy, Mar 07 '19 at 11:12
If you enable the query log (`DB::enableQueryLog()`) and then output the log (`DB::getQueryLog()`), what does it show? — Script47, Mar 07 '19 at 11:15
You need a placeholder for every binding: `WHERE products_id IN (?, ?, ?)` — Jonas Staudenmeir, Mar 07 '19 at 11:33
@JonasStaudenmeir that's a good idea if you know the amount of `?` you need, but what if you don't? — Alexandre Elshobokshy, Mar 07 '19 at 11:35
@JonasStaudenmeir I'm using the latest version of *eloquent*. I'm not using Laravel. Per my tags. — Alexandre Elshobokshy, Mar 07 '19 at 11:51

Jonas Staudenmeir · Answer 1 · 2019-03-07T12:10:36.157

2

You need a placeholder for every binding:

$placeholders = implode(', ', array_fill(0, count($arr), '?'));
DB::select("SELECT products_id FROM products WHERE products_id IN ($placeholders)", [$arr]);

In Laravel/Eloquent 5.7.17+, you can use insertUsing():

DB::table('table')->insertUsing(['foo', 'bar'], function ($query) use ($arr) {
    $query->from('other_table')
        ->whereIn('other_table_id', $arr);
});

Note that the method requires you to specify the column names (['foo', 'bar']).

edited Mar 07 '19 at 12:10

answered Mar 07 '19 at 11:43

Jonas Staudenmeir

24,815
6
63
109

This might not be the cleanest answer but it's a correct answer nonetheless, I wish eloquent had a built in method to do that... thanks! – Alexandre Elshobokshy Mar 07 '19 at 11:44
I've added an alternative solution. – Jonas Staudenmeir Mar 07 '19 at 12:10
I'll keep it in mind if I ever move to the use of `DB::table` which I might in the very near future, thanks once more. – Alexandre Elshobokshy Mar 07 '19 at 12:11

Script47 · Accepted Answer · 2019-03-18T15:07:24.443

2

A cleaner approach to generate the ?, placeholders is to use str_repeat:

$in = str_repeat('?,', count($arr) - 1) . '?';

Edit #1

To handle instances where you have zero items in your array, I came up with the following function:

function in($arr)
{
  return count($arr) > 0 ? str_repeat('?,', count($arr) - 1) . '?' : false;
}

Live Example

Repl

edited Mar 18 '19 at 15:07

answered Mar 07 '19 at 11:53

Script47

14,230
4
45
66

This won't work in the case of if `count($arr)` is equal to zero. As it'll always give 1 `?` and when `count($arr) == 0` we'd want no exclamtion marks. – Alexandre Elshobokshy Mar 18 '19 at 14:48
And if there are not results, in mysql, false won't return an error? – Alexandre Elshobokshy Mar 18 '19 at 15:16
I guess, you'd first validate your array, if values exists, run the where in, otherwise don't and give an error message as correct values have not been passed (or something like that). – Script47 Mar 18 '19 at 15:17
@GiveMeFreedomOrGiveMeFire use the pre-edit code and validate before the query to check if the array has values. – Script47 Mar 18 '19 at 15:19

Eloquent WHERE IN using raw query / capsule manager

2 Answers2

Edit #1