Level 9
Not tested, but I think you can use $join->whereRaw (in your Closure) and pass your FIND_IN_SET there.
1 like
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Could someone explain how to build a query with a join using a function? The query I'm trying to replicate is:
SELECT DISTINCT
f.id
FROM
spDiary AS d
INNER JOIN
spFacilities AS f ON FIND_IN_SET(f.id, d.facilityid)
WHERE
d.schoolid = 7
&& (d.timeslot_start < '2020-07-01 20:00:00'
&& d.timeslot_end >= '2020-07-01 19:00:00')
using the following...
$facilities = DB::table('spDiary')
->select('spFacilities.id')
->join('spFacilities', function ($join) {
$join->on(DB::raw('FIND_IN_SET(spFacilities.id, spDiary.facilityid)'));
})
->where('spDiary.schoolid', 7)
->where(function ($query) use ($start, $end) {
$query->where('spDiary.timeslot_start', '<', $end)
->where('spDiary.timeslot_end', '>=', $start);
})
->distinct()
->get();
but the join clause produced is...
inner join `spFacilities` on FIND_IN_SET(spFacilities.id, spDiary.facilityid) = ``
which is obviously an issue. How can I prevent it adding the = '' at the end?
Many thanks.
Please or to participate in this conversation.