Hello, I was wondering if there was any support for MySQL’s GROUP BY logic. The reason I’m asking is that for some of my data, I need to satisfy the following requirements when pulling data from a table (Let’s call it Table A)
- Only the records in Table A that have a specific value for a string user_id (This is easy, I can use filters. Note that this user_id is part of our data, a regular string, not part of the dream factory user framework)
- Those records must be between newer than a specified creation date (I can use the filters for this too I assume, by referencing my “date_created” column which is of type “timestamp_on_create” and telling it to be >= a datetime)
- I only want the last record from each day to be returned, if there are multiple records from a day.
The last one is where I run into a problem. I can’t see any way to do this currently. I somewhat assume I could somehow use scripts to discard all but the last record from each day, but I can’t get my head around how I could do so.
If I was doing it in raw MySQL syntax, I’d be doing it like this:
SELECT records.* FROM records INNER JOIN (SELECT DATE(`date`) as day_of, MAX(`date`) AS last_record FROM records GROUP BY day_of ) latest ON records.`date` = latest.last_record WHERE records.user_id = '9076224588'
I know that that would work. I just can’t figure out how to translate that to what’s available to me on Dreamfactory. Could I get some help here? If it means I need to go into the system files and somehow add an additional method to the REST Api, I’d be willing to do so, but I don’t know where that code lives.