如何生成周表并按日和小时拆分
First, to be clear; there is an example like this:
I can get courses with this query:
$qb
->select("c")
->from("ATLReservationBundle:Course", "c")
->where("WEEK(c.courseDate, 1) = WEEK(CURRENT_DATE(), 1)")
->orderBy("c.courseDate", "ASC");
QueryResult:
{ "courses": [
{
"id": 110,
"name": "Pilates",
"quota": 8,
"course_date": "2015-08-31T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 135,
"name": "Spinning",
"quota": 8,
"course_date": "2015-08-31T19:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 154,
"name": "Kick Box",
"quota": 6,
"course_date": "2015-08-31T20:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 133,
"name": "Spinning",
"quota": 8,
"course_date": "2015-09-01T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 113,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-01T18:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 112,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-01T19:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 150,
"name": "Spinning",
"quota": 8,
"course_date": "2015-09-01T19:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 136,
"name": "TRX Group",
"quota": 5,
"course_date": "2015-09-01T20:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 159,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-02T10:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 156,
"name": "Stretching",
"quota": 7,
"course_date": "2015-09-02T18:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 127,
"name": "Zumba",
"quota": 6,
"course_date": "2015-09-02T19:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 148,
"name": "Kick Box",
"quota": 6,
"course_date": "2015-09-02T20:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 138,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-03T12:15:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 121,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-03T19:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 117,
"name": "TRX",
"quota": 8,
"course_date": "2015-09-03T20:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 131,
"name": "Spinning",
"quota": 8,
"course_date": "2015-09-03T20:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 142,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-04T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 147,
"name": "LBT 30 min.",
"quota": 8,
"course_date": "2015-09-04T19:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 141,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-04T19:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
{
"id": 130,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-05T14:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
} ] }
But i want to group this data like;
DAY - 31
HOUR - 12:15 > Coursename
HOUR - 19:30 > Coursename
DAY - 01
HOUR - 10:00...
I don't know how to do this, anyone?
UPDATE
New Query:
$qb
->select("c, HOUR(c.courseDate) as hour, DAY(c.courseDate) as day")
->from("ATLReservationBundle:Course", "c")
->where("WEEK(c.courseDate, 1) = WEEK(CURRENT_DATE(), 1)")
->orderBy("c.courseDate", "ASC")
->groupBy("hour, day");
But; there is a problem with this query i could not group by HOUR(c.courseDate), DAY(c.courseDate) because i got this error:
Error: Cannot group by undefined identification or result variable.
So, i defined hour and day as variable in select area. I don't want to see day and hour as different variables.
QueryResult:
{
"courses": [
{
"0": {
"id": 110,
"name": "Pilates",
"quota": 8,
"course_date": "2015-08-31T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "12",
"day": "31"
},
{
"0": {
"id": 135,
"name": "Spinning",
"quota": 8,
"course_date": "2015-08-31T19:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "19",
"day": "31"
},
{
"0": {
"id": 154,
"name": "Kick Box",
"quota": 6,
"course_date": "2015-08-31T20:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "20",
"day": "31"
},
{
"0": {
"id": 133,
"name": "Spinning",
"quota": 8,
"course_date": "2015-09-01T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "12",
"day": "1"
},
{
"0": {
"id": 113,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-01T18:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "18",
"day": "1"
},
{
"0": {
"id": 112,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-01T19:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "19",
"day": "1"
},
{
"0": {
"id": 136,
"name": "TRX Group",
"quota": 5,
"course_date": "2015-09-01T20:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "20",
"day": "1"
},
{
"0": {
"id": 159,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-02T10:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "10",
"day": "2"
},
{
"0": {
"id": 156,
"name": "Stretching",
"quota": 7,
"course_date": "2015-09-02T18:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "18",
"day": "2"
},
{
"0": {
"id": 127,
"name": "Zumba",
"quota": 6,
"course_date": "2015-09-02T19:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "19",
"day": "2"
},
{
"0": {
"id": 148,
"name": "Kick Box",
"quota": 6,
"course_date": "2015-09-02T20:00:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "20",
"day": "2"
},
{
"0": {
"id": 138,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-03T12:15:00+0000",
"studio": {
"id": 1,
"name": "Stüdyo 1"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "12",
"day": "3"
},
{
"0": {
"id": 121,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-03T19:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "19",
"day": "3"
},
{
"0": {
"id": 117,
"name": "TRX",
"quota": 8,
"course_date": "2015-09-03T20:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "20",
"day": "3"
},
{
"0": {
"id": 142,
"name": "Pilates",
"quota": 8,
"course_date": "2015-09-04T12:15:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "12",
"day": "4"
},
{
"0": {
"id": 141,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-04T19:30:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "19",
"day": "4"
},
{
"0": {
"id": 130,
"name": "Crunch 30 Min.",
"quota": 8,
"course_date": "2015-09-05T14:00:00+0000",
"studio": {
"id": 2,
"name": "Stüdyo 2"
},
"trainer": {
"id": 1,
"name": "-"
}
},
"hour": "14",
"day": "5"
}
]
}
I think i'm close now. But it must be like;
DAY
HOUR
HOUR
DAY
HOUR
HOUR
TO get your visual example table, use:
WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday
select hour(date), -- group by hour
case when weekday(date)=0 then course_name else null end as Monday, -- 1 col for each weekday
.....
case when weekday(date)=6 then course_name else null end as Sunday
from table
where -- select a monday to sunday calendar week unique by year
date(date) - interval weekday(date)=date(curdate()) - interval weekday(curdate())
group by hour(date)
To get the other variant you mentioned:
select right(date, 2), -- day of month,
hour(date) --hour,
course_name
from table
group by 1,2