jerrywright
2 months ago
220
2
Laravel

Query builder with join on find_in_set

Posted 2 months ago by jerrywright

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 sign in or create an account to participate in this conversation.