How i can select most common value with codeigniter

Hi guys,
I have a question. I wanted to know how to select the most common value in a table while doing a join. For example: I have a "blog" table and another table "comments_blog" and I want to select from the "comments" the ID of the post table that most repeats.
The table would have this content:

// id - id_post - content
1 2 test
2 2 test2
3 2 test3
4 3 test4
4 5 test5

I looked in the official guide of codeigniter but found nothing. and truth i never done this type of query. I Try doing this:

Code:

$this->db->join( 'comments_blog', 'comments_blog' . '.' . 'id_blog' . ' = ' . 'blog' . '.' . 'id' );
$query = $this->db->query('SELECT id_blog AS magnitude FROM comments_blog GROUP BY id_blog ORDER BY magnitude DESC LIMIT 3');

I want to do this to know which is the most popular post. Not get me wrong, I do not want to you do the work for me, i just want to explain to me how you could do

Its more sql question than codeigniter.. group by and order by count perhaps...

Best VPS Hosting : Digital Ocean

It looks like you are trying to do something like this:  http://stackoverflow.com/questions/76936...d-in-mysql

First of all, you need to select count(*) as magnitude, not id_blog as magnitude.  Look at the example in that answer more closely.

Secondly, you are mixing Active Record syntax ( $this->db->join() ) with the command for doing a simple query ( $this->db->query() ).  I could be wrong, but I don't think you can mix those two things.

I think you either need to add the join to the $this->db->query()  statement, or use the Active Record syntax here: http://www.codeigniter.com/user_guide/da...ecord.html   $this->db->select(), $this->db->get(), etc

Make sure CodeIgniter is sending the query as you expect by using the profiler  $this->output->enable_profiler(TRUE);  or sending $this->db->last_query(); to the log

Hi sv3tli0,
i know it. But did not know how to do it using Active Record syntax.

thanks bclinton, now i understand better.