Let's say we want to query our Polls app for all Questions that have popular (more than 5 votes) choices that contain Python in the choice_text.
We could query our database as follows:
SELECT * FROM question JOIN (SELECT * FROM choice WHERE vote_count > 5 AND choice_text LIKE '%Python%') choice ON question.id = choice.question_id;
Or, it can be codified in a potentially more comprehendable, OOP/ORM format with SQLAlchemy.
First, we set up our schema by declaring the relationship and the parameters of the join:
class Question(Base): __tablename__ = 'question' id = Column(Integer, primary_key=True) popular_choices = relationship('Choice', primaryjoin='and_(Question.id == Choice.question_id, Choice.vote_count > 5')
Next, we query with SQLAlchemy's ORM:
popular_python_choices = session.query(Question) \ .filter(Question.popular_choices.contains('Python') \ .all()
Not quite as terse as straight SQL but hopefully it is clear how, after the schema is set up, calling
Question.popular_choices.contains('Python') provides increased readability (reads like English) and reusability (replace our filter Python with whatever you wish!).
SQLAlchemy provides another good example.