Drizzle Team•5d ago

How do I use the with statement when inserting with select?

How can I get this insert built with drizzle? Insert only has "values", and I am not sure how to construct this in drizzle

WITH "add_favorite_cte" AS (
    SELECT
        "id" as favorite_list_id,
        'my product id' as product_id
    FROM
        "favorite_lists"
    WHERE
        "favorite_lists"."user_id" = 'my user id'
)
INSERT INTO favorite_list_item (id, createdAt, favorite_list_id, product_id)
SELECT
    (SELECT MAX(id) + 1 FROM favorite_list_item), -- I the $default here preferably
    (SELECT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')), -- I the $default here preferably
    "favorite_list_id",
    "product_id"
FROM
    "add_favorite_cte"

WITH "add_favorite_cte" AS (
    SELECT
        "id" as favorite_list_id,
        'my product id' as product_id
    FROM
        "favorite_lists"
    WHERE
        "favorite_lists"."user_id" = 'my user id'
)
INSERT INTO favorite_list_item (id, createdAt, favorite_list_id, product_id)
SELECT
    (SELECT MAX(id) + 1 FROM favorite_list_item), -- I the $default here preferably
    (SELECT (strftime('%Y-%m-%dT%H:%M:%f', 'now') || 'Z')), -- I the $default here preferably
    "favorite_list_id",
    "product_id"
FROM
    "add_favorite_cte"

1 Reply

Mario564•this hour

Right now, Drizzle doesn't support insert into ... select syntax, but there is a PR for it on the Github repo

Gaming

Programming

How do I use the with statement when inserting with select?