Get most recent results query doesn't work

[scolby33]

Manager.get_most_recent_result_for_each_location, besides needing a new name, also doesn't work. Here's what I think it should do:

In the end, we need two lists, one of locations (Users) and one of "results." The results list corresponds to the rows of the results table that will be displayed and therefore needs the following information in each entry:

• Authority Name
• OCSP Responder URL
• Status (Result.status) for each location

Perhaps it could be laid out like so:

{
    'Location 1': [
        ('Authority 1', 'URL 1.1', 'status 1.1'),
        ('Authority 1', 'URL 1.2', 'status 1.2'),
        ('Authority 2', 'URL 2.1', 'status 2.1'),
        ...
    ],
    'Location 2': [
        ...
    ]
}

Another option might be:

locations = ['Location 1', 'Location 2', ...]
results = [
    ('Authority 1', 'URL 1.1', 'Location 1 Status', 'Location 2 Status', ...)
    ('Authority 1', 'URL 1.2', 'Location 1 Status', None, 'Location 3 Status')  # just leave blanks for missing data or something
]

This second format has advantages for the eventual creation of a table with Jinja, I think.

I don't know enough SQL to create this query. Help me, @cthoyt Kenobi! You're my only hope.

[scolby33]

@cthoyt closed with 6cdf7ff