How to sort all the available users for booking in Flask?

Question

I've created a small booking application where the client can book for a service, i've also made a form search where the client can search in a specific date to book.

The results should be all the available users except if one of them is busy in the given date by the client.

here is my models.py:

class User(db.Model, UserMixin):

    __tablename__ = 'user'

    id = db.Column(db.Integer(), primary_key=True)
    public_id = db.Column(db.String(50), default=uuid.uuid4)
    name = db.Column(db.String())
    appointments = db.relationship('Appointment', backref='user', passive_deletes=True, lazy='dynamic')

class Appointment(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    public_id = db.Column(db.String(50), default=uuid.uuid4)
    start_time = db.Column(db.DateTime(), default=datetime.utcnow)
    end_time = db.Column(db.DateTime(), default=datetime.utcnow)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id', ondelete='CASCADE'))

Here is my code:

from datetime import timedelta
from dateutil.parser import parse

date_time = parse(request.form.get('datetime'))
end_time = date_time + timedelta(minutes=20)
overlapping = db.session.query(Appointment).filter(
    not_(
        or_(Appointment.start_time > end_time,
            Appointment.end_time < date_time)))
overlapping.count() # if overlapping returns > 0 so the client can't book

# Here i am joining the two tables so i can get the available users on that date
results = User.query.join(Appointment, Appointment.user_id == User.id).filter(
            Appointment.start_time > end_time).filter(
            Appointment.end_time < date_time).all()

In the results i get nothing .

Please any suggestions guys !!!

What is the variable `date`? And start_time should be greater than end_time? I don't really understand that part. — Jonathan, Oct 22 '17 at 16:18
I've updated the question , in the first snippet i am trying to see if the chosen time is between any datetimes in the database if yes then the date that the user choosed should be busy. — swordfish, Oct 22 '17 at 16:22
In the second snippet of code i am trying to get all the other users except the busy one . — swordfish, Oct 22 '17 at 16:23

score 0 · Accepted Answer · answered Oct 22 '17 at 17:18

0

So what I think the issue is here is that there may be a case where all users are free. Ie no user has an appointment, and since you are matching on the FK you will only get results that match up with that very foreign key. Hence there are no results. You are doing an INNER JOIN.

Instead of of showing no results you still want to show results for the users. So instead of doing an inner join you'd probably want to do LEFT OUTER JOIN.

results = (User.query.join(Appointment, Appointment.user_id == User.id, isouter=True)              
                     .filter(Appointment.start_time > end_time)
                     .filter(Appointment.end_time < date_time).all())

I think the above should do it.

answered Oct 22 '17 at 17:18

Jonathan

8,453
9
51
74

thanks for your interest , i tried your code but without results , and could you please explain why you are using isouter as extra parameter ? – swordfish Oct 22 '17 at 17:29
You can read about isouter here: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter – Jonathan Oct 22 '17 at 17:30
I am really sure the date that i've selected is busy and the results should returns just the free users, but i got nothing the `len(results)` is **0** . – swordfish Oct 22 '17 at 17:31
What if i tried by using `Appointment.user_id.any(Appointment.user_id != overlapping.user.id)`, so i am getting all the users except the busy one, and the overlapping will be the condition so when it return **True** i will assign it to results, but still one problem that if overlapping returns more than one i must run it inside a for loop , am i right ? – swordfish Oct 22 '17 at 17:52
In that case you could use the in clause described here -> https://stackoverflow.com/questions/8603088/sqlalchemy-in-clause – Jonathan Oct 22 '17 at 18:00
I think SQLAlchemy clauses will do the job with a few modification , i will figure it out , thanks @Jonathan for your help :) . – swordfish Oct 22 '17 at 18:11

How to sort all the available users for booking in Flask?

1 Answers1