Using INNER JOIN and GROUP BY to help filter a Rest Get request

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.

If you only want the last one of a particular day, it seems to me that you could use the limit and order by clauses to accomplish that, i.e. adding additional SQL like “LIMIT 1 ORDER BY date DESC” or in the DF API syntax, it would be “&limit=1&order=date%20DESC”.

If I am not over-simplifying the issue, that should work. Please let me know and we can go from there.

That would work for getting the last record from one day, but I’m looking to get the last record from each and every day.

Is that date field actually a datetime field?
Also , support for stored procedures is to be released in the next few days, this seems like a good use case, or we could use a server side script in the interim if you’re open to that?

It’s a timestamp field according to the database. I’ve got that filter working. Stored procedures sounds interesting, but I would also be interested in how I could do it with the script.

In your MySQL you can create a VIEW (with your Select) as follow:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Then, your view is access like any simple table with a Where parameter.

Regards
Polgarmx.

That’s actually what I ended up deciding to do today, I’d forgotten about it to be honest. I’d still be curious how it could be done with the script though.