0

I have 3 tables, Model, Parameter and Value where parameters point to model and values to parameters.

What I want to do is to filter the parameters of some model by conditions on the parameter and the values and return only those parameters and values that match the criteria. What I tried is something like:

model.params.join(Value).filter(Param.name.in_(<whatever>), Value.type == xx, Value.count < yy)

This returns the expected Param objects, however it returns all the Values of those Param, including ones with, for example, count > yy.

What do I need to change in the query to get the result I want?

EDIT

This is how the relevant part of the tables looks like:

class Model(Base):
    __tablename__ = 'models'

    params = relationship('Param', cascade='all, delete-orphan', lazy='dynamic', back_populates='model')

class Param(Base):
    __tablename__ = 'params'

    model = relationship('Model', back_populates='params')
    values = relationship('Value', cascade='all, delete-orphan', order_by='asc(Value.count)', back_populates='param')

class Value(Base):
    __tablename__ = 'values'

    name  = Column(String(128))
    count = Column(Integer)
    param = relationship('Param', back_populates='values')
mibm
  • 1,328
  • 2
  • 13
  • 23
  • Please provide a [mcve]. How do you access those params and values? Are you using relationships? What do your models look like? – Ilja Everilä Dec 04 '19 at 10:19
  • @IljaEverilä added the table's code. The (incomplete) query I run is in the question. – mibm Dec 04 '19 at 10:50
  • How do you use the results of that query to get at the values? – Ilja Everilä Dec 04 '19 at 11:29
  • @IljaEverilä I loop over the resulting parameters, then for each loop over the values and perform an action. Currently I need to recheck the Value filters before the action which is annoying – mibm Dec 04 '19 at 12:41
  • You could use nested eager loading, such as here: https://stackoverflow.com/questions/47243397/sqlalchemy-joinedload-filter-column. With it the relationships are populated from the query with the filtered data. – Ilja Everilä Dec 04 '19 at 12:52

0 Answers0