Codeigniter 4 get count of rows on a table depeend on another table

26 Views Asked by At

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"}
}
0

There are 0 best solutions below