Improve documentation

[dimagi]

Improve documentation

[millerdev]

• With(...) constructs a common table expression to be placed in a SQL WITH clause.
• .join(...) adds a JOIN expression to the SQL FROM clause, joining the CTE query to some other queryset or model.
• .with_cte(...) adds a CTE constructed with With(...) to a WITH clause on the queryset.

Anatomy of the resulting SQL, slightly simplified for purposes of explanation.

WITH RECURSIVE "cte" AS (  -- WITH clause added by .with_cte(...)

    -- CTE query constructed in With(...)
    SELECT
        "tests_order"."region_id",
        SUM("tests_order"."amount") AS "total"
    FROM "tests_order"
        INNER JOIN "tests_region" ON (
            "tests_order"."region_id" = "tests_region"."name"
        )
    WHERE "tests_region"."parent_id" = 'sun'
    GROUP BY "tests_order"."region_id"
)
SELECT
    "tests_order"."id",
    "tests_order"."region_id",
    "tests_order"."amount",
    "cte"."total" AS "region_total"
FROM "tests_order"
INNER JOIN "cte" ON "tests_order"."region_id" = ("cte"."region_id")  -- JOIN clause added by .join(...)
ORDER BY "tests_order"."amount" ASC

The above query (except for comments and basic formatting) was produced by adding print(orders.query) to TestCTE.test_simple_cte_query and then running nosetests tests/test_cte.py:TestCTE.test_simple_cte_query --nocapture

Does that make things more clear?

[bernd-wechner]

Thanks, I worked that all out since and field a PR to simplify it a little by removing the need for .with_cte().. And I'll PR a README improvement some time too if you're content with the default CTE PR.

Another minor hassle I found the hard way along the way using this (otherwise brilliant addition to Django landscape - so brilliant it should be absorbed into Django as it solves a huge problem with Django namely querying on an encapsulated queryset) is that the result of the .join() includes only the fields from its argument and not the fields from the CTE, which need explicitly to be added again with an annotation as and when needed. That took me a long time to work out, and SQL dumps.

I might try and tidy that up a bit too. We shall see.

[millerdev]

New documentation was added in #54. Please file new issues if there are gaps.