I have non-standard sql query and I need bind array params for WHERE IN statement. How can I do this?
This:
$db->query(".... WHERE field IN (:ids)", ['ids' => implode(', ', $array)])->fetchAll();
not works
|
Nov '16 |
7 |
5559 |
0 |
sql look like this (simple version):
WITH dates AS (
SELECT
s::date AS date,
to_char(s::time, 'hh24:mi') AS time,
to_char(s::time, 'hh24') AS hour
FROM (
SELECT * FROM generate_series(
'2016-11-21'::timestamp with time zone,
'2016-11-28'::timestamp with time zone,
'1 hour'
) AS s
) AS dates
WHERE s::time BETWEEN '07:00' AND '23:00'
)
SELECT DISTINCT
t.*,
COUNT(o.id) OVER(PARTITION BY o.date, o.time) AS table2_count,
(
CASE WHEN CONCAT(t.date, ' ', t.time)::timestamp with time zone < NOW()
THEN 0
ELSE 1
END
) AS can_add
FROM dates AS t
LEFT JOIN table2 o ON o.date = t.date
AND (o.time = t.time OR o.time = ltrim(t.time, '0'))
AND (
CASE WHEN CONCAT(t.date, ' ', t.time)::timestamp with time zone < NOW()
THEN o.id_status IN (3, 4, 5, 6, 8)
ELSE o.id_status IN (2, 3, 5)
END
)
ORDER BY t.date, t.time
Then there is no difference than any other sql. Just check mysql or whatever other sql engine you are using.
I would just rewrite this query to PHQL.
It is :) Just don't use binding for array. Keep in mind it's not framework problem.
Also this is solution to still have values binded - https://stackoverflow.com/a/920523/4035199 you have to bind values - not array.
Another solution is to use find_in_set