python-sql: make an update with from clause instead of with select into the set clause

[Jesús Martín Jiménez]

Hi,

I've looked for a thread related with this topic but I've not found anything, so if there is any, I would like to apologize.

I've added a constraint in a customized module in order to make stock_lot number unique for each product. Now, I want to add a query in this module in order to update stock moves (and other models) to the first stock_lot introduced in the database when we upgrade version to 3.8, and so, delete repeated stock lots. I've tested this query:

UPDATE
    stock_move AS m
SET
    lot = l.target_lot
FROM
    (
    SELECT
        m.id AS move,
        MIN(ll.id) AS target_lot
    FROM
            stock_move AS m
        LEFT JOIN
            stock_lot AS l
                ON m.lot = l.id
        LEFT JOIN
            product_product AS p
                ON m.product = p.id
        LEFT JOIN
            stock_lot AS ll
                ON (ll.product = p.id AND l.number = ll.number)
    GROUP BY
        m.id,
        l.id
    HAVING
            COUNT(ll.id) > 1
        AND
            l.id != MIN(ll.id)
    ) AS l
WHERE
    m.id = l.move

And it works pretty well because it only modifies rows affected in less than 1 second.

My problem is that I don't be able to build this query with python-sql library and the query I'm able to build:

update = (move
  .update(
    columns=[move.lot],
    values=[
      inner_move
      .join(source_lot, condition=(inner_move.lot == source_lot.id))
      .join(product, condition=(inner_move.product == product.id))
      .join(target_lot, condition=((target_lot.product == product.id) & (source_lot.number == target_lot.number)))
      .select(
        Min(target_lot.id),
        where=(inner_move.id == move.id),
        group_by=(inner_move.id, source_lot.id),
        having=((Count(target_lot.id) > 1) & (source_lot.id != Min(target_lot.id)))
        )
      ]
    ))

UPDATE "stock_move"
SET "lot" = (
    SELECT MIN("e"."id")
    FROM "stock_move" AS "b"
    INNER JOIN "stock_lot" AS "c" ON ("b"."lot" = "c"."id")
    INNER JOIN "product_product" AS "d" ON ("b"."product" = "d"."id")
    INNER JOIN "stock_lot" AS "e" ON (("e"."product" = "d"."id") AND ("c"."number" = "e"."number"))
    WHERE ("b"."id" = "stock_move"."id") AND ("c"."id" != "e"."id")
    GROUP BY "b"."id", "c"."id"
    HAVING ((COUNT("e"."id") > 1)))

Affect all rows and takes almost a minute to be executed.

And my question is, is it possible to build the first query with python-sql?

[Cédric Krier]

On 2015-12-09 07:47, Jesús Martín Jiménez wrote:
> And my question is, is it possible to build the first query with python-sql?

Yes you can set a from clause on update with the from_ keyword but I
don't think it is supported by SQLite.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Jesús Martín Jiménez]

2015-12-09 16:58 GMT+01:00 Cédric Krier <cedric...@b2ck.com>:

On 2015-12-09 07:47, Jesús Martín Jiménez wrote:
> And my question is, is it possible to build the first query with python-sql?

Yes you can set a from clause on update with the from_ keyword

I see. Thank you very much.
 

but I
don't think it is supported by SQLite.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

--
You received this message because you are subscribed to the Google Groups "tryton-dev" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/tryton-dev/20151209155857.GB13365%40tetsuo.Home.

--

Jesús Martín

Zikzakmedia SL

C/ de Sant Jaume, 9, baixos, 2ª

08720 Vilafranca del Penedès

☏ 93 890 21 08