I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result.
More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the next date occurring on the same weekday where such a date does not occur in A, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday... Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like:
A
id | date
a-a 2014-11-11
a-b 2014-11-13
a-c 2014-11-18
B
id | weekday
b-a Tuesday
b-b Tuesday
b-c Wednesday
Then the resulting sort (if start date is 2014-11-10, a Monday) would be:
sorted
id | date
a-a 2014-11-11
b-c 2014-11-12 (Wednesday)
a-b 2014-11-13
a-c 2014-11-18
b-a 2014-11-25 (Tuesday)
b-b 2014-12-02 (Tuesday)
I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that
Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.
So I reimplemented the subquery to move the variable to a WHERE
clause, using JOIN
s, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out:
SELECT
@next_date as next_date,
@tmp_i := CASE w.weekday + 0
WHEN 0 THEN @idxw_U:=@idxw_U+1
WHEN 1 THEN @idxw_M:=@idxw_M+1
WHEN 2 THEN @idxw_T:=@idxw_T+1
WHEN 3 THEN @idxw_W:=@idxw_W+1
WHEN 4 THEN @idxw_R:=@idxw_R+1
WHEN 5 THEN @idxw_F:=@idxw_F+1
WHEN 6 THEN @idxw_S:=@idxw_S+1
END as idxW,
@idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset,
@idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr,
w.weekday as weekday,
@dayofweek:= w.weekday+0 as dweekday,
@TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,
@clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
IFNULL(
(
SELECT next_slot FROM (
SELECT
-- DAYOFWEEK(d.date) as weekdaynum,
-- dayname(d.date) as weekday,
-- d.date,
-- MIN(d2.date) as min_d2,
DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot,
-- DATEDIFF(d2.date, d.date) as datediff,
-- DATEDIFF(d2.date, d.date) DIV 7 as weeksdiff,
-- DATEDIFF(MIN(d3.date), d2.date) DIV 7 as nextdiff
FROM t_date as d
JOIN t_date as d2
JOIN t_date as d3
WHERE d.date >= @TMP_date
and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) = DAYOFWEEK(d2.date)
= DAYOFWEEK(d3.date)
and d2.date > d.date
and d3.date > d2.date
and DATEDIFF(d2.date, d.date) = 7
GROUP BY d.date
ORDER BY d.date ASC
) as t
-- these 3 lines below are the ones I need to figure out how to accomplish without an outer query
-- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results)
WHERE nextdiff >1
ORDER BY date ASC
LIMIT 1
)
, CONCAT('dow: ',@dayofweek,' ',@TMP_date) )
-- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week
,
0
) as clash_offset,
@NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
w.extra
FROM `t_weekday` as w
JOIN (SELECT
@idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
@next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY)
) as varrs
ORDER BY date DESC, weekday, id ASC
@TMP_date
is the starting (minimum) date. The subquery is the one inside the IFNULL
, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()
).
As a test, I did:
SELECT
@next_date as next_date,
@tmp_i := CASE w.weekday + 0
WHEN 0 THEN @idxw_U:=@idxw_U+1
WHEN 1 THEN @idxw_M:=@idxw_M+1
WHEN 2 THEN @idxw_T:=@idxw_T+1
WHEN 3 THEN @idxw_W:=@idxw_W+1
WHEN 4 THEN @idxw_R:=@idxw_R+1
WHEN 5 THEN @idxw_F:=@idxw_F+1
WHEN 6 THEN @idxw_S:=@idxw_S+1
END as idxW,
@idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset,
@idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr,
w.weekday as weekday,
@dayofweek:= w.weekday+0 as dweekday,
@TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,
@clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after),
IFNULL(
(
-- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem
SELECT @TMP_date as f
LIMIT 1
-- ) as ftmp
)
, CONCAT('dow: ',@dayofweek,' ',@TMP_show_after) )
-- , 1)
,
0
) as clash_offset,
@NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
w.extra
FROM `t_weekday` as w
JOIN (SELECT
@idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
@next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY)
) as varrs
ORDER BY date DESC, weekday, id ASC
Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want.
Edit: I found an error in the logic, I need to return the highest result (date) in a series of nextdiffs if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.
COUNT(*) ... GROUP BY weekday
(instead of thatcase
)?