Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Week Planning View Query

    The context:

    2 differents entities: user and center,
    The user is on a center for a certain date

    Model:
    Code:
    +-------------+
    | user_master |
    +-------------+
    | user_id     |
    | user_name   |
    +-------------+
    
    +---------------+
    | center_master |
    +---------------+
    | center_id     |
    | center_name   |
    +---------------+
    
    +----------------+     <--------- user to center relation
    | user_center    |
    +----------------+
    | user_center_id |
    | user_id        |
    | center_id      |
    | date           |
    +----------------+
    Sample data
    Code:
    +-------------------------+
    |           center_master |
    +-----------+-------------+
    | center_id | center_name |
    +-----------+-------------+
    |         0 | Paris_1     |
    |         1 | Paris_2     |
    |         2 | NewYork_1   |
    |         3 | NewYork_2   |
    +-----------+-------------+
    
    +---------------------+
    |         user_master |
    +---------+-----------+
    | user_id | user_name |
    +---------+-----------+
    |       0 | Mark      |
    |       1 | Dupont    |
    |       2 | Peter     |
    |       3 | Jerome    |
    +---------+-----------+
    
    +---------------------------------------------------+
    |                                       user_center |
    +----------------+---------+-----------+------------+
    | user_center_id | user_id | center_id | date       |
    +----------------+---------+-----------+------------+
    |              0 |       2 |         1 | 2005-12-12 |
    |              1 |       2 |         2 | 2005-12-13 |
    |              2 |       3 |         0 | 2005-12-13 |
    |              3 |       3 |         3 | 2005-12-16 |
    |              4 |       0 |         3 | 2005-12-14 |
    |              5 |       1 |         0 | 2005-12-15 |
    |              6 |       2 |         3 | 2005-12-14 |
    |              6 |       0 |         3 | 2005-12-15 |
    +----------------+---------+-----------+------------+
    I'd like to get a weekly view for a given week
    This is the result that I'm searching for :

    Code:
    +---------------------------------------------------------------+
    |                                                        Result |
    +-------------+--------+--------+-------------+--------+--------+
    | center_name | Mon 12 | Tue 13 | Wed 14      | Tue 15 | Fri 16 |
    +-------------+--------+--------+-------------+--------+--------+
    | Paris_1     |        | Peter  |             | Dupont |        |
    | Paris_2     | Peter  | Jerome |             |        |        |
    | NewYork_1   |        |        |             | Mark   | Jerome |
    | NewYork_2   |        |        | Mark, Peter |        |        |
    +-------------+--------+--------+-------------+--------+--------+
    What's wrong on my query ?
    Code:
    SELECT
    	cem.center_name AS "center_name",
    	GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 1, usm.user_name, "")) AS "Mon",
    	GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 2, usm.user_name, "")) AS "Tue",
    	GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 3, usm.user_name, "")) AS "Wed",
    	GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 4, usm.user_name, "")) AS "Thu",
    	GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 5, usm.user_name, "")) AS "Fri"
    FROM
    	user_center AS uc
    	LEFT JOIN center_master AS cem ON (uc.center_id = cem.center_id)
    	LEFT JOIN user_master AS usm ON (uc.user_id = usm.user_id)
    WHERE
    	DATE_FORMAT(uc.date, "%u") = 50
    GROUP BY cem.center_id
    ORDER BY 1
    Last edited by gtk; 11-15-05 at 10:05.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    SELECT
    cem.center_name AS "center_name",
    GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 1, usm.user_name, "")) AS "Mon",
    GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 2, usm.user_name, "")) AS "Tue",
    GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 3, usm.user_name, "")) AS "Wed",
    GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 4, usm.user_name, "")) AS "Thu",
    GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 5, usm.user_name, "")) AS "Fri"
    FROM
    user_center AS uc
    LEFT JOIN center_master AS cem ON (uc.center_id = cem.center_id)
    LEFT JOIN user_master AS usm ON (uc.user_id = usm.user_id)
    WHERE
    DATE_FORMAT(uc.date, "%u") = 50
    GROUP BY cem.center_id
    ORDER BY 1
    What does that query give you?

    Off the top of my head...does something like this work? (I know it's not the correct format, but will it return the correct data?)
    Code:
    SELECT
    	cem.center_name AS center_name,
    	DATE_FORMAT(uc.date, "%w") AS day_of_week,
    	GROUP_CONCAT(usm.user_name) AS users,
    FROM
    	user_center AS uc
    	LEFT JOIN center_master AS cem ON (uc.center_id = cem.center_id)
    	LEFT JOIN user_master AS usm ON (uc.user_id = usm.user_id)
    WHERE
    	DATE_FORMAT(uc.date, "%u") = 50
    GROUP BY cem.center_id, DATE_FORMAT(uc.date, "%w")
    ORDER BY cem.center_name, DATE_FORMAT(uc.date, "%w")
    (just an idea...never used group_concat, but I believe it goes by the group by clause and this may be where your problem is)

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Yes I know that it is the easier way (and the best) to get the data,
    but I'm trying to unload the maximum from the code to MySQL,
    because actually I have developped a querier/result dumper (with excel generation) directly detinated to users.

  4. #4
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by gtk
    What's wrong on my query ?
    Don't know. Why don't you tell us?
    (Errors? unexpected output? exploding server?)

    --
    felix

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Sorry, in fact my query is working
    I was having a little mistake (not on the post)
    Actually I'm just getting some
    Code:
    ,,,mark,,peter,,
    kind of output but I'm not sure there is a way to do
    Code:
    mark,peter
    .
    For now I'm using DISTINCT in my GROUP_CONCAT() function but the output is now
    Code:
    ,mark,peter
    (it's logical).
    Last edited by gtk; 11-16-05 at 05:18.

  6. #6
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by gtk
    Actually I'm just getting some
    Code:
    ,,,mark,,peter,,
    kind of output but I'm not sure there is a way to do
    Code:
    mark,peter
    .
    Try
    Code:
     COALESCE(GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 1, usm.user_name, NULL)), '')
    --
    felix

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by felixg
    Try
    Code:
     COALESCE(GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 1, usm.user_name, NULL)), '')
    --
    felix
    Won't using COALESCE() only return one value? Shouldn't it work without the COALESCE()? ...because GROUP_CONCAT() shouldn't return the NULL values from when (IF == false), right?

  8. #8
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by jfulton
    Won't using COALESCE() only return one value? Shouldn't it work without the COALESCE()? ...because GROUP_CONCAT() shouldn't return the NULL values from when (IF == false), right?
    Yes, COALESCE() returns only one value, but there is only one value (a string of names separated by ',') after GROUP_CONCAT() has done its work.

    The COALESCE() is just to avoid NULLs where there are no names for GROUP_CONCAT() to process.

    --
    felix

Posting Permissions

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