Vito Gravano Vito Gravano - 1 month ago 4
SQL Question

Have some troubles with Laravel whereIn

So, I am trying to execute:

$table = 'russian_names';
$nameType = 1;
$options = array(1, 2, 6);
$names = DB::table($table)
->where('name_type', $nameType)
->whereIn('options', $options)
->get();


Of course, I have a row in
russian_names
:

id: 1
name: Vito
name_type: 1
options: 1,2,3,6
short_description: Short description for Vito.
description: Full description for Vito.


But I always get an empty array. I tried to convert each element in
$options
to string, but still no result. Also, I tried to execute this query without
whereIn
and it works. And also, when I exucute

select * from `russian_names` where `name_type` = 1 and `options` in (1, 2, 6)


in phpmyadmin it works perfectly. Is there anything I am missing?

UPD: mysql query log showing me this log:

select * from `russian_names` where `name_type` = ? and `options` in (?, ?, ?)


Looks normaly, because

select * from `russian_names` where `name_type` = ?


Works fine and showing me array with result.

Answer

options: 1,2,3,6

WHERE options IN (1, 2, 6) means "find rows that have an options value of 1, 2, or 6. It does not mean "magically figure out that options is a CSV string and act as if it's an array of data". You'll need to store the options in another fashion - most likely, a many-to-many sort of relationship.

Comments