Combine two rows into a single one?

Using Django Using the ORM
1

Hello everyone,
I have a Django model with two fields - salary and date (there are some other fields but I’ll omit them for now). Database records look something like this:

id | salary | date

1 | 20.000 | 2021-01
2 | 25.000 | 2021-02
3 | 20.000 | 2021-03
4 | 30.000 | 2021-04
5 | 20.000 | 2021-05
6 | 24.000 | 2021-06
7 | 22.000 | 2021-07
8 | 21.000 | 2021-08
9 | 23.000 | 2021-09
10 | 24.000 | 2021-10
11 | 25.000 | 2021-11
12 | 20.000 | 2021-12
13 | 23.000 | 2022-01
14 | 25.000 | 2022-02
15 | 20.000 | 2022-03
16 | 21.000 | 2022-04
17 | 20.000 | 2022-05
18 | 23.000 | 2022-06
19 | 20.000 | 2022-07
20 | 22.000 | 2022-08
21 | 20.000 | 2022-09
22 | 24.000 | 2022-10
23 | 20.000 | 2022-11
24 | 22.000 | 2022-12
24 | 31.000 | 2023-01
…

Now, my objective is to retrieve this data as a query result:

id | salary | date | prev_year

1 | 20.000 | 2021-01 | 23.000
2 | 25.000 | 2021-02 | 25.000
3 | 20.000 | 2021-03 | 20.000
4 | 30.000 | 2021-04 | 21.000
5 | 20.000 | 2021-05 | 20.000
6 | 24.000 | 2021-06 | 23.000
7 | 22.000 | 2021-07 | 20.000
8 | 21.000 | 2021-08 | 22.000
9 | 23.000 | 2021-09 | 20.000
10 | 24.000 | 2021-10 | 24.000
11 | 25.000 | 2021-11 | 20.000
12 | 20.000 | 2021-12 | 22.000

I will need to filter results by year and I expect to get 12 records each time
(the last column displays values from the previous year same month).
So, I have an additional column, which returns the same field value filtered by another date (same month but previous year).
Is it possible to achieve this with a single Django ORM query?

2

Yes. Depending on exactly what you’re needing to do here, you’re looking for some combination of annotation with a Subquery.

3

Ken, thanks for taking time to answer!

I’ve managed to find out this:

fields_to_compare = ['field1, field2, field3, field4, field5']  

annotations = {f'{field}_pevious_year': Subquery(
                MyModel.objects.filter(
                    date__month=OuterRef('date__month'), 
                    date__year=OuterRef('date__year')-1
                ).order_by('-date').values(field)[:1]
            ) for field in fields_to_compare}

for field in fields_to_compare:
	annotations[f'{field}_diff'] = F(field) - F(f'{field}_pevious_year')

# Query to fetch the data with the previous year's values for all fields
salaries = MyModel.objects.annotate(**annotations).filter(date__year=datetime.date.today().year).order_by('date')

The code is working fine, it gives me the query results with all the fields I wanted, but I don’t know if there is any more efficient way to accomplish this (probably not).

4

I think this “looks” messy because of how you need to create this using the ORM. However, I’d make the guess that if you look at the actual SQL being generated for this - and the corresponding EXPLAIN for that query, I’d guess it’s probably not that bad.

Having said that, and depending upon how many years you expect to be gathering data for, you may find it worthwhile to create an index on those fields.

5

Thanks Ken,
of course, I would be grateful for the simplification of this mess, not because of performance (this table has 150 records so far, and it will have another 120 in ten years), but only because I would like to learn proper usage of Django ORM from an expert like you and others here are.

Having mentioned this, please bear in mind that I don’t expect you to provide me with any additional response.
I’m far away from experienced Django programmer so anyone’s suggestion is more than welcome :slight_smile: