Raw query and Datatables in Laravel 4.2

Wednesday, November 4, 2015

I'm using Laravel 4.2. I need to make a somewhat complicated query (cross-databases) with count() which has a between datetime condition and the problem is following: bllim-datatables only work with Eloquent/Query Builder yajra-datatables work with Eloquent and Query Builder and Collection, but Collections only work on Laravel 5.x
The raw mysql query looks like this:



SELECT td.song_id AS 'track_id', tm.track_name, a.artist_id, a.artist_display_name AS 'artist', IFNULL(cp.cnt,0) AS `spinsCurPer`, IFNULL(pp.cnt,0) AS `spinsPrevPer`, DATE_FORMAT(MIN(td.detection_time_local), '%m/%d/%y') AS `firstDetected`, IF(tm.track_status_id=1, 'Yes', 'No') AS `current`, tm.label_group_id, lg.label_group_name, lg.is_independent_label
FROM
traction_general.artist_names AS a,
traction_general.stations AS s,
traction_general.tracks_master AS tm,
traction_general.label_groups AS lg,
traction_monitors.traction_detections AS td
LEFT JOIN (SELECT song_id, COUNT(song_id) AS 'cnt' FROM traction_monitors.traction_detections WHERE detection_time_utc BETWEEN '1445299200' AND '1445903999' GROUP BY song_id) AS cp ON td.song_id = cp.song_id
LEFT JOIN (SELECT song_id, COUNT(song_id) AS 'cnt' FROM traction_monitors.traction_detections WHERE detection_time_utc BETWEEN '1444694400' AND '1445299199' GROUP BY song_id) AS pp ON td.song_id = pp.song_id
WHERE
tm.label_group_id=lg.label_group_id AND
td.song_id = tm.id AND
s.traction_station_id=td.station_id AND
tm.artist_id = a.artist_id AND
(cp.cnt IS NOT NULL OR pp.cnt IS NOT NULL) AND tm.track_name IS NOT NULL AND s.is_monitored=1 AND s.is_active=1
GROUP BY td.song_id ORDER BY spinsCurPer DESC


The problem are those 4 UNIX timestamps after BETWEEN (they could also be regular datetimes). The counts are directly tied to the timestamps, so making view is also not an option. I've tried DB::select(DB::raw(query_here), it doesn't work because the method toQuery() is missing. For all the other DataTables I've used Query Builder, so the output can be manipulated with something like YADCF plugin, which lets me use date range filter, or any other kind of external filtering. My question is, how can I make this work and have an option to filter the records using timestamps as variables?

0 comments:

Post a Comment