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.

I am not maintaining any relation just trying to join table on list_id — saikiran, Aug 04 '15 at 06:35
Hello downvoter , i expect minimum explanation or what wrong i did — saikiran, Aug 04 '15 at 06:41

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

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`

@M Khalid Junaid Edited my question, 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. — saikiran, Aug 11 '15 at 16:59
@saikiran i have posted answer according to your provided data set and yes using inner join posts with 0 likes/view and comments will not be returned you have use left join you can see [`demo`](http://www.sqlfiddle.com/#!9/a9a3d/1) here and using active record `->join ( 'likes li', 'l.sno = li.list_id','left')` if need this in answer i can update my answer — M Khalid Junaid, Aug 11 '15 at 17:51
yeah, please update your answer using left join , i will reward you with bounty as promised and thanks for the answer :) — saikiran, Aug 11 '15 at 17:58
I don't have s.no in listings and i wanted to know is using UUID from PHP is good idea. see the reference links i provided — saikiran, Aug 11 '15 at 18:01
@saikiran you can also use mysql's `UUID()` function leave it to mysql so that it will be database stuff just execute query from php and for primary key leave it to mysql — M Khalid Junaid, Aug 11 '15 at 18:07
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86720/discussion-between-saikiran-and-m-khalid-junaid). — saikiran, Aug 11 '15 at 18:08

FuzzyTree · Accepted Answer · 2015-08-07T05:16:07.623

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

score 1 · Answer 3 · edited Jan 17 '16 at 13:58

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.

score 0 · Answer 4 · answered Aug 08 '15 at 06:21

select a.list_id,a.user_id,a.name,a.category,a.fees,a.details,a.created_on,
(select sum(likes) from  Likes where user_id=a.user_id) "total likes",
(select count(*) from  comments where user_id=a.user_id) "total comments",
(select sum(views) from  views  where user_id=a.user_id) "total views",
from Listings as a
where a.user_id ='user 100'

shankar kumar · Answer 5 · 2015-08-04T06:37:19.800

-1

kindly change your query to get correct count as bellow.

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

        $this->db->where ( 'list.user_id', $user_id);
        $this->db->group_by( 'v.list_id');
        $this->db->group_by( 'l.list_id');
        $this->db->group_by( 'c.list_id');
      $query = $this->db->get ();

edited Aug 04 '15 at 06:37

answered Aug 04 '15 at 06:23

shankar kumar

648
5
9

user_id is different in views and comments and likes @shanusingh – saikiran Aug 04 '15 at 06:30
just group by those field that is common in listing and comments , likes, views and that should unique or put listing table id to every other table as column and then group by that id – shankar kumar Aug 04 '15 at 06:35
I modified answer, please check, – shankar kumar Aug 04 '15 at 06:38
this is not giving me proper count – saikiran Aug 04 '15 at 06:40
then alter your table comments, likes, views and add one primary key column of listing table and then use that column in group by – shankar kumar Aug 04 '15 at 06:43
giving the same results even though i added primary key for all of them – saikiran Aug 04 '15 at 06:49
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85068/discussion-between-saikiran-and-shanusingh). – saikiran Aug 04 '15 at 06:50

score -1 · Answer 6 · answered Aug 08 '15 at 21:15

If you are afraid of too much joins, time you tried out the SQL IN statement. Do something like;

// You can include query binding if you like
$sql = SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...) WHERE SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...));

Join four tables in codeigniter

6 Answers6

Fiddle Demo

Updated Demo

`Fiddle Demo`

`Updated Demo`