How to UPDATE FROM a subquery?

[Bertrand Bordage]

I would like to make the following SQL using peewee:

UPDATE cms_title

SET path = paths.new_path

FROM (

        SELECT […]

        FROM […]

    ) AS paths

WHERE […];

Is there a way to add this FROM subquery using peewee?  I tried using Model.update & UpdateQuery, but all I can add is a WHERE.

PS : Peewee is outstanding, I’m starting to use it in every complex Django situation! :)

[Charles Leifer]

You should be able to pass a select query in when assigning a new value, e.g.

subquery = Path.select(Path.path).where(Path.cms_page == CMSTitle.page)

CMSTitle.update(path=subquery)

I'm not sure if that's exactly what you're looking for. Check out the peewee tests module, specifically `test_update_subquery()` for an example.

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bertrand Bordage]

This is not exactly what I would like to do, because I also use paths.new_path in the WHERE clause.

If I do:

Title.update(path=subquery).where(path != subquery)

It'll execute the subquery two times.  Since my subquery is quite complex, performance could be decrease.

Another issue is that I don’t fetch only new_path in my subquery, but also an ID is use in the WHERE clause.

To be more precise, what I would like to do with peewee is:

UPDATE cms_title

SET path = paths.new_path,

    has_url_overwrite = TRUE

FROM (

    SELECT CONCAT(%s, '/', (CASE WHEN current_page.publisher_is_draft IS TRUE

                                 THEN current_page.id

                                 ELSE draft_page.id END)) AS new_path,

           current_title.id AS current_title_id

    FROM cms_title AS current_title

    INNER JOIN cms_page as current_page

    ON current_title.language = %s

       AND current_page.id = current_title.page_id

       AND current_page.level > 1

       AND current_page.tree_id = %s

       AND current_page.lft >= %s

       AND current_page.lft <= %s

    INNER JOIN cms_page as draft_page

    ON draft_page.publisher_public_id = current_page.id

    ) AS paths

WHERE paths.current_title_id = cms_title.id

      AND cms_title.path != paths.new_path;

(These tables are from the Django CMS models Title and Page. %s are values from an already selected Title and its Page.  What this query does is updating all descendant pages URLs from a level-1 page so that their URL is /[level-1-page-slug]/[page.id].)