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;

enter image description here

And what I want to get:

All posts
User info using usr_id
Get all categories id from post_categories using cat_ids
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)

Steven · Accepted Answer · 2013-09-22 15:20:01Z

2

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

answered Sep 22, 2013 at 15:20

Steven

6,1582 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Making query with multiple join's

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related