Getting the most recent past datetime

flowte

I want to output the most recent past datetime from a joined table. What way can I do this? What I've got currently is:

    Editor::inst( $db, 'contact' )
        ->field(
            Field::inst( 'contact.id' ),
            Field::inst( 'contact.fname' ),
            Field::inst( 'contact.lname' ),
            Field::inst( 'activity.datetime_end' ),
            Field::inst( 'company_contact.title' ),
            Field::inst( 'company.id' ),
            Field::inst( 'company.name' )
        )
    ->leftJoin('contact_activity', 'contact.id', '=', 'contact_activity.id_contact')
    ->leftJoin('activity', 'contact_activity.id_activity', '=', 'activity.id')
        ->where('activity.datetime_end', date(), "<")
    ->leftJoin('company_contact', 'contact.id', '=', 'company_contact.id_contact')
    ->leftJoin('company', 'company_contact.id_company', '=', 'company.id')
        ->where('company.id_venue', 1)
    ->process($_POST)
    ->json();

tangerine

->where('activity.datetime_end', date(), "<")

Is that the relevant line?

flowte

yes and no, it is my first attempt at trying to get it working but obviously it isn't and wouldn't cover all I'm needing anyway

tangerine

Try something like

->where('activity.datetime_end',MAX(datetime_end), "=")

I think you need MAX(), but I'm not familiar with the Editor's query syntax.

allan

Hi,

What you would need to do is implement a sub-select. The sub-select will get a list of the id's that the main table should select from and then simply use an IN operator.

The PHP manual for Editor details how sub-selects can be used.

Allan

flowte

MAX() seems to cause it to not return any results with datetime_end set

flowte

what I am currently getting is multiple rows for each activity for each contact so if user 1 has 3 activities they are listed 3 times with different datetime_end values. When I include the MAX() code it then doesn't output any rows that have activities associated with them but MAX is what seems to be the most logical way to do it if it is supposed to work

allan

Can you show us the code you are using please. Did you use a sub-select?

Allan

flowte

the above code is the code I'm using. Not sure what you mean by a sub select as I didn't think I could with datatables

allan

Yes - the documentation page I linked to above shows how a sub-select can be used.

The problem with the above code is that it is only going to select a single record - the one with the max value. You want the max value for multiple records depending on some other condition (assuming I understand correctly). I think a sub-select is probably the way to do this.

Allan