Codeigniter 2 join one-to-many query 3 tables

Question

I have 3 tables

table1: documents (id, code, content)

table2: files (id, code, filename)

table3: tags (documentId, tag)

On my page, I have to display all the documents in a table view. like

code | content

123 | This is a sample document with file **files.filename** and tags **tags.tag**

Each document can have many files and tags

This is my current code.

return $this->db
    ->select('t1.id, t1.barcode, t1.sub, t1.source_type, t1.sender, t1.address, t1.description, t1.receipient, t1.status, t2.id as fileId, t3.tag as docTag')
    ->select('DATE_FORMAT(t1.datetime_added, \'%m/%d/%Y-%h:%i %p\') as datetime_added', FALSE)
    ->from('documents as t1')
    ->join('files as t2', 't2.barcode = t1.barcode', 'left')
    ->join('tags as t3', 't3.id = t1.id')
    ->order_by('id', 'desc')
    ->get()->result_array();

Answer 1

You may use this query it will help you

SELECT `doc`.`id`,
       (select GROUP_CONCAT(`tag`) from `tags` `tag` where `docid`=`doc`.`id` group by `docid`) as `tags`, 
      (select GROUP_CONCAT(`filename`)  from `files` where `docid`=`doc`.`id` group by `docid`) as `file`
FROM `document` `doc` 

if you have more files or tags for document it will show one record from document table,tags and file name will be comma separated if multi