CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Busturdust
Busturdust

Reputation: 2485

SQLAlchemy - Get all Rows which have matching set of Columns

I have a table

class Term(CommonFunctions, Base):
    __tablename__ = 'terms'

    id = Column(Integer, primary_key=True,autoincrement=True)
    term_begin = Column(Date, nullable=False)
    term_end = Column(Date)
    term_served = Column(Integer)  # term_number # calculatable?
    office_type_id = Column(Integer, ForeignKey(OfficeType.id))
    office_type = relationship('OfficeType', backref='terms')
    state_id = Column(Integer, ForeignKey(State.id))
    state = relationship('State', backref='terms')
    district_id = Column(Integer, ForeignKey(District.id))
    district = relationship('District', backref='terms')
    office_class = Column(Integer)
    # ... other fieldds

I am trying to run a report, to find the ID pairs, of rows that have the same set of data for (state_id,district_id,office_type_id, office_class) for a specific office_type_id within a specific date range.

The query I have right now - (institution = office_type_id)

date = request.args.get('date')
institution = request.args.get('institution')
term_alias = aliased(schema.Term)
composition = Session.query(schema.Term.id, term_alias.id).\
    filter(schema.Term.id != term_alias.id).\
    filter(schema.Term.office_class == term_alias.office_class).\
    filter(schema.Term.state_id == term_alias.state_id).\
    filter(schema.Term.office_type_id == term_alias.office_type_id).\
    filter(schema.Term.office_type_id == institution).\
    filter(schema.Term.office_class != 0).\
    filter(and_(schema.Term.term_begin <= date, or_(schema.Term.term_end >= date,
                                                                          schema.Term.term_end == None))).\
    all()

This works - in a sense. I get back valid results, but it reproduces the result twice, once for each version of the pair.

For Example :

[(127,196), (196,127)]

My question is, How can I update the query, to include only pairs, that are not already represented by a logically equivalent pair.

I would like the above set to be either [(127, 196)] or [(196,127)] not both.

Thanks for reading

Upvotes: 0

Views: 843

Answers (1)

univerio
univerio

Reputation: 20508

A common way is to impose a particular (arbitrary) ordering:

Session.query(...).filter(..., schema.Term.id < term_alias.id)

If you can get back a "reflexive" pair (pair of identical IDs), you need apply a distinct as well.

Session.query(...).filter(..., schema.Term.id < term_alias.id).distinct()

Upvotes: 1

Related Questions