Level 51
How the arrary is generated at the begging?
1 like
Jan 18, 2023
6
Level 8
How to use raw sql query string that is created dynamically?
I get an array of arrays that represent the values I need to use when I construct the sql query string. For example:
[
["a" => "a1", "b" => "b1", "c" => "c1"],
["a" => "a2", "b" => "b2", "c" => "c2", "d" => "d2"]
]
Then I need to create some complex query that's impossible to write with Laravel's query builder and uses the dynamic data from above:
SELECT
...
WHERE (a="a1", b="b1", c="c1")
OR WHERE (a="a2", b="b2", c="c2", d="d2")
...
From older posts I've seen here and on SO, it was mentioned I can use
$result = DB::select($query_string):
Or even with DB::statement.
But, I'm not sure it's still a good way in Laravel 8 and above because it's not in the docs. (But it is in the API docs I think)
But even if it is, it means I'll put the string as is without taking care of binding the values to prevent sql injection.
So how can I do it in the case?
Ty
Level 51
have you tried to pass the array??
[$one, $two] = [
["a" => "a1", "b" => "b1", "c" => "c1"],
["a" => "a2", "b" => "b2", "c" => "c2", "d" => "d2"]
];
Model::where($one)
->orWhere($two)
1 like
Level 73
This sounds like it would be wide open for SQL injection. Be very careful with this.
1 like
Level 8
@mohamedtammam I get the array as is from the frontend
@webrobert This works if the query is simply as you wrote it, but in my case there's a complex query between the SELECT and WHERE like using CASE and COUNT and UNION, I found it difficult to convert it to query builder
@tray2 exactly :/, I just wonder how am I supposed to convert it then. I did see the select() method accepts second "binding" argument which I might find a way to use it, but I am not sure how to match each array to the correct "WHERE" clause, need to keep trying
Level 51
@Ligonsker Can you share some code with us?
1 like
Level 8
@webrobert no problem. I get an array of arrays, like the above example:
[
["a" => "a1", "b" => "b1", "c" => "c1"],
["a" => "a2", "b" => "b2", "c" => "c2", "d" => "d2"]
]
Each of the keys matches a column in the table, and there's also a column called status where it can either be active or inactive.
I then need to dynamically build the following query based on the above input, where each of these arrays has a corresponding SELECT followed by a UNION:
SELECT a
, b
, c
, NULL as d
, count(case when status='active' then 1 end) as number_of_active,
count(case when status='inactive' then 1 end) as number_of_inactive
FROM table
WHERE a='a1'
AND b='b1'
AND c='c1'
GROUP BY
a
, b
, c
UNION ALL
SELECT a
, b
, c
, d
, count(case when status='active' then 1 end) as number_of_active,
count(case when status='inactive' then 1 end) as number_of_inactive
FROM table
WHERE a='a2'
AND b='b2'
AND c='c2'
AND d='d2'
GROUP BY
a
, b
, c
, d
Now for every of these arrays I will need to do the correct SELECT based on what was selected. And as you can see, not always all the 4 keys are chosen. However you can assume that keys are given up to the last key. What I mean is that if b is in the array, then a would also be there, if c in the array then a and b would be there
Please or to participate in this conversation.