如何基于两个关联键两次关联数据表并将结果存入数组

Got it, let's work through this properly. When joining the same table multiple times (like your ip_clients joins here), you need table aliases to avoid column name conflicts—since both joins would pull in identical column names from ip_clients otherwise. Here's how to implement this and get your data into an array:

Step 1: Build the Query with Clear Aliases #

Use distinct aliases for each ip_clients join (I’ll use c1 for the invoice’s primary client and c2 for the recipient) to keep columns separate. Be explicit about which columns you want to select to avoid ambiguity:

// Start with the main invoices table
$this->db->select('
    ip_invoices.*,
    c1.client_id AS client_id_primary,
    c1.client_name AS client_name_primary,
    c2.client_id AS recipient_id_full,
    c2.client_name AS recipient_name_full
');
$this->db->from('ip_invoices');

// First join: link invoices to their main client
$this->db->join('ip_clients AS c1', 'c1.client_id = ip_invoices.client_id', 'left');

// Second join: link invoices to their recipient client
$this->db->join('ip_clients AS c2', 'c2.client_id = ip_invoices.recipient_id', 'left');

// Optional: add filters if needed (e.g., specific invoice status)
// $this->db->where('ip_invoices.invoice_status', 'paid');

Step 2: Fetch Results into an Array #

Once your query is built, use CodeIgniter’s result methods to pull the data into an array. You have two common options:

Option 1: Associative Array of Objects #

If you prefer working with objects (easy to read with dot notation):

$invoiceData = $this->db->get()->result();

You can access fields like $invoice->client_name_primary or $invoice->recipient_name_full for each entry.

Option 2: Pure Associative Array #

If you want a plain array (great for direct array manipulation):

$invoiceData = $this->db->get()->result_array();

Now you can access fields like $invoiceData[0]['client_name_primary'].

Quick Tips #

• Avoid using select('*')—explicitly listing columns prevents ambiguous column errors and keeps your query efficient.
• Use left joins if you want to include invoices even if there’s no matching client/recipient. Swap to inner if you only want invoices with valid client/recipient entries.

内容的提问来源于stack exchange，提问作者Sławomir Dworaczek