SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    notifications always come back as 1

    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?

    Code MySQL:
    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

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    You can do

    Code:
    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

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

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, I used the latter query and it takes 33ms for 400 clients with over 14'000 posts counted.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •