CodeIgniter query builder coding attempt with join() renders an unintended query

Question

I have written the code below which is a I think is a simple select with a join.

$this->db->select('
    PAS_User.user_first_name,
    PAS_User.user_last_name,
    PAS_User.user_avatar_url,
    AMS_Notification.noti_entitiy_id,
    AMS_Notification.noti_entity_type,
    AMS_Notification.noti_updated
    FROM AMS_Notification
    INNER JOIN PAS_User ON AMS_Notification.noti_from_user_id = PAS_User.user_user_id
');
$this->db->from('AMS_Notification');
$this->db->join('PAS_User', 'PAS_User ON AMS_Notification.noti_from_user_id = PAS_User.user_user_id');
$this->db->where('AMS_Notification.noti_to_user_id', $userid);
$this->db->order_by("noti_updated", "desc");
$query = $this->db->get('AMS_Notification');

if ($query -> num_rows() == 1) {
    return $query->result();
} else {
    return false;
}

However when I call the page, it shows this query with two FROM statements. Can anyone tell me what I am doing wrong here?

SELECT
    `PAS_User`.`user_first_name`,
    `PAS_User`.`user_last_name`,
    `PAS_User`.`user_avatar_url`,
    `AMS_Notification`.`noti_entitiy_id`,
    `AMS_Notification`.`noti_entity_type`,
    `AMS_Notification`.`noti_updated`
FROM AMS_Notification
INNER JOIN PAS_User ON AMS_Notification.noti_from_user_id = PAS_User.user_user_id
FROM (`AMS_Notification`, `AMS_Notification`)
JOIN `PAS_User` ON `PAS_User` `ON` AMS_Notification.noti_from_user_id = PAS_User.user_user_id
WHERE `AMS_Notification`.`noti_to_user_id` = '7'
ORDER BY `noti_updated` desc

Deepak Rai · Accepted Answer · 2014-02-25 09:08:17Z

1

$this->db->select('PAS_User.user_first_name, PAS_User.user_last_name, PAS_User.user_avatar_url, AMS_Notification.noti_entitiy_id, AMS_Notification.noti_entity_type, AMS_Notification.noti_updated');
$this->db->from('AMS_Notification');
$this->db->join('PAS_User', 'AMS_Notification.noti_from_user_id = PAS_User.user_user_id');
$this->db->where('AMS_Notification.noti_to_user_id', $userid);
$this->db->order_by("noti_updated", "desc");

$query = $this->db->get();

if($query -> num_rows() == 1) {
    return $query->result();
} else {
    return false;
}

No need to specify table name and JOIN in select part

edited Feb 25, 2014 at 9:08

answered Feb 25, 2014 at 9:02

Deepak Rai

2,2113 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justin Erswell Over a year ago

Hi there thanks for this I now get Error Number: 1066 - Not unique table/alias: 'AMS_Notification'

mickmackusa · Accepted Answer · 2025-11-17 00:17:08Z

It looks like you have accidentally left the FROM and JOIN clauses from your raw query on the end of your select() parameter. This simply needs to be removed.
The from(), where(), and get() calls can be consolidated into one get_where() call. You don't need to nominate AMS_Notification in the from() and get() calls.
Nominate and use table aliases to reduce script noise. Short aliases make your code easier to read.
PAS_User ON at the start of the second parameter of your join() call needs to be removed. Only the relating expression(s) need to be mentioned.
If you are only expecting a single row of data in the result set, it is probably more ideal to return a nullable flat data type instead of a 2d array. Use row() or row_array().
Chain your query builder calls together to enjoy an elegant and succinct script.

public function getNotificationsWithSenderByRecipient(int $userId): ?object
{
    return $this->db
        ->select('
            u.user_first_name,
            u.user_last_name,
            u.user_avatar_url,
            n.noti_entitiy_id,
            n.noti_entity_type,
            n.noti_updated
        ')
        ->join('PAS_User u', 'n.noti_from_user_id = u.user_user_id')
        ->order_by('n.noti_updated', 'desc')
        ->get_where('AMS_Notification n', ['n.noti_to_user_id' => $userId])
        ->row();
}

Collectives™ on Stack Overflow

CodeIgniter query builder coding attempt with join() renders an unintended query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related