CodeIgniter database query with left and inner join

Question

i am having troubles translating this database query

 SELECT 'conversations','conversation_id',
'conversations','conversation_subject',
MAX('conversations_messages','message_date') AS 'conversation_last_reply'
FROM 'conversations'
LEFT JOIN 'conversations_messages' ON 'conversations'.'conversation_id' = 'conversations_messages'.'conversation_id'
INNER JOIN 'conversations_members' ON 'conversations'.'conversation_id' = 'conversations_members'.'conversation_id'
WHERE 'conversations_members', 'user_id' = $sender_id
AND 'conversations_members','conversation_deleted' = 0
GROUP BY 'conversations'.'conversation_id'
ORDER BY 'conversation_last_reply'  DESC";

to codeignitor's active records .

I have tried it this way

$this->db->select('conversation_id, conversation_subject');

    $this->db->get('conversations');

     $this->db->select_max('message_date', 'conversation_last_reply');

    $this->db->get('conversations_messsages');
   $this->db->from('conversations');
   ........

But i get stack at left and inner join . So i tried it this way

 $query = $this->db->query(
    SELECT 
          'conversations','conversation_id',
         'conversations','conversation_subject',
    MAX('conversations_messages','message_date') AS 'conversation_last_reply'
    FROM 'conversations'
    LEFT JOIN 'conversations_messages' ON 'conversations'.'conversation_id' = 'conversations_messages'.'conversation_id'
    INNER JOIN 'conversations_members' ON 'conversations'.'conversation_id' = 'conversations_members'.'conversation_id'
    WHERE 'conversations_members', 'user_id' = $sender_id
    AND 'conversations_members','conversation_deleted' = 0
    GROUP BY 'conversations'.'conversation_id'
    ORDER BY 'conversation_last_reply'  DESC"
        );
   return $query->result();

but there's errors everywhere.

Ajay Makwana · Accepted Answer · 2016-01-02 06:18:09Z

1

You have mistaken in quote

$query = $this->db->query("SELECT 'conversations','conversation_id',
         'conversations','conversation_subject',
    MAX('conversations_messages','message_date') AS 'conversation_last_reply'
    FROM 'conversations'
    LEFT JOIN 'conversations_messages' ON 'conversations'.'conversation_id' = 'conversations_messages'.'conversation_id'
    INNER JOIN 'conversations_members' ON 'conversations'.'conversation_id' = 'conversations_members'.'conversation_id'
    WHERE 'conversations_members', 'user_id' = $sender_id
    AND 'conversations_members','conversation_deleted' = 0
    GROUP BY 'conversations'.'conversation_id'
    ORDER BY 'conversation_last_reply' DESC"
        );
   return $query->result();

answered Jan 2, 2016 at 6:18

Ajay Makwana

2,3921 gold badge19 silver badges32 bronze badges

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

Comments

Mourad MAMASSI · Accepted Answer · 2017-11-08 12:22:16Z

1

You can try something like this

// left, right, outer, inner, left outer, and right outer
$this->db->join('comments', 'comments.id = blogs.id', 'left');

https://www.codeigniter.com/userguide3/database/query_builder.html

answered Nov 8, 2017 at 12:22

Mourad MAMASSI

8851 gold badge12 silver badges15 bronze badges

Collectives™ on Stack Overflow

CodeIgniter database query with left and inner join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related