使用Having（count（））和CI活动记录

Question

I have the following SQL query but I can't seem to find out how I would change this query to make it work with CI:

SELECT *
FROM Table1
JOIN Table2 ON Table2.tID = Table1.tID
JOIN Table3 ON Table3.vID = Table2.vID
WHERE vegetable
IN (
'Potatoe', 'Carrot'
)
GROUP BY Table1.tID
HAVING COUNT( DISTINCT vegetable ) =2

Here is what I have so far on CI:

$arrCount = count($array);
$this->db->select('*');
this->db->from('Table1');
$this->db->join('Table2', 'Table2.tID = Table1.tID'); 
$this->db->join('Table3', 'Table3.vID = Table2.vID');
$this->db->where_in('vegetable', $array);
$this->db->group_by("Table1.tID");
$this->db->having('vegetable');
$this->db->count_all($arrCount);
$q = $this->db->get();

But doing this gives me the following sql statement:

Table 'DatabaseTest.2' doesn't exist

SELECT COUNT(*) AS `numrows` FROM `2`

I am pretty sure the ActiveRecord count I have used is incorrect but I am not sure what else it would be because I have found any COUNT() function with CI.

So my question is how can I make my SQL query in CodeIgniter?

Extra note, I have also tried running the query using CIs $this->db->query but get an array error, when looking with the in.

$this->db->query('SELECT *
FROM Table1
JOIN Table2 ON Table2.tID = Table1.tID
JOIN Table3 ON Table3.vID = Table2.vID
WHERE vegetables
IN (
' .$array . '
)
GROUP BY Table1.tID
HAVING COUNT( DISTINCT vegetables ) =' . $arrCount);

And heres the error message:

Unknown column 'Array' in 'where clause'

So it is assuming that I am using a where instead of a where in it looks like. Again can someone help me get my sql to work with CIs active record?

Answer 1

I haven't tested the first query with active records, but wouldn't this work? count_all is a query of on it's own. The first parameter is suppose to be the table... you entered the size of your array as the table which is 2!!! active records reference

$arrCount = sizeof($array);
$this->db->select('*');
this->db->from('Table1');
$this->db->join('Table2', 'Table2.tID = Table1.tID'); 
$this->db->join('Table3', 'Table3.vID = Table2.vID');
$this->db->where_in('vegetable', $array);
$this->db->group_by("Table1.tID");
$this->db->having('COUNT(DISTINCT vegetable)', $arrCount);
$q = $this->db->get();

Also, for the query, you need to convert your array into a string:

$this->db->query('SELECT *
FROM Table1
JOIN Table2 ON Table2.tID = Table1.tID
JOIN Table3 ON Table3.vID = Table2.vID
WHERE vegetables
IN (
' .implode(',',$array) . '
)
GROUP BY Table1.tID
HAVING COUNT( DISTINCT vegetables ) =' . $arrCount);