Is there a way to use attributes on a relationship attribute when selecting models using where? #261

Activity

1. taranlu-houzz

   added

   questionFurther information is requestedFurther information is requested

   on Mar 4, 2022
2. byrman commented on Mar 5, 2022

   

   byrman

   on Mar 5, 2022

   Contributor

   More actions

   Yes, there is a way:

   db_statement = select(Product).where(Product.source.has(another_attribute = False))
   

   But if you inspect the generated SQL, you'll see that you are probably better off using a join.
3. taranlu-houzz commented on Mar 5, 2022

   

   taranlu-houzz

   on Mar 5, 2022

   Author

   More actions

   @byrman I see, thanks for the help. Just confirming: this is not in the SQLModel documentation currently, right? Is that because it is actually a SQLAlchemy feature?

   I ended up using multiple foreign keys to get the result I wanted (which does appear to create joins). I'm don't really have a lot of db experience, but I ended up using something like this:

   Updated Example

   import logging
   from typing import List
   
   from sqlmodel import (
       Field,
       Relationship,
       SQLModel,
       Session,
       create_engine,
       select,
   )
   
   
   logging.basicConfig(level=logging.INFO)
   
   sqlite_url = "sqlite://"
   
   # engine = create_engine(sqlite_url)
   engine = create_engine(sqlite_url, echo=True)
   
   
   class Product(SQLModel, table=True):
   
       id: str = Field(primary_key=True)
       product_status: int = 0
   
       source_status: str = Field(foreign_key="source.source_status")
       another_attribute: bool = Field(foreign_key="source.another_attribute")
   
       source: "Source" = Relationship(
           back_populates="products",
           sa_relationship_kwargs={
               "primaryjoin": "Product.source_status==Source.source_status",
               "lazy": "joined",
           },
       )
   
   
   class Source(SQLModel, table=True):
   
       id: int = Field(primary_key=True)
       source_status: int = 0
       another_attribute: bool = False
   
       products: List["Product"] = Relationship(
           back_populates="source",
           sa_relationship_kwargs={
               "primaryjoin": "Source.source_status==Product.source_status",
               "lazy": "joined",
           },
       )
   
   
   def create_db_and_tables():
       SQLModel.metadata.create_all(engine)
   
   
   def add_data():
   
       with Session(engine) as session:
           source_1 = Source(id=1, another_attribute=True)
           source_2 = Source(id=2, source_status=2)
   
           product_a = Product(
               id=1,
               source_status=source_1.source_status,
               another_attribute=source_1.another_attribute,
               source=source_1,
           )
           product_b = Product(
               id=2,
               source_status=source_1.source_status,
               another_attribute=source_1.another_attribute,
               source=source_1,
           )
           product_c = Product(
               id=3,
               source_status=source_2.source_status,
               another_attribute=source_2.another_attribute,
               source=source_2,
           )
           product_d = Product(
               id=4,
               source_status=source_2.source_status,
               another_attribute=source_2.another_attribute,
               source=source_2,
           )
           product_e = Product(
               id=5,
               source_status=source_2.source_status,
               another_attribute=source_2.another_attribute,
               source=source_2,
           )
   
           session.add(product_a)
           session.add(product_b)
           session.add(product_c)
           session.add(product_d)
           session.add(product_e)
   
           session.commit()
   
   
   def get_data_good3():
   
       with Session(engine) as session:
           db_statement = select(Product).where(Product.another_attribute == True)
           result = session.exec(db_statement).unique().all()
   
           return result
   
   
   def main():
   
       create_db_and_tables()
   
       add_data()
   
       good_data3 = get_data_good3()
   
       logging.info("GOOD DATA3:")
       for item in good_data3:
           logging.info(f"{' ' * 4}{repr(item)}")
   
   
   if __name__ == "__main__":
   
       main()

   Not really sure if this is a good solution, but it seems to work.
4. byrman commented on Mar 6, 2022

   

   byrman

   on Mar 6, 2022

   Contributor

   More actions

   Is that because it is actually a SQLAlchemy feature?

   I guess so. The SQLAlchemy is extensive, there is no point in copying all that.

   Not really sure if this is a good solution, but it seems to work.

   You may read more abut where / join here: https://sqlmodel.tiangolo.com/tutorial/connect/read-connected-data/
5. taranlu-houzz commented on Mar 7, 2022

   

   taranlu-houzz

   on Mar 7, 2022

   Last edited by taranlu-houzz

   Author

   More actions

   So, I actually ended up using @byrman's suggestion because I was running into other issues with my multiple foreign keys approach. Just to clarify, in case someone sees this in the future, the suggestion to use Product.source.has(another_attribute=False) above is specific to my example. The name source is not special (this confused me for a moment), and your editor may not be able to give a completion for has (in VSCode, I did not get a completion for it).
6. fastapi

   locked and limited conversation to collaborators on Aug 12, 2025
7. YuriiMotov

   converted this issue into a discussion #1492 on Aug 12, 2025