Mysql - 将数组元素与字段值进行比较

Question

I have a table with structure

id | item | order_date | delivery_date

And I have an array of dates, for example

$dates = array('2015-06-01', '2015-06-11', '2015-06-26');

What I need to do is to find count of items which where ordered, but not delivered yet on each date. Because of my code complexity, it is impossible to foreach array and search for each date separately.

There is an example of what I think the query should look like, but I am starting wondering if it is even possible to make it work this way (without foreaching each date).

SELECT SUM(id) FROM orders WHERE order_date > IN({implode(",", $dates)}) AND delivery_date < IN ({implode(",", $dates)}) GROUP BY ???

Notice: I would like to GROUP BY each date given in an array.

Answer 1

Use a subquery that returns all the dates in the array. Then you can join with that, and use its column in the GROUP BY.

$subquery = implode(' UNION ', array_map(function($d) {
    return "SELECT '$d' AS date";
}, $d));
$query = "SELECT d.date, SUM(id)
          FROM orders
          JOIN ($subquery) AS d ON d.date BETWEEN order_date AND delivery_date
          GROUP BY d.date";

Note that if an order matches multiple dates, it will be counted for each of them.