I am trying to get the first object from an ordered many-to-many relationship using the SQLAlchemy flag.
I would like to accomplish this using hybrid properties, so I can use my code in its purest form.
Here is the code, with some comment:
class PrimaryModel2Comparator(Comparator):
def __eq__(self, other):
return self.__clause_element__().model2s.is_first(other)
class model2s_comparator_factory(RelationshipProperty.Comparator):
def is_first(self, other, **kwargs):
return isinstance(other, Model2) & \
(db.session.execute(select([self.prop.table])).first().id == other.id)
model1_model2_association_table = db.Table('model1_model2_association',
db.Column('model1_id', db.Integer, db.ForeignKey('model1s.id')),
db.Column('model2_id', db.Integer, db.ForeignKey('model2s.id')),
)
class Model1(db.Model):
__tablename__ = 'model1s'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
model2s = db.relationship('Model2',
order_by=desc('Model2.weight'),
comparator_factory=model2s_comparator_factory,
secondary=model1_model2_association_table,
backref=db.backref('model1s', lazy='dynamic'),
lazy='dynamic'
)
@hybrid_property
def primary_model2(self):
return self.model2s.order_by('weight desc').limit(1).one()
@primary_model2.comparator
def primary_model2(cls):
return PrimaryModel2Comparator(cls)
class Model2(db.Model):
__tablename__ = 'model2s'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
weight = db.Column(db.Integer, nullable=False, default=0)
And use:
Model1.query.filter(Model1.primary_model2 == Model2.query.get(1))
Problems:
- In my factory comparator is_first method, I cannot get the actual instance, so I don't know which ones are related to Model2s
- In the same method, I want to order my choice against the Model2 weight attribute, and then take the first
Is something unclear in my head, maybe there is a simpler solution?