Level 104
Does MySQL support full joins now; can't remember the last time I needed one, but MySQL didn't do it at the time?
Apr 14, 2016
6
Level 1
How to do full join?
SELECT
dl.date,
operators.id,
IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(oop.end_time, oop.login_time)))), '00:00:00')
FROM date_list AS dl
JOIN operators <----------------------------------THIS LINE --------------------------
LEFT JOIN operator_online_period AS oop ON date(oop.login_time) = dl.date AND oop.operator_id = operators.id
WHERE dl.date BETWEEN '2016-04-01' AND '2016-04-15'
AND operators.id IN (1, 2, 17)
GROUP BY dl.date, operators.id
ORDER BY dl.date, operators.id
How can I made this? According to "compileJoins" function in Illuminate\Database\Query\Grammars\Grammar.php line 149, this can't be done. This function think what at least one of clause must exist.
I can make patch & commit. Or where is a way to do it clean(without DB::select)?
Level 1
HM, this sql worked well) .
SELECT * FROM a JOIN b JOIN c ON c.aid = a.id Will get all from a join with b, you get all variants from a-b and for every a join all from c
Level 1
This SQL get dates between 2 dates, join all operators, and we get list for each day list of all operators and join to this information about session each session times, so at result we get online time for each operator for each day in between 2 dates.
date_list it't just list of dates(1 column table), cause in mysql you can't generate list of rows from query
Level 1
Anyone ?
Level 1
This is a simple way to trick the QueryBuilder:
DateList::join('operators', DB::raw(1), '=', DB::raw(1))->get();
EXPLANATION: 1=1 is always true, ergo MySQL will build full join between the rows in "Date_lists" and the rows in "Operators".
Level 1
There is an argument called "type" in the join method. By default it is used "inner" then you can type "full" and use it to achieve it.
->join('tableA tA', function ($join) { $join->on('field1', '=', 'field2'); }, null, null, 'FULL')
Please or to participate in this conversation.