php - Joins in codeigniter -

i have sql-table products (dranken) , table orders (bestellingen).

now want select products , sum of orders product. works fine code:

$this->db->select('dranken.id,dranken.naam,dranken.minimum_prijs,dranken.gewicht, sum(bestellingen.aantal) totaalverkocht'); $this->db->from('dranken'); $this->db->group_by('dranken.id'); $this->db->join('bestellingen', 'bestellingen.drank_id = dranken.id',"left"); 

but want sum of orders before date.

$this->db->where('bestellingen.date <=',$wheredate); 

this works almost. doesn't give me products doesn't have order. added clause.

$this->db->or_where('bestellingen.date',null); 

but have problem. when there order product has date after clause, disappears results. more or less logical, because doesn't match statement.

but don't want them disappear. if there no order matches this, want null.

i thought left join gives records of table 1?

how can solve this?

this query gets produced:

select      dranken.id, dranken.naam, dranken.minimum_prijs, dranken.gewicht, sum(bestellingen.aantal) totaalverkocht  (dranken)  left join bestellingen on bestellingen.drank_id = dranken.id       bestellingen.date <= '2014-11-19 14:00:00'  or  bestellingen.date null  group dranken.id 

i can't sure work, please let me know if doesn't.

remove clauses , change join statement to:

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

hope helps!