How to do sampling with many-to-many filter? Problems arise with the collection.

Now I do this:

There is such a request:

.filter( User.city == current_user.city, User.propertys.any(Property.name.in_(['Лид', 'Контакт'])), ~User.contacted.any(), User.contacted.any(User.id == current_user.id))

in which either ~User.contacted.any() works, giving users without friends, or User.contacted.any(User.id == current_user.id) , giving users who have a friend — the user who is being sampled, but don't work together. Returns nothing.

One request overlaps another.

I need to get all the users who have a searching user in their contacts, and who have no one in their contacts, while having them in the same city.

 contacts_users = db.Table('contacts_users', db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('contact_id', db.Integer, db.ForeignKey('user.id')) ) class Address(Base): name = db.Column(db.String(50), unique=True) index = db.Column(db.String(50)) city = db.Column(db.ForeignKey('city.id')) class City(Base): name = db.Column(db.String(50)) country = db.Column(db.ForeignKey('country.id')) addresses = db.relationship('Address', backref=db.backref('city')) class User(Base, UserMixin): first_name = db.Column(db.String(255)) last_name = db.Column(db.String(255)) ... adresses = db.relationship('Address', secondary=addresses_users, backref=db.backref('users')) contacts = relationship( 'User', lambda: contacts_users, primaryjoin=lambda: User.id == contacts_users.c.user_id, secondaryjoin=lambda: User.id == contacts_users.c.contact_id, backref='contacted' )

I do not understand how to do it.

Thank you in advance!

    2 answers 2

    I assume that it is necessary to write a condition in one line through OR

     ~User.contacted.any() OR User.contacted.any(User.id == current_user.id))
    • Throws an exception.builtins.TypeError TypeError: Narnik Gamarnik
    • in sqlalchemy, or_ and and_ work and they are written like this: filter (and_ (User.contacted.any (User.id == current_user.id), ~ User.contacted.any ()) - Narnik Gamarnik

    Thanks @santavital for the tip. I slightly modified the request but it does not change the essence. It works like this:

     return ( self.session.query( User ).filter(User.city == current_user.city ).filter(User.propertys.any(Property.name.in_(['Лид', 'Контакт'])) ).filter(or_(User.contacted.any(User.id == current_user.id), ~User.contacted.any())) )