How to form the query to MySQL in CodeIgniter'e?

Good day to day
Please help.

I have articles(articles) and there are categories(politics, economy, etc.)
How to make a/to generate a query so that when I go to the category page: (list of my categories)
that should is displayed for example as:
5 politics| Economy 10 (i.e. five articles to the category policy). Here's what I want.
I have my site made, for example

- site.com/categories/articles/politics .(displays the article to the category "politics.").
PS
I hope you understand.
Using the framework CodeIgniter.
and please do not send me abstraction, and the specifics!
April 4th 20 at 00:42
2 answers
April 4th 20 at 00:44
Solution
How to form the query to MySQL in CodeIgniter'e?
SQL query does not depend on PHP framework, if that.

A normal subquery \ JOIN.

and please do not send me abstraction, and the specifics!

You have not provided sufficient data for specifics, and do not want to give her, he thought.
Request know how to use? (The same can and using queryBuilder to write)

SELECT 
categories.*,
 (SELECT COUNT(`id`) FROM `articles` WHERE `category_id` = categories.id) as articlesCount
FROM 
`categories`
ORDER
 BY `id`
DESC
April 4th 20 at 00:46
Solution
$this->db->select("category.id category.title, COUNT(`post.id`) as posts_count")
->from('category')
 ->join('post','category.id = post.category_id')
 ->where(array('post.category_id !=' => NULL, 'category.id !=' => Is NULL))
 ->group_by(array("category.id", "category.title"))
 ->get();


the idea of this, but not tested

Find more questions by tags MySQLCodeIgniter