codeigniter query prbolem

Question

This code is working perfectaly in mysql run command

SELECT employeeCode
FROM employee_details
WHERE employeeCode
IN (
    SELECT DISTINCT (employeeCode) FROM quiz_answer_detailsWHERE submitTime
    IN (SELECT MIN( submitTime ) FROM quiz_answer_details WHERE quizId
    IN (SELECT id FROM quiz_details WHERE uploadtime =  '2014-04-03')
    AND answer IN (SELECT answer FROM quiz_details WHERE uploadtime =  '2014-04-03'))
   )

But I want to use this code on my codeigniter, but it is not working.

My codeigniter query code is

    $this->db->select('employeeCode');
    $this->db->from('employee_details');
    $this->db->where_in('employeeCode');
    $this->db->select('DISTINCT(employeeCode)');
    $this->db->from('quiz_answer_details');
    $this->db->where_in('submitTime');
    $this->db->select('min(submitTime)');
    $this->db->from('quiz_answer_details');
    $this->db->where_in('quizId');
    $this->db->select('id');
    $this->db->from('quiz_details');
    $this->db->where('uploadtime',"2014-04-03");
    $this->db->where_in('answer');
    $this->db->select('answer');
    $this->db->from('quiz_details');
    $this->db->where('uploadtime',"2014-04-03");
    $query=$this->db->get();
    print_r($query);
    if($query->num_rows>=1)
    {
        return $query;
    }
    else
    {
         return false;
    }

What is wrong please help me

I've seen a lot, but Active Records used like this... never. — Shomz, Apr 03 '14 at 13:42
If CodeIgniter 3 - use `get_compiled_select()`, if CodeIgniter 2 - see [Is there a function like _compile_select or get_compiled_select()?](http://stackoverflow.com/q/9232316/1356425) and related questions about generating queries with subqueries. — Apostle, Apr 03 '14 at 14:02

score 2 · Answer 1 · answered Apr 03 '14 at 13:40

The problem lies with this code and subsequent similar uses of where_in

$this->db->where_in('employeeCode');

You have given the where parameter value but not what to match with.
for eg.

$this->db->where_in('employeeCode',$subQuery1);

The documentation of where_in:

$this->db->where_in();

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

$names = array('Frank', 'Todd', 'James'); $this->db->where_in('username', $names); // Produces: WHERE username IN ('Frank', 'Todd', 'James')

You have to create a separate sub query for each invocation of where_in.

score 1 · Accepted Answer · answered Apr 03 '14 at 13:34

You should re write you subquery and use joins instead to get the better performance,without having full information regarding your tables/relationship and desired result i can't provide you the new query but you can use your subquery in active record's where function

$subquery=" SELECT DISTINCT (employeeCode) FROM quiz_answer_detailsWHERE submitTime
    IN (SELECT MIN( submitTime ) FROM quiz_answer_details WHERE quizId
    IN (SELECT id FROM quiz_details WHERE uploadtime =  '2014-04-03')
    AND answer IN (SELECT answer FROM quiz_details WHERE uploadtime =  '2014-04-03')) ";
$this->db->select('employeeCode');
$this->db->from('employee_details');
$this->db->where('employeeCode IN('.$subquery.')',null,FALSE);
$query=$this->db->get();

You should pass third parameter as FASLE in order to prevent the query to be quoted by bacticks Or you can use query() fucntion to run your raw queries

$query=$this->db->query(' your full query here');
$query->result();

codeigniter query prbolem

2 Answers2