codeigniter : want not first but last row of joined table?

Question

now my joined table returns the first posts row, i want it to have the last row for each topic

this is my code now

$this->db->select('*');
      ->where('disccategory_id', $cat);
       ->from('discussiontopics as topics');
       ->join('discussionposts as posts', 'posts.topic_id = topics.id', 'left');
       ->order_by('posts.id', 'desc');
       ->group_by('topics.id');

maybe i can only do it with a select inside the join? but not sure how to do that with Codeigniter's Active Record

Cant you order it in the opposite way and select the top? Or else go for the bottom? — Rohit Vipin Mathews, Dec 13 '12 at 10:32
$this->db->order_by('posts.id', 'asc'); doesn't change the result. i only get one row — Dave Driesmans, Dec 13 '12 at 10:38

score 2 · Accepted Answer · answered Dec 13 '12 at 15:38

2

$this->db->query("
SELECT *
FROM (  SELECT *,
            topics.id AS topic_id
        FROM discussiontopics as topics
        LEFT JOIN discussionposts as posts
        ON posts.topic_id = topics.id
        ORDER BY posts.id DESC) AS h
GROUP BY topic_id
");

If you wonder why yours doesn't work, run a echo $this->db->last_query(); after runned query, and you'll see how if behaves.

answered Dec 13 '12 at 15:38

Robin Castlin

10,956
1
28
44

this is how i solved it `$query = $this->db->query(" SELECT * FROM discussiontopics as topics LEFT JOIN ( SELECT * FROM discussionposts ORDER BY id DESC) AS selectedposts ON (selectedposts.topic_id = topics.id) WHERE topics.disccategory_id = $cat GROUP BY topics.id; ");` – Dave Driesmans Dec 13 '12 at 15:41
when parsing values to `query()`, send them through the second argument as an array. `query(" .. topics.disccategory_id = ? ..", array($cat))` this will save your day against SQL injections. – Robin Castlin Dec 13 '12 at 15:47

score 1 · Answer 2 · edited May 23 '17 at 12:12

Actually it should not work, the reason why is that ORDER BY is executed after grouping, if you write

->order_by(...)
->group_by(...)

or

->group_by(...)
->order_by(...)

it doesn't matter, the query will always have GROUP BY before ORDER BY.

What you are most likely having problems with is discussed here:

MySQL Order before Group by

So I would rather write a query manually using

$this->db->query('
    SELECT ...
');

Or even get a list of rows (your query without grouping) and only take the first one.

Hope it helps!

codeigniter : want not first but last row of joined table?

2 Answers2