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
– Damodaran

2013-11-14 12:16:25 +00:00
Commented Nov 14, 2013 at 12:16 — Damodaran
– Damodaran, Commented Nov 14, 2013 at 12:16

jszobody · Accepted Answer · 2013-11-14 12:18:45Z

8

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, 2013 at 12:18

jszobody

29k6 gold badges66 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Victor Over a year ago

I can write DB::select('..')->whereIn together?

jszobody Over a year ago

The docs on that page are for the query builder, not a manual sql query

Victor Over a year ago

Well I use manual sql query. It seems a little bit difficult to put that query inside query builder?

jszobody Over a year ago

Because of your subquery? Sure it'll be a bit tricky. You can look at 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.

vishal-mote Over a year ago

->whereIn('device_id', $var) this will work with. DB::table() not with DB::select().

Community · Accepted Answer · 2017-05-23 10:30:52Z

3

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, 2017 at 10:30

CommunityBot

11 silver badge

answered Nov 14, 2013 at 12:17

Kasyx

3,19823 silver badges32 bronze badges

3 Comments

Victor Over a year ago

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?

Kasyx Over a year ago

Yes you're right with sql injection danger. You should convert all $var elements into integer.

Kasyx Over a year ago

The best option with converting is intval in my opinion: us1.php.net/intval

vishal-mote · Accepted Answer · 2015-07-27 06:39:24Z

0

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

answered Jul 27, 2015 at 6:39

vishal-mote

5022 gold badges9 silver badges23 bronze badges

Collectives™ on Stack Overflow

Laravel 4 where in condition with DB::select query

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related