MySQL Join two different columns in the same table twice in CodeIgniter

Question

I have announcements table, where I store announcements.catid (category id).

Categories table categories are used to be nested with the use of categories.pid.

What I am trying to do, is to get the categories nested one by one inside the multidimensional array.

Here is the image of how I want it to be inside highlighted part:

enter image description here

and here is the code:

public function get_mixed( $limit = NULL, $offset = NULL )
{
    $this->db
        ->select('
            announcements.id,
            announcements.catid,
            announcements.uid,
            categories.title AS section,
            categories.title AS category,
            announcements.title,
            announcements.text,
            announcements.views,
            announcements.created,
            announcements.modified,
            announcements.pubdate
        ')
        ->join('categories', 'announcements.catid = categories.id', 'left')
        ->join('categories as section', 'categories.pid = section.id', 'left')
        ->order_by('created DESC, '.$this->_order_by);

    return $this->db->get( $this->_table_name, $limit, $offset )->result();
}

I tried first with by creating a section alias, but it gets me the same results.

Maybe there is a better approach ?

random_user_name · Accepted Answer · 2014-07-02 15:30:30Z

1

If you are aliasing one of the categories joins as section, then you would need to reference any fields you want from that categories table as section.[field] (for example, section.title)

Not knowing exactly what you want, my best guess at your revised query would be:

$this->db
    ->select('
        announcements.id,
        announcements.catid,
        announcements.uid,
        section.title AS section,
        categories.title AS category,
        announcements.title,
        announcements.text,
        announcements.views,
        announcements.created,
        announcements.modified,
        announcements.pubdate
    ')
    ->join('categories', 'announcements.catid = categories.id', 'left')
    ->join('categories as section', 'categories.pid = section.id', 'left')
    ->order_by('created DESC, '.$this->_order_by);

answered Jul 2, 2014 at 15:30

random_user_name

26.3k7 gold badges81 silver badges119 bronze badges

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

1 Comment

aspirinemaga Over a year ago

exactly what I wanted, I forgot about referencing the aliased name in SELECT. thank you

Collectives™ on Stack Overflow

MySQL Join two different columns in the same table twice in CodeIgniter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related