Binding string of ids to DB::raw WHER IN

Have been trying every variant there is, but this query keeps on failing or only returning one row instead of more. The problem is that I want to bind a string of integers to a WHERE IN select:

// the original input is an array of ints
$inputids = array(1880000838,1880000878,1880000196);
// make it a string to be used in the WHERE IN
$stringids = implode(",", $inputids);
// the query
$sql = "SELECT 
            projects.`id`,
            projects.`title`,
            projects.`description`
        FROM projects
        WHERE projects.`id` IN (?)
        GROUP BY projects.`id`
        ORDER BY projects.`id` ASC";
// results correctly in 3 rows, if I replace the ? directly with the string, so using NO BINDINGS
$rows = DB::select(DB::raw($sql));

// No results
$rows = DB::select( 
    DB::raw($sql, array( $stringids))
);

// Call to a member function setBindings() on a non-object 
$rows = DB::select( 
            DB::raw($sql) 
)->setBindings([$stringids]);

// no results
$rows = DB::select(
    DB::raw($sql, array('projectids' => $stringids))
);

// no results
$rows = DB::select(
    DB::raw($sql, array($stringids))
);

// only 1 row instead of 3
$rows = DB::select(
    DB::raw($sql), array($stringids)
);

dd($rows);

I could also put the string directly into the sql query, but I'd prefer to bind it from a security perspective.

Also tried the different ways with named params as explained here http://fideloper.com/laravel-raw-queries but that didn't work either.

JarekTkaczyk

11 years ago

Best Answer

Level 53

@Mattiman You can't do it this way. When you provide comma separated string as single binding to PDO, you will get basically this:

select * from table where id in ('1,2,3,4,5')
// equivalent to
select * from table where id = 1

And you need

select * from table where id in (1,2,3,4,5)

So do this:

select * from table where id in (?,?,?,?,?)

And here's the easiest way that I suggest:

$inputids = array(1880000838,1880000878,1880000196);
$bindingsString = trim( str_repeat('?,', count($inputids)), ','); // '?,?,?'
// or
$bindingsString = implode(',', array_fill(0, count($inputids), '?')); // '?,?,?'

$sql = "select ... where id in ( {$bindingsString} ) ... "
DB::select($sql, $inputids);

5 likes

pedroborges

11 years ago

Level 6

If the Query Builder is an option, why not do:

$inputIds = [1880000838, 1880000878, 1880000196];

DB::table('projects')
    ->select('id', 'title', 'description')
    ->whereIn('id', $inputIds) // pass an array
    ->groupBy('id')
    ->orderBy('id', 'ASC')
    ->get();

1 like

Mattiman

11 years ago

Level 7

@JarekTkaczyk That is indeed the issue. Even when it worked, I got only one result.

Tried both your suggestions and they work fine it seems. Thanks!

@pedroborg_es Normally I would do that, but in this case my query is 25 joins and lots of GROUP_CONCAT stuff. Impossible or very difficult to do when building the query in Query Builder.

willvincent

11 years ago

Level 54

Impossible or very difficult to do when building the query in Query Builder.

Would have a large amount of unnecessary overhead from php too vs just running a raw query.

Mattiman

11 years ago

Level 7

@willvincent yes that's probably the case as well.

Please or to participate in this conversation.