[eluser]Majd Taby[/eluser]
Hey guys, I'm wondering if someone has come across the same issue and solved it, I want to run the following query through active record:
Code:
SELECT r.forum_name
, t.thread_name
, u.display_name
, m.max_last_posted
, r.id as reply_id
FROM ( SELECT thread_id
, MAX(date_posted) AS max_last_posted
FROM forum_replies
GROUP
BY thread_id ) AS m
INNER
JOIN forum_threads AS t
ON t.id = m.thread_id
INNER
JOIN forum_rooms AS r
ON t.room_id = r.id
INNER
JOIN forum_replies as reply
ON reply.date_posted = m.max_last_posted
AND reply.thread_id = m.thread_id
INNER
JOIN users AS u
ON reply.user_id = u.id
so I set it up as follows:
Code:
$this->db->select('r.forum_name, t.thread_name, u.display_name, m.max_last_posted')
->from('(SELECT thread_id , MAX(date_posted) AS max_last_posted FROM forum_replies GROUP BY thread_id) AS m')
->join('forum_threads AS t','t.id = m.thread_id','inner')
->join('forum_rooms AS r','t.room_id = r.id','inner')
->join('forum_replies AS new_r','r.id = new_r.id','inner')
->join('users AS u','new_r.user_id = u.id','inner');
The above php produces the following query:
Code:
SELECT r.forum_name, t.thread_name, u.display_name, m.max_last_posted
FROM ((`SELECT` thread_id , MAX(`date_posted`) `max_last_posted` FROM `forum_replies` GROUP `BY` thread_id) `m`)
INNER JOIN `forum_threads` AS t ON t.id = m.thread_id
INNER JOIN `forum_rooms` AS r ON t.room_id = r.id
INNER JOIN `forum_replies` AS new_r ON r.id = new_r.id
INNER JOIN `users` AS u ON new_r.user_id = u.id
Notice how SELECT, max_last_posted, BY, and m are escaped as table names in the second line.
