Преобразуйте запрос SELECT с помощью INNER JOIN, LEFT JOIN, GROUP BY, ORDER BY и MAX() в построитель запросов CodeIgnite ⇐ Php

[Anonymous]

У меня возникли проблемы с переводом этого запроса к базе данных

Код: Выделить всё

 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";

в построитель запросов CodeIgniter.
Я пробовал так

Код: Выделить всё

$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');
...

Но я застреваю в левом и внутреннем соединении. Итак, я попробовал так:

Код: Выделить всё

$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();

но везде ошибки.

Подробнее здесь: https://stackoverflow.com/questions/345 ... by-and-max