CODEWITHSUNDEEP
mysqldjango
Paul Ayling
Paul Ayling

Reputation: 53

Django , Query to get certain values with a distinct name and the latest date and time

The situation is i have a database full of test results split into different test sets. Each test has a name, result , start time , start date , ... , what currently happens is over the week test sets can be run multiple times , giving multiple test results under a test set

Currently when i want to get the latest result of each test under a test set i am querying for the distinct test names, and then for each distinct name i am querying for that name and ordering by startDate and startTime to get the latest. This is a pain because when i have a test set with over 100 different tests it degrades substantially.

What im trying to do is to perform what i want in one call of django.objects...

Here is the mysql to effectively represent what i want to achieve:

select testName,result,MAX(startDate),MAX(startTime),othertestinfo from testset where testset_id = 'UniqueID' group by testName;

Im having a hard time trying to figure this out in django , if its even possible.

Any help would be much appreciated.

Thanks

Update 23/1/12

Models for what i am using.

class testCase(models.Model):

    id = models.AutoField(primary_key=True)
    testName = models.CharField(max_length=50)
    result = models.CharField(max_length=50)
    precision = models.CharField(max_length=10)
    fileLocation = models.CharField(max_length=150)
    testset_id = models.ForeignKey(testset)
    machine = models.CharField(max_length=15)
    startDate = models.DateField()
    startTime = models.TimeField()

class testset(models.Model):
    testsetID = models.CharField(max_length=100, primary_key=True)
    testsetName = models.CharField(max_length=40)
    platformName = models.CharField(max_length=15)
    osName = models.CharField(max_length=15)
    executionName = models.CharField(max_length=40)
    version = models.CharField(max_length=10)
    software = models.CharField(max_length=20)
    runType = models.CharField(max_length=20)

Upvotes: 1

Views: 223

Answers (1)

Sandip Agarwal
Sandip Agarwal

Reputation: 1910

You can give a try to the following:

t = testset.objects.values('testName').annotate(Max('startDate'),Max('startTime'))

This would give you a list of the objects' values-dictionaries containing key-value pairs of testName, startDate, startTime for the required condition.

You would get the condition fulfilled in this but you can try experimenting with this to get all the columns.

Upvotes: 1

Related Questions