2

Related question

I have a very similar query to this question below:

Search GROUP_CONCAT using LIKE

I think my question is pretty much identical, but in CodeIgniter. Therefore I think I am just looking for the same answer but converted into active record language...

My question

To provide some background, in my case I have a many to many relationship so with three tables:

  • companies which has two fields (company_id and company_name)
  • sectors which has two fields (sector_id and text)
  • companies_sectors which has two fields (company_id and sector_id)

(One company can operate in multiple sectors, multiple companies operate in the same sector.)

I have grouped by company to show sectors.sector_name as a group_concat field and I have given an alias to this concatenated field at the select level:

$this->db->select('sectors.sector_id, GROUP_CONCAT(DISTINCT sectors.text SEPARATOR "; ") as sector_text', false);

I want to include a filter which selects where 'sector_text' (the group_concat field) includes the text from the query form. I understand that, because I want to run the filter on an aggregated list, I should use "having" and not "where". Per the answer to the link above, it looks like MySQL has a HAVING LIKE, but I was struggling to replicate this under CodeIgniter's active record (in fact my understanding is that CodeIgniter's ->like() is a WHERE x LIKE y which is not what I am looking for...)

At the moment I am only using a LIKE:

$this->db->like('sectors.text', $this->input->post('sector_text') );

But this filters before the grouping, which means the output will only show the sector that was searched for. For example, if Company A operates in "fishing" and "shipping" while Company B operates only in "fishing", and a user searched for "fishing", I want the result to show:

  • Company A - Fishing; Shipping
  • Company B - Fishing

(This is the desired result!)

But at the moment I am only getting:

  • Company A - Fishing
  • Company B - Fishing

... which I think is because I have used like, which filers pre-grouping?

Can someone please assist? Many thanks in advance!

PS If I can also use the alias "sector_text" instead of sector.text that would be ideal (I think I have read that "Having" allows you to use the alias?)

Community
  • 1
  • 1
Umito
  • 71
  • 1
  • 7

1 Answers1

5

I found the answer! I just modified the "LIKE" to:

$sector_text = $this->input->post('sector_text');
$this->db->having("sector_text LIKE '%$sector_text%' ");

It feels a little off doing it via active record, but it works. If there is a solution that keeps it within active record then please let me know as I would probably prefer this!

Many thanks!

Umito
  • 71
  • 1
  • 7