CODEWITHSUNDEEP
codeigniteractiverecord
Dave Driesmans
Dave Driesmans

Reputation: 819

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

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

Upvotes: 1

Views: 1896

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

$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.

Upvotes: 2

Sergey Telshevsky
Sergey Telshevsky

Reputation: 12197

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!

Upvotes: 1

Related Questions