Codeigniter Query Builder关联查询问询：多表关联逻辑实现

Hey there! Let's walk through how to use CodeIgniter's Query Builder to handle these many-to-many relationships between your story table and the genre/tag/content warning tables. I'll cover the most common use cases you'll need, from fetching full story details to getting options for your form.

1. Fetch a Single Story with All Associated Genres, Tags, and Content Warnings #

If you need to pull a single story along with all its linked genres, tags, and content warnings, we'll use left joins (to ensure the story is returned even if it has no associated items) and GROUP_CONCAT to bundle multiple related values into a single string for easy display.

public function get_story_with_full_details($story_id) {
    return $this->db
        // Select core story data + aggregated related items
        ->select('story.*,
                  GROUP_CONCAT(DISTINCT genre.name SEPARATOR ", ") AS story_genres,
                  GROUP_CONCAT(DISTINCT tags.name SEPARATOR ", ") AS story_tags,
                  GROUP_CONCAT(DISTINCT content_warning.name SEPARATOR ", ") AS content_warnings')
        ->from('story')
        // Join genres via the pivot table
        ->join('story_genre', 'story_genre.story_id = story.id', 'left')
        ->join('genre', 'genre.id = story_genre.genre_id', 'left')
        // Join tags via the pivot table
        ->join('story_tags', 'story_tags.story_id = story.id', 'left')
        ->join('tags', 'tags.id = story_tags.tag_id', 'left')
        // Join content warnings via the pivot table
        ->join('story_content_warning', 'story_content_warning.story_id = story.id', 'left')
        ->join('content_warning', 'content_warning.id = story_content_warning.warning_id', 'left')
        ->where('story.id', $story_id)
        ->group_by('story.id') // Avoid duplicate story rows from multiple related items
        ->get()
        ->row(); // Return a single object with all data
}

2. Fetch All Stories with Their Associated Details #

This is similar to the above, but returns every story in your database along with its linked items:

public function get_all_stories_with_details() {
    return $this->db
        ->select('story.*,
                  GROUP_CONCAT(DISTINCT genre.name SEPARATOR ", ") AS story_genres,
                  GROUP_CONCAT(DISTINCT tags.name SEPARATOR ", ") AS story_tags,
                  GROUP_CONCAT(DISTINCT content_warning.name SEPARATOR ", ") AS content_warnings')
        ->from('story')
        ->join('story_genre', 'story_genre.story_id = story.id', 'left')
        ->join('genre', 'genre.id = story_genre.genre_id', 'left')
        ->join('story_tags', 'story_tags.story_id = story.id', 'left')
        ->join('tags', 'tags.id = story_tags.tag_id', 'left')
        ->join('story_content_warning', 'story_content_warning.story_id = story.id', 'left')
        ->join('content_warning', 'content_warning.id = story_content_warning.warning_id', 'left')
        ->group_by('story.id')
        ->get()
        ->result(); // Return an array of story objects
}

3. Fetch Only Specific Associated Items (e.g., Genres for a Story) #

If you just need one type of linked item (like a story's genres), you can simplify the query:

public function get_story_genres($story_id) {
    return $this->db
        ->select('genre.id, genre.name')
        ->from('genre')
        ->join('story_genre', 'story_genre.genre_id = genre.id')
        ->where('story_genre.story_id', $story_id)
        ->get()
        ->result();
}

// Repeat this pattern for tags and content warnings:
public function get_story_tags($story_id) {
    return $this->db
        ->select('tags.id, tags.name')
        ->from('tags')
        ->join('story_tags', 'story_tags.tag_id = tags.id')
        ->where('story_tags.story_id', $story_id)
        ->get()
        ->result();
}

4. Fetch All Predefined Options for Form Rendering #

Since you need to populate form dropdowns/checkboxes with the predefined genres, tags, and content warnings, these are simple, straightforward queries:

// Get all genres for form options
public function get_all_genres() {
    return $this->db
        ->select('id, name')
        ->from('genre')
        ->order_by('name', 'ASC')
        ->get()
        ->result();
}

// Get all tags for form options
public function get_all_tags() {
    return $this->db
        ->select('id, name')
        ->from('tags')
        ->order_by('name', 'ASC')
        ->get()
        ->result();
}

// Get all content warnings for form options
public function get_all_content_warnings() {
    return $this->db
        ->select('id, name')
        ->from('content_warning')
        ->order_by('name', 'ASC')
        ->get()
        ->result();
}

Then in your view, you can loop through these results to render form elements (example for a multiple-select genre dropdown):

<select name="genres[]" multiple class="form-control">
    <?php foreach ($genres as $genre): ?>
        <option value="<?= esc($genre->id) ?>"><?= esc($genre->name) ?></option>
    <?php endforeach; ?>
</select>

(Note: Using esc() helps prevent XSS vulnerabilities by sanitizing output.)

Key Notes to Remember #

• Left Joins vs. Inner Joins: Use left joins if you want to return stories even if they have no associated genres/tags/warnings. Use inner joins only if you want to filter out stories with no linked items.
• GROUP_CONCAT Limits: If you have a lot of linked items, MySQL's default GROUP_CONCAT length might truncate results. You can adjust this in your MySQL config or fetch individual items and group them in PHP instead.
• Avoid Duplicates: Always GROUP BY story.id when fetching aggregated data to prevent duplicate story rows from multiple linked items.
• Security: Stick to CodeIgniter's Query Builder methods (instead of raw SQL strings) to protect against SQL injection.

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