kayak / pypika

PyPika is a python SQL query builder that exposes the full richness of the SQL language using a syntax that reflects the resulting query. PyPika excels at all sorts of SQL queries but is especially useful for data analysis.
http://pypika.readthedocs.io/en/latest/
Apache License 2.0
2.43k stars 293 forks source link

PostgreSQLQuery: Can't use WITH with DELETE USING #756

Open wont-work opened 9 months ago

wont-work commented 9 months ago

simplified reproduction

from pypika import *

# setup
t1 = Table('t1')
q1 = Query.from_(t1).select(t1.id)
a = AliasedQuery('test')

# broken query
broken_q = PostgreSQLQuery.with_(q1, 'test').delete().using(a).where(t1.id == a.id)
>>> str(broken_q)
'DELETE USING test WHERE "t1"."id"="test"."id"'

# working query
ok_q = PostgreSQLQuery.with_(q1, 'test').select(a.id).from_(a).where(t1.id == a.id)
>>> str(ok_q)
'WITH test AS (SELECT "id" FROM "t1") SELECT "test"."id" FROM test WHERE "t1"."id"="test"."id"'

"real life" use case

(just in case i'm using it wrong)

from pypika import *
table = Table("tags")
implications_table = Table("tag_implications")
id_q = Query.from_(table).select(table.id)
tag_q = id_q.where(table.name == Parameter("$1"))
tag_a = AliasedQuery("tag")
implied_q = id_q.where(table.name == Parameter("$2"))
implied_a = AliasedQuery("implied_tag")
q = (
    PostgreSQLQuery.from_(implications_table)
    .delete()
    .using(tag_a)
    .using(implied_a)
    .with_(tag_q, "tag")
    .with_(implied_q, "implied_tag")
    .where(table.tag_id == tag_a.id)
    .where(table.implied_tag_id == implied_a.id)
)
>>> str(q)
'DELETE FROM "tag_implications" USING tag,implied_tag WHERE "tags"."tag_id"="tag"."id" AND "tags"."implied_tag_id"="implied_tag"."id"'

expected output (hand written sql i'm converting to pika):

WITH tag AS (SELECT id FROM tags WHERE name=$1),
     implied_tag AS (SELECT id FROM tags WHERE name=$2)
DELETE FROM tag_implications USING tag, implied_tag
WHERE tag_id=tag.id AND implied_tag_id=implied_tag.id;