phpmysqlcodeigniterjoinaggregate-functions

LEFT JOIN, GROUP BY, SUM() query WHERE datetime column IS NULL or <= a specified value with CodeIgniter

I have a sql-table with products (dranken) and a table with orders (bestellingen).

Now I want to select all the products and get the sum of all the orders for that product.

This mostly works well:

$this->db->SELECT('dranken.id,dranken.naam,dranken.minimum_prijs,dranken.gewicht, SUM(bestellingen.aantal) AS totaalVerkocht');
$this->db->FROM('dranken');
$this->db->group_by('dranken.id');
$this->db->JOIN('bestellingen', 'bestellingen.drank_id = dranken.id', "left");

but I want to get only the sum of the orders before a certain date.

$this->db->WHERE('bestellingen.date <=', $whereDate);

This works almost. It doesn't give me the products that doesn't have an order. So I added this WHERE clause.

$this->db->OR_WHERE('bestellingen.date', NULL);

But now I have a problem. When there is an order for a product that has a date after the where clause, it disappears from the results. More or less logical, because it doesn't match the WHERE statement.

But I don't want them to disappear. If there is no order that matches this, I want NULL.

I thought a LEFT JOIN gives always all the records of TABLE 1?

How can I solve this?

This is the query that gets produced:

SELECT 
    dranken.id, dranken.naam, dranken.minimum_prijs, dranken.gewicht, SUM(bestellingen.aantal) AS totaalVerkocht 
FROM (dranken) 
LEFT JOIN bestellingen ON bestellingen.drank_id = dranken.id 
WHERE 
    bestellingen.date <= '2014-11-19 14:00:00' 
OR  bestellingen.date IS NULL 
GROUP BY dranken.id
Solution

  • I can't be sure this will work, so obviously please let me know if it doesn't.

    Remove the WHERE clauses and change the join statement to:

    $this->db->JOIN('bestellingen', "bestellingen.drank_id = dranken.id AND bestellingen.date <= '$whereDate'","left");

    Hope this helps!