CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
Krishna Kumar Yadav
Krishna Kumar Yadav

Reputation: 323

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

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.

Upvotes: 2

Views: 2364

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

Join keywords_tags_company onto keywords_tags by their shared keyword_tag_id column to avoid making two trips to the database. MySQL has USING to make the table relationship declaration more concise.

The get_where() method will consolidate the from(), where(), and get() methods calls into one.

To return a flat array of zero or more tag names for a specified company, call array_column() on the array of zero or more objects that is returned from result_array().

public function getCompanyTagNames(int $companyId): array
{
    return array_column(
        $this->db
             ->select('t.keyword_tag_name')
             ->join('keywords_tags_company c', 'keyword_tag_id')
             ->get_where('keywords_tags t', ['c.company_id' => $companyId])
             ->result(),
        'keyword_tag_name'
    );
}

In this context, ->select('t.keyword_tag_name') can be omitted because of the later call of array_column().

Rendered SQL (if $companyId = 7):

SELECT `t`.`keyword_tag_name`
FROM `keywords_tags` `t`
JOIN `keywords_tags_company` `c` USING (`keyword_tag_id`)
WHERE `c`.`company_id` = 7

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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();
}

Upvotes: 2

Related Questions