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

Unable to retrieve foreignkey relation object from select statement #547

Open
spaceofmiah opened this issue Apr 23, 2023 · 2 comments
Open

Comments

@spaceofmiah
Copy link

spaceofmiah commented Apr 23, 2023

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
Copy link

Any update on this?

@aawilson
Copy link

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

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

3 participants