| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-15-05, 07:17
|
|
Registered User
|
|
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
|
|
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 09:05.
|
11-15-05, 12:17
|
|
Registered User
|
|
Join Date: Apr 2005
Location: Baltimore, MD
Posts: 297
|
|
Quote:
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)
|
|
11-16-05, 03:19
|
|
Registered User
|
|
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.
|
|
11-16-05, 03:52
|
|
Registered User
|
|
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
|
|
11-16-05, 04:15
|
|
Registered User
|
|
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 kind of output but I'm not sure there is a way to do .
For now I'm using DISTINCT in my GROUP_CONCAT() function but the output is now (it's logical).
|
Last edited by gtk; 11-16-05 at 04:18.
|
11-16-05, 05:18
|
|
Registered User
|
|
Join Date: Apr 2005
Location: Lier, Belgium
Posts: 122
|
|
Quote:
|
Originally Posted by gtk
Actually I'm just getting some kind of output but I'm not sure there is a way to do . |
Try
Code:
COALESCE(GROUP_CONCAT(IF(DATE_FORMAT(uc.date, "%w") = 1, usm.user_name, NULL)), '')
--
felix
|
|
11-16-05, 10:01
|
|
Registered User
|
|
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?
|
|
11-16-05, 10:11
|
|
Registered User
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Week Planning View Query
