Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
ShittyKopper opened this issue Sep 30, 2023 · 0 comments
Open

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

ShittyKopper opened this issue Sep 30, 2023 · 0 comments

Comments

@ShittyKopper
Copy link

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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant