How bind named parameter in raw sql WHERE IN [LUMEN/LARAVEL]

Question

Lumen has DB facade with select method

$results = DB::select('select * from users where id = :id', ['id' => 1]);

But this dont work in WHERE IN case.

DB::connection('db')->selectOne("many lines of sql WHERE my_id IN (:my_id) ", ["my_id" => $new_ids]);

Of course, it is possible do with query builder,

DB::connection()->table('users')->select()->whereIn(id, $new_ids)

But I want to save raw SQL to easier debugging in future, cause my sql is too comprehensive with many joins.

What is the value of `$new_ids`? It should be a string, comma separated. I really think the query builder is easier to maintain if used correctly. And will decrease the chance of getting SQL errors. — Gert B., Sep 28 '21 at 05:46

score 0 · Answer 1 · answered Sep 28 '21 at 08:22

So I did this with some compromise.

I used query builder but I could save all my joins in raw format.

IMHO, I think comprehensive joins more readable in raw format, than in query builder using anonymous functions.

My code

DB::connection('super_db')->query()
            ->selectRaw(
            "very long sql INNER JOIN AND LEFT JOIN ON and other stuff...
                ")
            ->whereIn('my_id', $new_ids)
            ->groupBy('some_column')
            ->get();

So, sql before WHERE section I can write in raw format, but after section in QB format.

But now it works, safe and quoted.

How bind named parameter in raw sql WHERE IN [LUMEN/LARAVEL]

1 Answers1