I have a query like this:
SELECT
foo.bar
FROM
foo
WHERE
foo.bang = 0
AND (
CASE
WHEN ? = 2 THEN foo.baz IS NOT NULL
WHEN ? = 1 THEN foo.baz IS NULL
ELSE ? NOT IN (1, 2)
END
)
AND (
(? = 0)
OR
(foo.bang = ?)
)
where the placeholders (?) are used as inputs for filter parameters:
my $query = $aboveQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
$query,
{ Slice => {} },
$bazFilter,
$bazFilter,
$bazFilter,
$bangFilter,
$bangFilter,
);
This works, but it's not very reader-friendly.
MySQL supports @ variables, which would increase readability:
SET @bazFilter = ?; -- passed in via selectall_arrayref or execute;
SET @bangFilter = ?;
SELECT
foo.bar
FROM
foo
WHERE
foo.bang = 0
AND (
CASE
WHEN @bazFilter = 2 THEN foo.baz IS NOT NULL
WHEN @bazFilter = 1 THEN foo.baz IS NULL
ELSE @bazFilter NOT IN (1, 2)
END
)
AND (
(@bangFilter = 0)
OR
(foo.bang = @bangFilter)
);
I'd like to do something like this instead:
my $query = $aboveAtVariablizedQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
$query,
{ Slice => {} },
$bazFilter,
$bangFilter,
);
but MyISAM apparently won't do multiple statements in a single query.
My google-fu is failing me.
Is there a good way to mix-and-match the @variables with the placeholders?
Do each statement separately, using execute not one of the fetch methods.
@ variables are local to the connection, so there is no problem with contamination between threads.
If the goal is to have a single query with no repeated substitutions, then consider:
SELECT foo.bar
FROM ( SELECT baz = ?, bang = ? ) AS init
JOIN foo
WHERE foo.bang = 0
AND (
CASE
WHEN init.baz = 2 THEN foo.baz IS NOT NULL
WHEN init.baz = 1 THEN foo.baz IS NULL
ELSE init.baz NOT IN (1, 2)
END
)
AND (
(init.bang = 0)
OR
(foo.bang = init.bang)
);