CODEWITHSUNDEEP
phpmysqldatabasecodeigniterjoin
saikiran
saikiran

Reputation: 2435

Join four tables in codeigniter

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.

Upvotes: 12

Views: 2734

Answers (6)

spencer7593
spencer7593

Reputation: 108380

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.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 5

O'Brien
O'Brien

Reputation: 43

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,...));

Upvotes: -1

CHINTU RANA
CHINTU RANA

Reputation: 97

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'

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

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

Upvotes: 4

shankar kumar
shankar kumar

Reputation: 648

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

Upvotes: -1

Related Questions