notifications always come back as 1

[audienceWatching]

Hello all,

Im trying to combine fetching multiple pieces of clients information and getting a count of how many posts they have in a current state.

However with the query below notifications always comes back as a minumum of 0, I'm guessing its because the COUNT(*) isnt specifically aimed at the left join for Post (which it should be)

However if I do count(p.*) I get mysql errors.

What is the correct way todo this?

SELECT count(*) as notifications, c.alias, c.id, cf.page_id as facebook_page_id, cf.username as facebook_username, ct.username as twitter_username, cg.page_id as googleplus_page_id, cg.username as googleplus_username
				FROM Client AS c 
				LEFT JOIN Client_Facebook AS cf ON (cf.client_id = c.id) 
				LEFT JOIN Client_Twitter AS ct ON (ct.client_id = c.id) 
				LEFT JOIN Client_Googleplus AS cg ON (cg.client_id = c.id) 
				LEFT JOIN Post AS p ON (p.client_id = c.id AND p.status = 2)
				WHERE c.id = 18

[swampBoogie]

You can do

select (select count(*) 
          from Post as P
         where p.client_id = c.id
           and p.status = 2) as notifications, 
       c.alias, 
       c.id, 
       cf.page_id as facebook_page_id, 
       cf.username as facebook_username, 
       ct.username as twitter_username, 
       cg.page_id as googleplus_page_id, 
       cg.username as googleplus_username
  from Client as c
  left
  join Client_Facebook as cf
    on cf.client_id = c.id
  left
  join Client_Twitter as ct
    on ct.client_id = c.id
  left
  join Client_Googleplus as cg
    on cg.client_id = c.id
 where c.id = 18

or

select p.notifications, 
       c.alias, 
       c.id, 
       cf.page_id as facebook_page_id, 
       cf.username as facebook_username, 
       ct.username as twitter_username, 
       cg.page_id as googleplus_page_id, 
       cg.username as googleplus_username
  from Client as c
  left
  join Client_Facebook as cf
    on cf.client_id = c.id
  left
  join Client_Twitter as ct
    on ct.client_id = c.id
  left
  join Client_Googleplus as cg
    on cg.client_id = c.id
  left
  join (select count(*) as notifications, 
               client_id 
          from Post
         where status = 2
         group
            by client_id) p
    on p.client_id = c.id
 where c.id = 18

In general the Mysql optimizer is very poor at handling subqueries, so the later query may be faster to execute.

[audienceWatching]

Thank you, I used the latter query and it takes 33ms for 400 clients with over 14'000 posts counted.

[r937]

the relationship between Client and Client_Facebook, is it one-to-one? how do you enforce this, or is it possible that a client could have more than one facebook row? i'll bet Client_Facebook has its own auto_increment, right? do you do anything to prevent more than one per client?

this is not a trivial question, and it's the same for twitter and googleplus

so perhaps if you only want one of each, those columns should be in the same table as Clients

anyhow, about counting posts...

use COUNT(p.client_id) instead of COUNT(*)

just be aware that if a client has more than one facebook, or more than one twitter, or more than one googleplus, those counts will get blowed up accordingly

[audienceWatching]

the relationship between Client and Client_Facebook, is it one-to-one? how do you enforce this, or is it possible that a client could have more than one facebook row? i'll bet Client_Facebook has its own auto_increment, right? do you do anything to prevent more than one per client?

this is not a trivial question, and it's the same for twitter and googleplus

so perhaps if you only want one of each, those columns should be in the same table as Clients

anyhow, about counting posts...

use COUNT(p.client_id) instead of COUNT(*)

just be aware that if a client has more than one facebook, or more than one twitter, or more than one googleplus, those counts will get blowed up accordingly

There used to be multiple Client_Facebook with an id column that was an auto increment, it was then changed to a single row so now there in so auto increment.