StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POjoin three tables in codeigniter with one to many relation
text
Body
copied!<p>I am doing a project in codeigniter.Here I want to join three tables </p> <p>clients(id, name, email, adminId, campaignId, dateAdded, is_deleted)</p> <p>campaign(id, name, adminId) and</p> <p>order(id, name, cost, dateAdded, clientId).</p> <p>From these tables i want to select(in between two dates) the number of clients added,campaign name and total order cost of a client.When I joined the two tables (clients and campaign) it returns the correct result.</p> <p>The query I used is</p> <pre><code>$this->db->select('clients.id AS my_client, clients.name AS client_name, campaign.name AS campaign_name, DATE(clients.dateAdded) as client_date, COUNT(clients.id) AS num_rows'); $this->db->from('clients'); $this->db->where('clients.adminId', $adminId); $this->db->where('DATE(clients.dateAdded) >=', $from_date); $this->db->where('DATE(clients.dateAdded) <=', $to_date); $this->db->join('campaign', 'campaign.id = clients.campaignId', 'left'); $this->db->group_by('campaign_name'); $query = $this->db->get(); return $query->result(); </code></pre> <p>But when I joined three tables(clients, campaign, order) it is not returning correct result.The relation between client and order is one to many.Ie one client can have more than one order.So it will not give correct value for total number of clients added between two dates.The join query I used to join three tables is </p> <pre><code>$this->db->select('clients.id AS my_client, clients.name AS client_name, campaign.name AS campaign_name, DATE(clients.dateAdded) AS client_date, SUM(order.cost) AS order_cost, COUNT(clients.id) AS num_rows'); $this->db->from('clients'); $this->db->where('clients.adminId', $adminId); $this->db->where('clients.is_deleted', 0); $this->db->where('DATE(clients.dateAdded) >=', $from_date); $this->db->where('DATE(clients.dateAdded) <=', $to_date); $this->db->join('campaign', 'campaign.id = clients.campaignId', 'left'); $this->db->join('order', 'order.clientId = clients.id', 'left'); $this->db->group_by('campaign_name'); $query = $this->db->get(); return $query->result(); </code></pre> <p>Can anyone have some idea to do this.Thanks in advance</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload