Unable to retrieve foreignkey relation object from select statement

[spaceofmiah]

I've Product model that has a brand attribute which is a ForeignKey to Brand model. There are data in the database serving for the appropriate relationship i.e i've products in the database that maps to a brand.

issue

On product listing, i only get the product payload but the relation object is not returned sample response

[
  {  
    "id": 1,
    "brand_id": 1,
    "brand": null
   },
    {  
    "id": 2,
    "brand_id": 1,
    "brand": null
   },
]

# models
class Product(Base):
    __tablename__ = 'product'

    id:Mapped[int]   = Column(Integer, primary_key=True) 

    brand_id:Mapped[typing.Optional[int]]   = Column(ForeignKey('brand.id'))
    brand:Mapped[Brand]   = relationship(Brand, back_populates='products')

# database setup
database = databases.Database(URL)

# retrieving product listing
async def list():
    return await database.fetch_all(select(Product).options(selectinload(Product.brand)))

I tried same query using orm.Session and i attained the desired result with even lesser query see below

def list():
    with Session(bind=engine) as session:
         return session.execute(select(Product))

sample response i got from the session query

[
  {  
    "id": 1,
    "brand_id": 1,
    "brand": {...........}
   },
    {  
    "id": 2,
    "brand_id": 1,
    "brand": {............}
   },
]

How do i retrieve foreignkey relation using database call

databases == v0.6.2 sqlalchemy == 1.4.34

[chamara96]

Any update on this?

[aawilson]

I'm also interested in the answer for this, though the fact that it hasn't been forthcoming in over a year makes me think perhaps the capability doesn't exist, yet. I do notice that the databases.backends.postgres.Record return type in the result has enumerated columns in its result_columns property, e.g. single_result._result_columns will have a key client_identifier_1 when the child in the relation has a column client_identifier