0

I'm not sure if this can be done in a single query or not. I have a model that looks like this:

class TestStatus(models.Model):
    id = models.AutoField(primary_key=True)
    build = models.CharField(max_length=20)
    test_name = models.CharField(max_length=50)
    test_status = models.CharField(max_length=10)
    test_start = models.DateTimeField(auto_now=True)
    test_end = models.DateTimeField(null=True)

The table is designed to store the result of automated testing runs against a given build number. What I am trying to do is query for all of the tests and their statuses run against a given build, but in some cases the same test is run more than once. This query returns all of the tests for the given build:

TestStatus.objects.filter(build__exact='1.2.3.4')

But if the same test was run more than once then I get a result like this:

-------------------------------------------------
| test_name | test_status | test_start          |
-------------------------------------------------
| Test1     | FAIL        | 2015-10-28 14:20:56 |
| Test2     | PASS        | 2015-10-28 14:23:14 |
| Test1     | PASS        | 2015-10-28 15:21:24 |
-------------------------------------------------

What I want to return is only the latest test_status for each test_name run against a given build. I know that I can do a MAX() on the test_start field but that only returns a single entry because it is looking at the maximum date value for all of the fields in the queryset. I could do a query, then if there are any failed tests do another query to see if there is a newer test entry for the given test_name but I'd rather avoid having to do multiple queries if I can.

I'm looking to get output like this with a single query:

-------------------------------------------------
| test_name | test_status | test_start          |
-------------------------------------------------
| Test2     | PASS        | 2015-10-28 14:23:14 |
| Test1     | PASS        | 2015-10-28 15:21:24 |
-------------------------------------------------

Where I'm only getting the newest test run against the given build. Any ideas?

EDIT

Here are some additional things I've tried (the first one is suggested by another answer): TestStatus.objects.annotate(max_date=Max('test_start')).filter(test_start=F('max_date'), build='1.2.3.4')

But that returns the same results as just doing TestStatus.objects.filter(build='1.2.3.4')

I've also been playing with some SQL posted in other answers but this query (which would seem to solve my problem) returns nothing when I try to filter by build:

SELECT a.*
FROM test_status a
INNER JOIN(
    SELECT test_name, MAX(test_start) AS max_date
    FROM test_status
    GROUP BY test_name) b
ON a.test_name = b.test_name
AND a.test_start = b.max_date
WHERE build = '1.2.3.4';
stimko68
  • 67
  • 2
  • 7
  • You mean: TestStatus.object.filter(build__exact='1.2.3.4').latest('test_start') – Jaakko Oct 29 '15 at 17:14
  • That only returns the newest record in the given query (i.e., only a single record), but I want to see the newest for all records in the queryset. – stimko68 Oct 29 '15 at 17:15
  • just FYI, you don't need to define the id as autofield... [django does that for you](https://docs.djangoproject.com/en/1.8/topics/db/models/#automatic-primary-key-fields). – eykanal Oct 29 '15 at 17:27
  • @stimko68 Check the [`latest`](https://docs.djangoproject.com/en/1.9/ref/models/querysets/#latest) QuerySet operator. – narendra-choudhary Oct 29 '15 at 17:58

1 Answers1

-1

try:

tests = TestStatus.objects.filter(build__iexact='1.2.3.4',test_status=True).order_by('test_name','-test_start').distinct('test_name')

Update: just saw the mysql tag, distinct([*fields]) only works on PostgresSQL

Anush Devendra
  • 5,285
  • 1
  • 32
  • 24