Level 73
I think you might be overthinking a little here.
What is it that you want to do that makes you think you need to add a shitloads of union alls to your query?
1 like
Jan 19, 2023
11
Level 8
How to add UNION ALL programmatically in a loop?
I need to construct a query of the following structure:
SELECT
...
FROM table
WHERE
...
GROUP BY
...
UNION ALL
SELECT
...
FROM table
WHERE
...
GROUP BY
...
UNION ALL
SELECT
...
FROM table
WHERE
...
GROUP BY
...
.. and so on..
I get some data in an array, then I iterate it in a foreach loop where I construct each SELECT individually.
But I am not able to find a way to add that UNION ALL after every SELECT (Except for the last one):
$query = DB::table('table');
foreach ($data as $item) {
$query->.. // construct the SELECT
$query->unionAll($query); // does not work
}
How can I do it?
Thanks!
Level 8
@Tray2 I also think so, I just couldn't find a way to do it.
I get an array of arrays from the frontend. Each array represent selection made about the company's hierarchy: (group, unit, department, team)
[
["group" => "g1", "unit" => "u1", "department" => "d1"],
["group" => "g25", "unit" => "u54", "department" => "d70", "team" => "t88"],
["group" => "g3", "unit" => "u6"]
]
I need to fetch the active and inactive data about users from the following example dataset, according to the selections made:
id | username | group | unit | department | team | status
----|------------|---------|--------|--------------|--------|-----------
1 | user1 | g1 | u1 | d1 | t1 | active
2 | user2 | g1 | u1 | d1 | t2 | active
3 | user3 | g1 | u1 | d1 | t3 | inactive
4 | user4 | g3 | u6 | d12 | t30 | active
5 | user5 | g3 | u6 | d13 | t35 | active
6 | user6 | g25 | u54 | d70 | t88 | inactive
Level 102
Show the actual query that does not work instead of half of it with the words "does not work" :)
1 like
Level 8
@Sinnbeck Haha alright.
So the query does work, but I can't reconstruct it with query builder.
I am trying to build the query from this demo programmatically based on the data: https://dbfiddle.uk/YGz9teNv
So let's say again I get some array of arrays in the form of:
[
["group" => "g1", "unit" => "u1", "department" => "d1"],
["group" => "g25", "unit" => "u54", "department" => "d70", "team" => "t88"],
["group" => "g3", "unit" => "u6"]
]
This is how I try to create this raw query using query builder. Each iteration in the outer foreach ($data as $Item) is supposed to create a single SELECT. However, I could not find a way to add the UNION ALL at the end (or somewhere else) of each iteration:
$info = ['group', 'unit', 'department' , 'team'];
$data = [
["group" => "g1", "unit" => "u1", "department" => "d1"],
["group" => "g25", "unit" => "u54", "department" => "d70", "team" => "t88"],
["group" => "g3", "unit" => "u6"]
];
$query = DB::table('table');
foreach ($data as $item) {
foreach ($item as $key => $value) {
if (in_array($key, $info) {
$query->addSelect($key);
}
}
$query->selectRaw("COUNT(CASE...)");
$query->selectRaw("COUNT(CASE...)");
foreach ($item as $key => $value) {
if (in_array($key, $info) {
$query->where($key, "=", $value);
}
}
foreach ($item as $key => $value) {
$query->addSelect($key);
}
foreach ($item as $key => $value) {
$query->groupBy($key);
}
}
Level 102
@Ligonsker It seems you are mutating the same query over and over?
$info = ['group', 'unit', 'department' , 'team'];
$data = [
["group" => "g1", "unit" => "u1", "department" => "d1"],
["group" => "g25", "unit" => "u54", "department" => "d70", "team" => "t88"],
["group" => "g3", "unit" => "u6"]
];
$mainQuery = DB::table('table');
foreach ($data as $item) {
$query = DB::table('table');
foreach ($item as $key => $value) {
if (in_array($key, $info) {
$query->addSelect($key);
}
}
$query->selectRaw("COUNT(CASE...)");
$query->selectRaw("COUNT(CASE...)");
foreach ($item as $key => $value) {
if (in_array($key, $info) {
$query->where($key, "=", $value);
}
}
foreach ($item as $key => $value) {
$query->addSelect($key);
}
foreach ($item as $key => $value) {
$query->groupBy($key);
}
$mainQuery->unionAll($query);
}
$mainQuery()->get();
Untested, but see if it generates something closer to what you need
1 like
Level 8
@Sinnbeck It doesn't do exactly that query and it's not working. I get something like that:
select * from
(select * from table) as temp_table
union all
select * from
(select group, unit, department,
count(case...),
count(case...)
from table
where group='g1'
and unit='u1'
and department='d1'
group by group, unit, department) as temp_table
union all
select * from ...
It also adds temp_table which I never defined but maybe that's done automatically because of the union
Level 75
@Ligonsker Of course you will have a derived table to hold the data. But it's not a table in database.
I suggest this tutorial: https://www.mysqltutorial.org/
And work some examples, it will give you a better understanding of the concepts.
1 like
Level 8
@jlrdw thanks, going to use that! But if you see my demo, I actually have a working query in this case (alright I did not know about the temp table behind the scenes though). I just couldn't find a way to convert it to query builder. Do you think I should then just use PHP's native PDO inside Laravel? do the bindings myself? Or it is somehow possible
Level 75
@Ligonsker laravel has the native instance, use db facade or getPdo().
See https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Bind parameters.
1 like
Level 8
@jlrdw Thank you! I will use that then. I lean towards the DB::select as seen in the best answer there.
Level 8
@jlrdw Update: works great with DB::select($sql, $bindings), thanks! (while iterating the data I push the into the $bindings array so that it will in the correct order)
Please or to participate in this conversation.