phpcodeigniterselectactiverecorddistinct

How to include the DISTINCT keyword in a CodeIgniter active record SELECT query?

I have a problem converting an SQL statement into a CodeIgniter query builder record. In workbench, I've run the following query with no problems.

This works in MySQL WorkBench...

# This 
SELECT DISTINCT
    projects.id, projects.title
FROM
    projects
INNER JOIN
    positions
ON
    (projects.id = positions.project_id)
WHERE (positions.is_draft = '0')

Query builder

$this->db->select('DISTINCT projects.id, projects.title');
$this->db->from('projects');
$this->db->join('positions', 'projects.id = positions.project_id', 'inner');
$this->db->where('positions.is_draft', '0');
$query = $this->db->get();

return $query->result();

Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`id`, `projects`.`title` FROM `projects` INNER JOIN `positions` ON projects\.`' at line 1

SELECT `DISTINCT` `projects`.`id`, `projects`.`title` FROM `projects` INNER JOIN `positions` ON `projects`.`id` = `positions`.`project_id` WHERE `positions`.`is_draft` =0

I've also tried

$this->db->where('positions.is_draft = 0');

I think the problem is the equals assignment, but what do you think? I've played around with it, but keep getting the same error.

Solution

  • May be it related to distinct(). There are several way to use distinct in Codigniter.

    $this->db->distinct();

    or in your example try this

    $this->db->select('DISTINCT(projects.id), projects.title');