Convert database column value to array during query buildler laravel and use wherein

✔ Recommended Answer

If $request->input('customer_id') is an integer, and column customer_id is string, add to query:

->where('customer_id', 'LIKE', '%'.$request->input('customer_id').'%')

instead of

->whereIn($request->input('customer_id'),array('permissions.customer_id'))

Edit: this solution would get all records with id 12, 20, 200 ...(every number that cointains 2).

To prevent this, you could add , (comma) to beginning and end of customer_id before storing it to database (your first row's customer_id would be ",1,2,"). Then you could add to query:

->where('customer_id', 'LIKE', '%,'.$request->input('customer_id').',%')`//added comma next to % sign

Best practice solution would probably be to use many to many relationship, more info here

Source: stackoverflow.com

Answered By: franB

Method #2

To convert a database column value to an array during query building in Laravel and use whereIn, you can make use of the DB::raw() method.

Here's an example:

php
$myArray = ['foo', 'bar', 'baz'];

$results = DB::table('my_table')
    ->whereIn('my_column', DB::raw('("' . implode('","', $myArray) . '")'))
    ->get();

In this example, we have an array $myArray which we want to use to filter rows in a database table my_table based on the values in a column my_column. We first use the implode() method to convert the array to a comma-separated string of values, wrapped in quotes. We then pass this string to the DB::raw() method, which generates a raw SQL expression that can be used in the query.

Finally, we use the whereIn() method to filter the rows based on the values in the my_column column. The whereIn() method takes two arguments: the name of the column to filter on, and an array of values to match. In this case, we pass the name of the column (my_column) and the raw SQL expression generated by DB::raw().

Note that using DB::raw() can be a potential security risk, as it allows raw SQL code to be injected into the query. Be sure to validate and sanitize any user input before using it in a raw SQL expression.