Reputation: 3947
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:
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 ?
Upvotes: 0
Views: 970
Reputation: 26180
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);
Upvotes: 1