Making query with multiple join's

Question

I'm now learning php and codeigniter and now I want to combine query to be fast and effective. I have made all, but haven't joined the last table...

I have 4 tables: posts, users, post_categories, categories;

And what I want to get:

1. All posts
2. User info using usr_id
3. Get all categories id from post_categories using cat_ids
4. And get each name of category using id_*

This is what I end up... it's not complete because I've got stuck with getting categories name for each of id_*

$data = $this->db->select('p.*, u.nickname, u.usr_status, u.usr_rating, pc.*')
                    ->from('posts p')
                    ->join('users u', 'p.usr_id = u.id', 'left')
                    ->join('post_categories pc', 'p.cat_ids = pc.id', 'left')
                    ->limit($limit, $start)
                    ->order_by('p.id', 'desc')
                    ->where('p.active', 1)
                    ->get()
                    ->result_array();

Anyone could help me to end up this query in codeigniter?

edit: in post_categories: id_1 always will be... but id_2 and id_3 could stay as NULL(default value)

Answer 1

Something like the following SQL query should work for you...

SELECT 
    posts.*,
    users.nickname, users.user_status, users.usr_rating,
    c1.category as category_1,
    c2.category as category_2,
    c3.category as category_3
FROM posts
INNER JOIN users ON user.id = posts.user_id
INNER JOIN post_dategories ON post_categories.id = posts.cat_ids
INNER JOIN categories c1 ON post_categories.id_1 = c1.id
LEFT JOIN categories c2 ON post_categories.id_2 = c2.id
LEFT JOIN categories c3 ON post_categories.id_3 = c3.id
WHERE posts.active = 1

NOTE: LEFT JOIN on c2 and c3 because you said they were optional