0

In sqlalchemy I defined a model for my database, in this case two tables "sample" and "experiment", which are linked over a many-to-many relationship to each other:

class Sample(Base):
    __tablename__ = 'sample'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    date = Column(String)

class Experiment(Base):
    __tablename__ = 'experiment'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)
    result = Column(String)

    samples = relationship('Sample', secondary='sample_has_experiment', backref="experiments")

t_sample_has_experiment = Table(
    'sample_has_experiment', metadata,
    Column('sample_id', ForeignKey('sample.id'), primary_key=True, nullable=False, index=True),
    Column('experiment_id', ForeignKey('experiment.id'), primary_key=True, nullable=False, index=True)
)

In my database, I have a sample "Dilithium", and in experiments two experiments "Cold fusion" and "Worm hole".

I try to query the "Worm hole" experiment over a join to the sample table:

samples = s.query(Obj.Sample).join(Obj.Sample.experiments).\
    filter(Obj.Experiment.name == "Worm hole").all()


for sample in samples:
    for experiment in sample.experiments:
        print(experiment.name)

But as a result I still get both experiments "Worm hole" AND "Cold Fusion". So it looks like the filtering is not applied. How can I filter that way that I only receive the "Worm Hole" Experiment object? Thank you.

@Dublicate: Indeed it looks like the same question, but the given answer does not solve my problem. In my opinion, my suggested query does exactly the same like in the proposed answer.

ju.
  • 304
  • 3
  • 16
  • What is `i` in `for experiment in i.experiments`? I guess it should be `sample`… – Błotosmętek Sep 13 '17 at 14:01
  • Thanks, you're right, was just a typo from creating this example. – ju. Sep 13 '17 at 14:05
  • Possible duplicate of [SQLAlchemy how to filter by children in many to many](https://stackoverflow.com/questions/13517141/sqlalchemy-how-to-filter-by-children-in-many-to-many) – Ivan Choo Sep 13 '17 at 14:25
  • Actually, the suggested answer is exactly what I'm using for my query, but in my case the result is somehow not filtered. – ju. Sep 13 '17 at 14:37

2 Answers2

1

What your code says is:

  • For all samples that were part of the wormhole experiment

    • Print all experiments that sample is part of

That is, given a particular sample, sample.experiments is always all the experiments that sample belongs to not just the experiment you got to that sample through.

You can see this if you go add a new sample that does not belong to the wormhole experiment. It should not appear in your query.

So, my answer to "why isn't my query filtering on the join," is that I strongly suspect it is.

If you want the sample objects that were in the wormhole experiment then something like

samples = session.query(Experiments).filter_by(name = 'wormhole').one().samples
Sam Hartman
  • 6,210
  • 3
  • 23
  • 40
  • Thank you, I can see the problem now. Actually, I'd like to go the other way around. I have a specific sample and I want to see/filter the experiments made for this sample. The problem with your suggetion is, if I first select the experiment and then see the samples, I will still have to filter the result, as one experiment can have multiple samples assigned. – ju. Sep 13 '17 at 15:13
  • OK. I think I've answered the question you asked. Even given your comment, it's not clear enough what you're hoping to do that I'd attempt to answer it if you asked that question. – Sam Hartman Sep 13 '17 at 15:45
0

Edit: Actually I realized what my mistake in the first place was. I have to query all tables and then apply joins and filters:

qry = s.query(Obj.Sample, Obj.Experiment).\ # Query both tables!
    filter(Obj.Sample.name == "...").\
    filter(Obj.Experiment.name == "...").\
    join(Obj.Experiment, Obj.Sample.experiments).all()

Alternative way: The use of a subquery leads to the desired behaviour:

wormhole_exp = s.query(Obj.Experiment).filter(Obj.Experiment.name == "wormhole").subquery()

print(s.query(Obj.Sample, wormhole_exp).join(wormhole_exp, Obj.Sample.experiments).one())
ju.
  • 304
  • 3
  • 16