CODEWITHSUNDEEP
phpmysqlcodeigniterselectinner-join
JETHRO KANE
JETHRO KANE

Reputation: 81

Codeigniter join statement with a join statement

I'm having an issue with converting the below statement into code on my model in the codeigniter framework.

SELECT mt_prs_groups.groupId, mt_prs_groups.groupName, mt_prs_administrators.administratorId
FROM mt_prs_status INNER JOIN (mt_prs_administrators INNER JOIN mt_prs_groups ON mt_prs_administrators.administratorId = mt_prs_groups.administratorId) ON (mt_prs_status.statusId = mt_prs_groups.statusId) AND (mt_prs_status.statusId = mt_prs_administrators.statusId)
WHERE (((mt_prs_status.status)="Active") AND ((mt_prs_administrators.administratorId)=3)) OR (((mt_prs_administrators.administratorId)=4));

On the model I have translated this to

$this->db->select('mt_prs_groups.groupId, mt_prs_groups.groupName');
$this->db->from('mt_prs_status');
$this->db->join('mt_prs_administrators', 'inner');
$this->db->join('mt_prs_groups', 'mt_prs_administrators.administratorId = mt_prs_groups.administratorId AND mt_prs_status.statusId = mt_prs_administrators.statusId', 'inner');
$this->db->where('mt_prs_status.status="Active"');
$this->db->where('mt_prs_administrators.administratorId=3');
$this->db->or_where('mt_prs_administrators.administratorId=4');

This is giving me the error below

Error Number: 1054

Unknown column 'inner' in 'from clause'

SELECT mt_prs_groups.groupId, mt_prs_groups.groupName FROM mt_prs_status JOIN mt_prs_administrators USING (inner) INNER JOIN mt_prs_groups ON mt_prs_administrators.administratorId = mt_prs_groups.administratorId AND mt_prs_status.statusId = mt_prs_administrators.statusId WHERE mt_prs_status.status = "Active" AND mt_prs_administrators.administratorId = 3 OR mt_prs_administrators.administratorId = 4

The query runs well when I put it directly on mysql but fails in codeigniter. The issue is the inner join is dependent on another inner join

Upvotes: 0

Views: 214

Answers (3)

Hansraj Systematix
Hansraj Systematix

Reputation: 104

Please try this .I tried this into codeigniter

$this->db->select('mt_prs_groups.groupId, mt_prs_groups.groupName');
$this->db->from('mt_prs_status');
$this->db->join('mt_prs_administrators', 'mt_prs_status.statusId = mt_prs_administrators.statusId','inner');
$this->db->join('mt_prs_groups', 'mt_prs_administrators.administratorId = mt_prs_groups.administratorId', 'inner');
$this->db->where('mt_prs_status.status="Active"');
$this->db->where('mt_prs_administrators.administratorId=3');
$this->db->or_where('mt_prs_administrators.administratorId=4');

Upvotes: 1

Hansraj Systematix
Hansraj Systematix

Reputation: 104

In this line - "$this->db->join('mt_prs_administrators', 'inner');" you are not defining joining condition, that's why you are getting an error because the format of the join in CI is - $this->db->join('table_name', 'column name on which we apply the join', 'type of join');

Upvotes: 0

Hansraj Systematix
Hansraj Systematix

Reputation: 104

Please try this format

 $this->db->select('tbl_tasks.task_id,tbl_tasks.task, tbl_assign_task.user_id');
        $this->db->from(TBL_TASKS);
        $this->db->join(TBL_ASSIGN_TASK, 'tbl_tasks.task_id = tbl_assign_task.task_id');
        $this->db->where('tbl_assign_task.is_deleted', 0);
         $this->db->where('tbl_assign_task.user_id', $userId);
        $query = $this->db->get();

Upvotes: 0

Related Questions