i try to get a list with the amount of rows from another table in CodeIgniter
table orders:
| OrderID | State |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
table OrderIDStates
| OderStateID | OrderStateText |
|---|---|
| 0 | Closed |
| 1 | Open |
| 2 | Pending |
| 3 | Wait for Parcel |
| 4 | Delivered |
| 5 | recall |
Actual with this try
return $this->db->table('OrderIDStates')
->where('OrderStateID >', '0') // items with State 0 (Closed) not listen
->join('orders', 'orders.State = OrderIDStates.OrderStateID', 'left')
->select('OrderStateID, OrderStateText')
->selectCount('State', 'amount')
->groupBy('State')
->orderBy('OrderIDStates.OrderStateID', 'asc')
->get()
->getResult();
i got this:
{"OrderStates":[
{"OderStateID":"1","OrderStateText":"Open","amount":"1"},
{"OderStateID":"2","OrderStateText":"Pending","amount":"1"}
}
but i need a result like this:
{"OrderStates":[
{"OderStateID":"1","OrderStateText":"Open","amount":"1"},
{"OderStateID":"2","OrderStateText":"Pending","amount":"1"},
{"OderStateID":"3","OrderStateText":"Wait for Parcel","amount":"0"},
{"OderStateID":"4","OrderStateText":"Delivered","amount":"0"},
{"OderStateID":"5","OrderStateText":"recall","amount":"0"}
}