0

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

Hyperion
  • 1
  • 2
  • the question needs sufficient code for a minimal reproducible example: https://stackoverflow.com/help/minimal-reproducible-example – D.L Mar 09 '23 at 22:21
  • @D.L hello, I am not asking for debugging help here. I am asking about what, if any, ways we could insert/update into a DB and get back our results with a column joined in without having to do an extra query to the database to get it. – Hyperion Mar 10 '23 at 16:08
  • [1] the query in the database would be more efficient. [2] yes, you could do the latter, which would be less efficient... it is difficult to help if there is no problem to resolve, so a MRE is useful: https://stackoverflow.com/help/how-to-ask – D.L Mar 12 '23 at 15:53

0 Answers0