I have two tables component
and component_transform
class Component(BaseModel):
__tablename__ = "component"
component_id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
component_serial_number: Mapped[str] = mapped_column(String(250), unique=True)
component_transform: Mapped[List["ComponentTransform"]] = relationship(
"ComponentTransform", back_populates="component"
)
class ComponentTransform(BaseModel):
__tablename__ = "component_transform"
transform_id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
component_id: Mapped[Optional[int]] = mapped_column(
ForeignKey("component.component_id")
)
component_serial_number: Mapped[Optional[str]] = mapped_column(String(250))
component: Mapped["Component"] = relationship(
"Component", back_populates="component_transform"
)
component has a one to many relation to component_transform. I have a function that needs to search for the serial_number. Now I want to be able to search the serial_number in either component or component_transform. Regardless of the matches I just want to return the one record being held in the component table.
I have the function below but it keeps returning multiple records. For example if have the serial_number “A” in my component table and “B”, “C”, “D” in the component_transform table. If I search for any of these I should only get the record back for A. Ofcourse they all share the component_id.
async def search_components(
session: AsyncSession,
component_serial_number: Optional[str] = None,
component_name: Optional[str] = None,
component_status: Optional[list[str]] = None,
limit: int = 20,
offset: int = 0,
) -> Sequence[ComponentSearchModel]:
async with session:
subquery_service_hrs = (
select(
DBMotorComponent.component_id,
func.sum(DBMotorComponent.drilling_hrs).label("service_hrs"),
func.sum(DBMotorComponent.drilling_hrs).label("life_hrs"),
).group_by(DBMotorComponent.component_id)
).subquery()
statement = (
select(DBComponent)
.options(joinedload(DBComponent.part))
.outerjoin(
subquery_service_hrs,
DBComponent.component_id == subquery_service_hrs.c.component_id,
)
.outerjoin(
DBComponentTransform,
DBComponent.component_id == DBComponentTransform.component_id,
)
.add_columns(
subquery_service_hrs.c.service_hrs, subquery_service_hrs.c.life_hrs
)
)
if component_serial_number is not None:
statement = statement.where(
or_(
DBComponent.component_serial_number.ilike(
f"%{component_serial_number}%"
),
DBComponentTransform.component_serial_number.ilike(
f"%{component_serial_number}%"
),
)
)
if component_name is not None:
statement = statement.where(
DBComponent.component_name.ilike(f"%{component_name}%")
)
if component_status is not None:
statement = statement.where(
DBComponent.component_status.in_(component_status)
)
statement = statement.limit(limit).offset(offset)
result = await session.execute(statement)
components = result.fetchall()
component_model = [
ComponentSearchModel.model_validate(
{
**component.__dict__,
"service_hrs": service_hrs or 0,
"life_hrs": life_hrs or 0,
}
)
for component, service_hrs, life_hrs in components
]
return component_model
I have tried doing a distinct but I am getting an error of ORA-00932: inconsistent datatypes: expected - got CLOB
I have also tried to do a group_by on the DBComponent but getting a “Not a group by expression” error.