Join four tables in codeigniter

Considering your structure your each table should have a one field defined as a primary key and the associations of listing table (likes,views and comments) should relate to that key as a foreign key i expect sno field to be defined as primary key for each table ,so in your case list_id column in likes views and comments will have reference of listing table with with auto generated list id that is 1,2,3 so on then the list_id column in your listing table is not needed any more , same case will apply for your users table and its auto generated user ids now the query part you are using multiple joins so that there will be cross product as mentioned by @FuzzyTree and doing aggregation on the result set will give you wrong results more than expected so that you need a distinct count thats why i have defined primary keys for tables so instead of counting v.views count distinct v.sno same for like and comments

select l.*,count(distinct v.sno) as views,count(distinct li.sno) as likes,count(distinct c.sno) as commentsfrom listings ljoin likes li on(l.sno = li.list_id)join comments c on(l.sno = c.list_id)join `views` v on(l.sno = v.list_id)where l.user_id = 'user100'group by l.sno

Using active record you can write your query something like

$this->db->select ( 'l.*,    count(distinct v.sno) as views,    count(distinct li.sno) as likes,    count(distinct c.sno) as comments' ,false)    ->from ( 'listings as l' )    ->join ( 'likes li', 'l.sno = li.list_id')    ->join ( 'comments c', 'l.sno = c.list_id')    ->join ( 'views v', 'l.sno = v.list_id')    ->where ( 'l.user_id', $user_id)    ->group_by( 'l.sno');

According to your provided data set list 1 has 3 likes,3 views and 1 comment you can find attached demo of above query and also you can find update table definitions with foreign keys and cascading (will help to maintain relations)

Fiddle Demo

Edit for posts with 0 likes/views and comments

using inner join posts with 0 likes/views and comments will not be returned for this you need left join you can see updated demo and using active record you can build query something like below, define join type (left/inner/right) in join() function's 3rd parameter

$this->db->select ( 'l.*,    count(distinct v.sno) as views,    count(distinct li.sno) as likes,    count(distinct c.sno) as comments' ,false)    ->from ( 'listings as l' )    ->join ( 'likes li', 'l.sno = li.list_id','left')    ->join ( 'comments c', 'l.sno = c.list_id','left')    ->join ( 'views v', 'l.sno = v.list_id','left')    ->where ( 'l.user_id', $user_id)    ->group_by( 'l.sno');

Updated Demo

If user_id is not unique to likes, comments or views you will end up with a cross product when doing multiple joins, which will inflate your counts. Because you’re only querying one user_id, subqueries might be the best way to go.

Remember to set the second parameter to select to false so codeigniter doesn't try to escape the subqueries.

$this->db->select ( 'list.*,     (select count(*) from views v where v.user_id = list.user_id) as views,    (select count(*) from likes l where l.user_id = list.user_id) as likes,    (select count(*) from comments c where c.user_id = list.user_id) as comments',false)->from ( 'listings as list' );$this->db->where ( 'list.user_id', $user_id);$query = $this->db->get ();

If you take a closer look at the "wrong" values, and took a look at the individual rows that are contributing to the inflated values, you'll find that you are getting the same rows counted multiple times. (With an inner join, if one of the tables has zero matching rows, that row won't be returned.)

You've got a classic cross product. What's happening with the JOIN operation, every matching row from Likes is getting "matched" to every matching row from Views. If there's three matching rows in Views, and three matching rows in Likes, you're going to get 3x3 rows returned.

Your database design looks okay.

It's the generated SQL query that is the problem.

To fix the query, either

either avoid generating a cross product or count distinct rows*

To avoid a cross product, don't JOIN to both Likes and Views tables. As an alternative, you could avoid the join operations and instead use correlated subqueries in the SELECT list...

 SELECT list.*      , (SELECT COUNT(1) FROM views v WHERE v.list_id = list.list_id) AS count_views      , (SELECT COUNT(1) FROM likes l WHERE l.list_id = list.list_id) AS count_likes   FROM list    WHERE ... 

or, if you do use JOIN operation and generate a cross product, then get a count of unique rows by using an expression like COUNT(DISTINCT pk), so you don't count the same row more than once.

  SELECT list.*       , COUNT(DISTINCT v.`s.no`) AS count_views       , COUNT(DISTINCT l.`s.no`) AS count_likes

To allow rows with zero counts to be returned, you'd want to use outer joins:

    FROM list    LEFT JOIN views v ON v.list_id = list.list_id    LEFT JOIN likes l ON l.list_id = list.list_id

Once you understand what MySQL is doing with the SELECT statement your code is currently generating, it's just a matter of figuring out a SQL statement that will return the result you want.

Once you get a SQL statement that returns the expected results, then it's just a matter of getting CodeIgniter to run SQL statement.