Arbitrary GROUP BY fields

[thinkwell]

I'm using 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!

[James Gutu]

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.

--
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...@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.

--

--
Regards,
James Gutu

[thinkwell]

Thanks for your reply James. I wasn't able to get it to work.

I posted on the new Django forum because it's easier to format code examples over there. Hopefully that clarifies things more...

[Integr@te System]

Hi Issuer,

Bc of you use ORDER BY with .date field so it must include in GROUP BY claude.

On Sat, Oct 19, 2019, 09:09 thinkwell <thinkwel...@gmail.com> wrote:

Thanks for your reply James. I wasn't able to get it to work.

I posted on the new Django forum because it's easier to format code examples over there. Hopefully that clarifies things more...

--

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...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9d55c47a-c2a9-481e-82b8-d4302f4bec86%40googlegroups.com.