Join four tables in codeigniter

Question

I have four tables Like below

Listings:

------------------------------------------------------------------------------------------------
| list_id                              |user_id | name | category | fees | details |created_on |
------------------------------------------------------------------------------------------------
| 90cc57a4-f782-4c57-ac98-1965c57ece57 |user 100 |satwik| music   | 500  | dummy   |2015-08-02 |
------------------------------------------------------------------------------------------------

changed my list_id from a random string to UUID.
from this comment on php.net
see this stackoverflow question
In listings table list_id is primary key, i know using autoincreament is best but my requirement is like this. and s.no is primary key for rest of tables.

I need to generate a random key from PHP side because to set list_id in session and to avoid another query to set list_id in session. Likes:

----------------------------------------------------------------
|.sno | list_id                              | user_id | likes |
----------------------------------------------------------------
| 1   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user110 |  1    |
----------------------------------------------------------------
| 2   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user215 |  1    |
----------------------------------------------------------------
| 3   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user200 |  1    |
----------------------------------------------------------------

comments:

-------------------------------------------------------------------------
|.sno | user_id | list_id                              | comment         |
-------------------------------------------------------------------------
|  1  | user 205| 90cc57a4-f782-4c57-ac98-1965c57ece57 | dummy comment   |
-------------------------------------------------------------------------

Views:

----------------------------------------------------------------
|.sno | list_id                              | user_id | views |
----------------------------------------------------------------
| 1   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user110 |  2    |
----------------------------------------------------------------
| 2   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user215 |  1    |
----------------------------------------------------------------
| 3   | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user200 |  1    |
----------------------------------------------------------------

I am trying to get a list of user id user100 from listings table

and need to get count of views,like,comments for the list id of the user from various tables i have.

i tried using this query from codeigniter

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

I am getting wrong views and likes and comments count.

is this database design good or i need to change anything. i have less awarness in using joins please help me.

EDIT:

I tried this query from answers below

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

I am getting what i want but this way of querying fails(return null) if i don't have any comments or views or likes.

Answer 1

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 comments
from listings l
join 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

Answer 2

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 ();