I am thinking along the lines of this:
page_titles =
PageTitles.object.select_related('category','rating').values('id',
'category_id', 'rating_id').order_by('date','id').annotate(
allow=Sum('allow'),
block=Sum('block'),
hits=Sum('hits'))
To get it right, you may have to share your models. Hope this helps. Let me
know how it goes.
On Fri, Oct 18, 2019 at 12:00 AM thinkwell <thinkwelldesi...@gmail.com>
wrote:
> I'm using pg-partitioning
> <https://github.com/chaitin/django-pg-partitioning> to partition tables
> on Django 2.2 & Postgres 11. It's working great as far as that goes, except
> that I bumped hard into annotated queries. Since PG can't guarantee PK
> uniqueness across partitioned tables, it requires including all fields in
> the GROUP BY that will in the output.
>
> Django generates this query:
>
> SELECT
>
> "page_titles"."id",
>
> "page_titles"."date",
>
> "page_titles"."name",
>
> "page_titles"."title",
>
> SUM("page_titles"."allow") AS "allow",
>
> SUM("page_titles"."block") AS "block",
>
> SUM("page_titles"."hit_count") AS "hits",
>
> "categories_category"."name" AS "category_name",
>
> "categories_rating"."name" AS "rating_name"
>
> FROM
>
> "page_titles"
>
> LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" =
> "categories_category"."id")
>
> LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" =
> "categories_rating"."id")
>
> GROUP BY
>
> "page_titles"."id",
>
> "categories_category"."name",
>
> "categories_rating"."name"
>
> ORDER BY
>
> "page_titles"."date" DESC,
>
> "page_titles"."id" DESC;
>
> That query raises an error:
>
> ERROR: column "page_titles.date" must appear in the GROUP BY clause or
> be used in an aggregate function
>
> LINE 3: "page_titles"."date",
>
> ^
>
> So if we add all the (non-summed) fields in the GROUP BY that the query
> will return, as the query below, then the query runs successfully.
>
>
> SELECT
>
> "page_titles"."id",
>
> "page_titles"."date",
>
> "page_titles"."name",
>
> SUM("page_titles"."allow") AS "allow",
>
> SUM("page_titles"."block") AS "block",
>
> SUM("page_titles"."hit_count") AS "hits",
>
> "categories_category"."name" AS "category_name",
>
> "categories_rating"."name" AS "rating_name"
>
> FROM
>
> "page_titles"
>
> LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" =
> "categories_category"."id")
>
> LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" =
> "categories_rating"."id")
>
> GROUP BY
>
> "page_titles"."id",
>
> "page_titles"."date",
>
> "page_titles"."name",
> "page_titles"."title",
> "categories_category"."name",
>
> "categories_rating"."name"
>
> ORDER BY
>
> "page_titles"."date" DESC,
>
> "page_titles"."id" DESC;
>
>
> I really can't be using raw SQL for these queries because of dynamic WHERE
> clauses. Is there a way to use / extend the ORM to add arbitrary GROUP BY
> fields?
>
> Or any other suggestion about how to use the ORM to generate queries on
> partitioned tables where Postgres doesn't have a global index...?
>
> TIA!
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/414d3e70-418c-41e9-becb-65209ed0b7eb%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/414d3e70-418c-41e9-becb-65209ed0b7eb%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
--
Regards,
James Gutu
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAOnWGOJD%2B-Mb8PwPgYwyY8odkfU%2B%2BW1CPXZiEBNTk-nQj%3Dgtew%40mail.gmail.com.
