Codeigniter从另一个表中选择SELECT作为数组

Question

What i was hoping i would break some functions down and skipping running multiple queries all the time.

Now if i have a ONE-to-MULTI relationship between two tables. tbl_invoices and tbl_invoice_items. Table tbl_invoice_items has a different row for each item for a specific order.

Would it be possible with a SINGLE query from Codeigniter to get the item from the tbl_invoices and all the tbl_invoice_items in an array in the same result ?

Basically JOIN the below queries

$this->db->select('*,tbl_invoices.id AS invoice_id');
$this->db->from('tbl_invoices');
$this->db->where('tbl_invoices.id',$invoice_id);
$data = $this->db->get();


$this->db->select('*');
$this->db->from('tbl_invoice_items');
$this->db->where('tbl_invoice_items.invoice_id',$invoice_id);
$this->db->join('tbl_products','tbl_products.id = tbl_invoice_items.item_id','left');
$data = $this->db->get();

Also would it be able in a ONE-to-ONE relationship tables return the other table as a custom variable ? Something like: SELECT tbl_clients.* AS client, tbl_invoices.* FROM...JOIN...WHERE...etc

Answer 1

The solution is a 3 table sql join (in answer to your primary question) Here is the SQL query which should answer your primary question

$queryStr = sprintf("SELECT * from tbl_invoices a 
    INNER JOIN tbl_invoice_items b using a.id = b.invoice_id
    INNER JOIN tbl_products c using c.id = b.item_id 
    WHERE a.id = '%s' ", $invoice_id);

$query = $this->db->query($queryStr);