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
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.
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.