CodeIgniter 3多表关联查询：保留含空/NULL值行的解决方案

Hey there! I see you're running into an issue where your CodeIgniter 3 joins are dropping rows that have NULL or empty values—this is super common when using the default join type. Let's break down how to fix it and get you that complete user summary table you need.

The Root Problem #

By default, CodeIgniter's join() method uses an INNER JOIN, which only returns rows where there's a matching record in all joined tables. That's why users like Aslam (user_id=2, no favorite book) or Jhon (user_id=3, no avatar) are getting excluded. We need to switch to LEFT JOINs to keep every user from your user table, even if they don't have related records in the other tables.

The Solution: Left Joins for All Related Tables #

We'll make the user table our main table, then left join each related table to it. This ensures we retain every user row, and fill in NULL/empty values where there's no matching data in the joined tables.

Here's the complete CodeIgniter query to generate your desired summary:

// Select the exact fields we need (avoid ambiguous columns like dateCreated)
$this->db->select('
    user.user_id,
    user.name,
    user.contact,
    user_image.img,
    books.book_name,
    user.dateCreated AS user_dateCreated
');
$this->db->from('user');

// Left join user_image: keep users even if they have no avatar
$this->db->join('user_image', 'user.user_id = user_image.user_id', 'left');

// Left join favorite_book: keep users even if they have no favorite books
$this->db->join('favorite_book', 'user.user_id = favorite_book.user_id', 'left');

// Left join books: keep users even if their favorite book entry has no matching book (or no favorite book at all)
$this->db->join('books', 'favorite_book.book_id = books.book_id', 'left');

// Optional: Order results to match your expected output
$this->db->order_by('user.user_id', 'ASC');

// Execute the query and return results
$query = $this->db->get();
return $query->result_array(); // Use result() for object-oriented results

Key Details to Note #

1. Explicit Field Selection: We specify user.dateCreated (with an alias for clarity) because multiple tables have a dateCreated column—this ensures we pull the correct value from the user table, as required.
2. Left Join Logic: Each left join tells the database: "Keep all rows from the left table (user), and match with rows from the right table where possible. If no match exists, fill those columns with NULL."
3. Handling NULLs: Your user_image table has NULL values for some users, and favorite_book has no entries for user_id=2—left joins will preserve these rows instead of filtering them out.

Expected Result #

When you run this query, you'll get exactly the summary table you're looking for, with all 5 users included, even those with missing avatars or favorite books.

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