SQL join in CodeIgniter with Active Record

Question

I'm trying to wrap my head around this, but I seem to go in circles. I'm trying to list a users topics one by one, with the quotes belonging to that specific topic underneath. If that makes sense.

I have 3 tables, like so:

[USERS] user_id username

[TOPICS] topic_id user_id topic_name

[QUOTES] quote_id topic_id quote_name

I want to be able to do something like this in my view:

Username: Thomas

Topic 1: Whatever

Quotes: One quote, another quote, and a third quote, all belonging to Topic 1.

Topic 2: Another topic from Thomas

Quotes: Yes indeed, Okay thanks, I love Stack Overflow, These quotes belong to Topic 2.

But I can't get it to work, I've been trying everything, including weird stuff like:

public function get_quotes()
{

    $this->db->select('*');
    $this->db->from('topics');
    $this->db->join('quotes', 'topic_id = quote_id');

    $query = $this->db->get();

    if($query->num_rows() > 0)
    {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
    }
    return $data;
}

Is this strange, should I instead try using 'where' instead? Something like:

$this->db->where('user', $user_id);
$this->db->where('topic', $topic_id);
$this->db->where('quote', $quote_id);

I really appreciate any help I can get, or just a finger pointed in the right direction!

Answer 1

Right off the bat I would ask "What is not working?", secondly I would suggest you run the profiler to show you the EXACT SQL being generated, so that you can make a valid assessment of where the ACTIVE QUERY is failing you.

To use the profiler, stick this into your controller:

$this->output->enable_profiler(TRUE);

It will result in a nice output of all DB calls, all POST vars, etc;
Reference here: http://codeigniter.com/user_guide/libraries/output.html

UPDATE

So to fully do what you want, you need a query that returns the following columns:

user_id, username, topic_id, topic_name, quote_id, quote_name

Here is the active query you want (you can also use method chaining if that is clear enough):

$this->db->select('u.user_id, u.username, t.topic_id, t.topic_name, q.quote_id, q.quote_name');
$this->db->from('users u');
$this->db->join('topics t', 't.user_id = u.user_id'); // this joins the user table to topics
$this->db->join('quotes q', 'q.topic_id = t.topic_id'); // this joins the quote table to the topics table
$query = $this->db->get();

Your result set will then be something like:

user_id     | username  | topic_id  | topic_name    | quote_id  | quote_name
1           |Thomas     |1          |Whatever       |1          |One quote, anot...
2           |Ryan       |4          |Another...     |6          |To be or not to...

Once you have that result set, simply loop through the data to output it, and check to see if you have multiple quotes from the same person (say sort by user_id and do a test on the 2nd loop if its the same person, otherwise output the new users name).