CodeIgniter ORM嵌套关联查询功能等效实现求助

Hey there, I totally get the frustration—CodeIgniter’s query builder doesn’t have that built-in nested relational result feature like Sequelize’s include out of the box. But don’t worry, we can easily replicate that behavior with a bit of PHP post-processing or a structured query approach. Let me walk you through a couple of solid solutions:

Option 1: Manual N+1 Query (Simple, for Small Datasets) #

This approach is straightforward and easy to read, though it runs one initial query for users plus one query per user for payments (so "N+1" queries total). Great for small datasets where performance isn’t critical:

// First, fetch all users from the database
$users = $this->db->get('users')->result_array();

// Loop through each user to attach their payments
foreach ($users as &$user) {
    $user['payments'] = $this->db
        ->where('user_id', $user['user_id'])
        ->get('payments')
        ->result_array();
}

// Now $users contains your nested structure!
// Example output:
// [
//   ["user_id" => 1, "payments" => [["payment_id" => 1], ["payment_id" => 2]]],
//   ["user_id" => 2, "payments" => [["payment_id" => 3], ["payment_id" => 4]]]
// ]

Option 2: Single Query + PHP Reorganization (Better for Large Datasets) #

For larger datasets, a single JOIN query followed by PHP restructuring is more efficient (only one round trip to the database). Here’s how to do it:

// Run a JOIN query to get flat, combined results
$flat_results = $this->db
    ->select('u.user_id, p.payment_id')
    ->from('users u')
    ->join('payments p', 'u.user_id = p.user_id', 'left') // Use left join to include users with no payments
    ->get()
    ->result_array();

// Restructure the flat results into nested arrays
$nested_users = [];
foreach ($flat_results as $row) {
    $user_id = $row['user_id'];
    
    // Initialize the user entry if it doesn't exist yet
    if (!isset($nested_users[$user_id])) {
        $nested_users[$user_id] = [
            'user_id' => $user_id,
            'payments' => []
        ];
    }
    
    // Add the payment only if it exists (avoids empty entries from left joins)
    if (!empty($row['payment_id'])) {
        $nested_users[$user_id]['payments'][] = [
            'payment_id' => $row['payment_id']
        ];
    }
}

// Convert the associative array to an indexed array if needed
$nested_users = array_values($nested_users);

Bonus: Reusable Model Method #

If you need this functionality often, wrap it in a custom model method to keep your code clean (just like Sequelize’s model methods):

class UserModel extends CI_Model {
    public function findAllWithPayments() {
        $flat_results = $this->db
            ->select('u.user_id, p.payment_id')
            ->from('users u')
            ->join('payments p', 'u.user_id = p.user_id', 'left')
            ->get()
            ->result_array();

        $nested_users = [];
        foreach ($flat_results as $row) {
            $user_id = $row['user_id'];
            if (!isset($nested_users[$user_id])) {
                $nested_users[$user_id] = [
                    'user_id' => $user_id,
                    'payments' => []
                ];
            }
            if (!empty($row['payment_id'])) {
                $nested_users[$user_id]['payments'][] = ['payment_id' => $row['payment_id']];
            }
        }

        return array_values($nested_users);
    }
}

Then use it like this:

$this->load->model('UserModel');
$users_with_payments = $this->UserModel->findAllWithPayments();

内容的提问来源于stack exchange，提问作者Vahid Najafi