django: group by extracting month and year from a date

im trying to get a list of invoicepositions where there hasn’t been payed an payout to a person.

Looking in the internet i came to this “Solution”

src_invoice_month_year = InvoicePosition.objects.values_list("designer_id", ExtractYear('created_at'),ExtractMonth('created_at'))
.filter(payoutposition__isnull=True, designer_id=designer.id).distinct()

but the query, that comes out is:

SELECT DISTINCT `accounting_invoiceposition`.`designer_id`, EXTRACT(YEAR FROM `accounting_invoiceposition`.`created_at`) AS `extractyear1`,
                EXTRACT(MONTH FROM `accounting_invoiceposition`.`created_at`) AS `extractmonth2`, `accounting_invoiceposition`.`created_at`, `accounting_invoiceposition`.`id`
FROM `accounting_invoiceposition` LEFT OUTER JOIN `partners_payoutposition` ON (`accounting_invoiceposition`.`id` = `partners_payoutposition`.`invoiceposition_id`)
WHERE (`accounting_invoiceposition`.`designer_id` = 3 AND `partners_payoutposition`.`id` IS NULL)
ORDER BY `accounting_invoiceposition`.`created_at` DESC, `accounting_invoiceposition`.`id` DESC

so he added “created_at” and the id from the invoiceposition model although i don’t want that!

for example i have the following 4 lines in the db:

date                id
2011-12-13 13:57:43 60300
2011-12-13 13:57:43 60288
2011-11-07 13:14:29 55135
2011-11-07 13:14:29 55134

then i just want

2011 12
2011 11

(i’m running the query with designer_id 3)

Answer

It’s hard guessing what you want with such little information. But based on the input and output you provided, the following should do the trick.

src_invoice_month_year = (
    InvoicePosition.objects.filter(payoutposition__isnull=True, designer_id=designer.id)
    .values_list(ExtractYear("created_at"), ExtractMonth("created_at"))
    .distinct()
)

Filter first for objects, then select distinct month and year of it.