Get records from a database table which relate to qualifying rows in another table using CodeIgniter's active record

Question

I have two table keywords_tags and keywords_tags_company

Table: keywords_tags

keyword_tag_id   keyword_tag_name
--------------   ----------------
1                Clothing
2                Footwear
3                Fashion

Table: keywords_tags_company

keyword_tag_company_id    keyword_tag_id   company_id
----------------------    --------------   ----------
1                          1               7
2                          2               7
3                          3               7

I want to select all keyword_tag_name which company_id is 7.

this is my query

public function getAllTag($id) {
    $this->db->where('company_id', $id);
    $this->db->select('keyword_tag_id');
    $tagId = $this->db->get('keywords_tags_company')->result();
    
    $this->db->or_where('keyword_tag_id', $tagId);
    $this->db->select('keyword_tag_name');
    $tagName = $this->db->get('keywords_tags')->result_array();
    return $tagName;
}

if I run the above query output is:

Unknown column 'Array' in 'where clause' SELECT keyword_tag_name FROM (keywords_tags) WHERE keyword_tag_id= Array

What I am wrong in this query.

Answer 1

You are passing array in or_where('keyword_tag_id', $tagId) because parent query returns multiple rows i suggest you to use a single query with join

public function getAllTag($id) {
return $this->db->select('t.keyword_tag_name')
       ->from('keywords_tags t')
       ->join('keywords_tags_company c','t.keyword_tag_id = c.keyword_tag_id')
       ->where('c.company_id', $id)
       ->get()
       ->result_array();
}