Getting the most recent past datetime

Getting the most recent past datetime

flowteflowte Posts: 21Questions: 6Answers: 0
edited October 2015 in DataTables

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();

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

    Is that the relevant line?

  • flowteflowte Posts: 21Questions: 6Answers: 0

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    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.

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • flowteflowte Posts: 21Questions: 6Answers: 0

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

  • flowteflowte Posts: 21Questions: 6Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

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

    Allan

  • flowteflowte Posts: 21Questions: 6Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

This discussion has been closed.