How do you unit test Django RawQuerySets

Question

I'm using raw() and annotate() on a Django query and need some way to test it.

Here's what my code looks like (this is simplified significantly)

query = """SELECT
           table1.id, table1.column1, table1.column2,
           table2.other_column1, table2.other_column2 
           FROM myapp_mymodel as table1 
           JOIN otherapp_othermodel as table2 ON table1.othermodel_id = table2.id"""

return MyModel.objects.annotate(
    other_column1=models.Value('other_column1', models.IntegerField()),
    other_column2=models.Value('other_column2', models.DateField())
).raw(query)

It's relatively straightforward to fill the database with sample data, but what's the best way to check that the data is returned by this code?

There are a lot of options when dealing with standard querysets that seem to go out the window when dealing with RawQuerySets.

Answer 1

Usually the approach is to have the test set up a relatively small data set that contains some things the query should find, and some things it shouldn't find. Then inspect the returned QuerySet and verify:

1. That it contains the expected number of results
2. That the set of primary key values returned matches what you expected to return
3. That the annotated values on the returned objects are what they're expected to be.

So, for example, you might do something like:

def test_custom_query(self):
    # Put whatever code you need here to insert the test data
    results = run_your_query_here()
    self.assertEqual(results.count(), expected_number_of_results)
    self.assertEqual({obj.pk for obj in results}, set_of_expected_primary_keys)
    self.assertEqual(
        [obj.annotated_value for obj in results],
        list_of_expected_annotated_values
    )