Arbitrary GROUP BY fields

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Arbitrary GROUP BY fields

thinkwelldesigns
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!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/414d3e70-418c-41e9-becb-65209ed0b7eb%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Arbitrary GROUP BY fields

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.

On Fri, Oct 18, 2019 at 12:00 AM thinkwell <[hidden email]> wrote:
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!

--
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 [hidden email].
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

--
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 [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: Arbitrary GROUP BY fields

thinkwelldesigns
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9d55c47a-c2a9-481e-82b8-d4302f4bec86%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: Arbitrary GROUP BY fields

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 <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9d55c47a-c2a9-481e-82b8-d4302f4bec86%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAP5HUWou9MO-sibuntv-O%2BtM2SD%2BDpWhn0OQboF%3Dupf_z5PR3Q%40mail.gmail.com.