Hyperion
Hyperion

Reputation: 1

Is it possible to issue an update/insert statement RETURNING joined in columns in sqlalchemy/gino?

I have a Users and a Tags table. The column that I want to replace after updating the Tags table is Tags.created_by_user_id with Users.hash_id left joined in.

class Tag(BaseClass):
    __tablename__ = "tags"
    ...
    created_by_user_id: Optional["UserId"] = db.Column(
        db.BigInteger,
        db.ForeignKey("users.id"),
        index=True,
    )

class User(BaseClass):
    __tablename__ = "users"
    ...
    id: Optional[int] = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    hash_id: Optional[str] = db.Column(
        db.Text,
        index=True,
        server_default=FetchedValue(),
        nullable=False,
    )

My current solution: After I update my tag, I query for my tag using the returned column from my update statement.

tag_int: int = (
            await ORMTag.update.values(**t)
            .where(
                and_(
                    ORMTag.hash_id == id,
                )
            )
            .returning(Tag.id)
            .gino.load(ColumnLoader(Tag.id))
            .first()
        )
tag: Tag = await db.select(
        [
            Tag.id,
            ...
            User.hash_id.label("created_by_user_id"),
        ]
    ).select_from(
        Tag.join(User, Tag.created_by_user_id == User.id, isouter=True)
    ).gino.first()

Is it possible to do this without adding an extra query to the database?

I have looked into with_expressions and query_expressions. Looked into Gino subquery loading

Upvotes: 0

Views: 253

Answers (0)

Related Questions