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

REST API
1

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.

2

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.

3

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.

4

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?

5

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.

6

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.

7

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.