Laravel 4 where in condition with DB::select query

Question

I have next SQL query:

SELECT summary_table.device_id, WEEKDAY(summary_table.day) as day, AVG(summary_table.shows) as avg_shows
    FROM (
         SELECT device_id, day, sum(shows) as shows
         FROM statistics
         GROUP BY device_id, day
    ) as summary_table
    WHERE device_id IN (1,2,3) // Just for example
    GROUP BY device_id, WEEKDAY(day)

How should I execute this using Laravel? I put this query in DB::select function, but how can I place all ids in "WHERE device_id IN (?)" condition? I tried using "array(implode(',', $var))" but it doesnt work. If I have ids like "13, 14" I get results only for id = 13.

So the question is how to place instead of "?" array of ids?

Show code that you tried so far – Damodaran Nov 14 '13 at 12:16 — Damodaran, Nov 14 '13 at 12:16

score 9 · Answer 1 · answered Nov 14 '13 at 12:18

9

Take a look at the docs here, scroll down to "Using Where In With An Array":

http://four.laravel.com/docs/queries

The whereIn method takes an array, so pass in $var directly, no need to implode.

->whereIn('device_id', $var)

answered Nov 14 '13 at 12:18

jszobody

28,495
6
61
72

I can write DB::select('..')->whereIn together? – Victor Nov 14 '13 at 12:20
The docs on that page are for the query builder, not a manual sql query – jszobody Nov 14 '13 at 12:20
Well I use manual sql query. It seems a little bit difficult to put that query inside query builder? – Victor Nov 14 '13 at 12:23
Because of your subquery? Sure it'll be a bit tricky. You can look at http://laravel.com/docs/queries#advanced-wheres to see how subqueries work with the querybuilder. Ultimately I think you either use query builder to take advantage of proper binding, or go with @Kasyx's answer and do it manually all the way. – jszobody Nov 14 '13 at 12:28
->whereIn('device_id', $var) this will work with. DB::table() not with DB::select(). – vishal-mote Jul 27 '15 at 06:40

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

Laravel is using PDO library. Sadly, binding with PDO doesn't work with WHERE IN. You can read about it here.

So what you have to do is to put prepared string in your query like this:

"(...)
    WHERE device_id IN (".array(implode(',', $var)).")
    (...)"

edited May 23 '17 at 10:30

Community

1
1

answered Nov 14 '13 at 12:17

Kasyx

3,170
21
32

I thought about it. The only trouble is to prevent SQL injection. I've always trusted laravels functions like ->where(). Should I loop through $var and convert in to int like (int)$item before putting it inside implode? – Victor Nov 14 '13 at 12:22
Yes you're right with sql injection danger. You should convert all $var elements into integer. – Kasyx Nov 14 '13 at 12:29
The best option with converting is `intval` in my opinion: http://us1.php.net/intval – Kasyx Nov 14 '13 at 12:31

score 0 · Answer 3 · answered Jul 27 '15 at 06:39

0

"(...)
    WHERE device_id IN (".implode(',', $var).")
    (...)"

answered Jul 27 '15 at 06:39

vishal-mote

362
2
6
22

Laravel 4 where in condition with DB::select query

3 Answers3

Linked