CODEWITHSUNDEEP
angularjssails.jswaterline
Cris69
Cris69

Reputation: 590

A RESTful query from Angular to Sails.js with dates

I'm using sails-mysql adapter for Waterline to connect Sails,js with my Mysql database that stores dates in this format: "YYYY-MM-DD HH:MM:SS". With default Sails.js blueprints untouched I'm doing the following query from an Angular.js service:

CheckAvailability: function(arrival,departure,room){

                departure = moment(departure).endOf('day').format();

                arrival = moment(arrival).startOf('day').format();

                var bookingList = $resource(IP_ADDRESSES.dbserver+'bookings');

                return bookingList.query({

                    or:[{                            
                                departure: {'>=': arrival},
                                departure: {'<=': departure}                                                     

                        },{
                                arrival:{'>=':arrival},
                                arrival:{'<=':departure}

                        },{
                                arrival: {'<=': arrival},
                                departure: {'>=': departure}
                        }

                    ],

                    where:{room:room}}).$promise;
}

The returned result is not correct, I get all the reservation from the selected room, instead of receiving only overbooked rooms.

Upvotes: 1

Views: 244

Answers (3)

Cyril CHAPON
Cyril CHAPON

Reputation: 3747

This

or:[{                            
  departure: {'>=': arrival},
  departure: {'<=': departure}
},{
  arrival:{'>=':arrival},
  arrival:{'<=':departure}
},{
  arrival: {'<=': arrival},
  departure: {'>=': departure}
}]

is not a valid waterline condition.

The AND operator is done by adding multiple operators to the same key:

or:[{                            
  departure: {'>=': arrival, '<=': departure},
},{
  arrival:{'>=':arrival, '<=':departure},
},{
  arrival: {'<=': arrival},
  departure: {'>=': departure}
}]

As an aside, be aware that var b = {a:1, a:2} is a Javascript nonsense. You'd get a unique b.a property with a value of... well... I guess it totally depends on the implementation (Node.js, browser) and is unpredictable ?

Upvotes: 0

Cyril CHAPON
Cyril CHAPON

Reputation: 3747

Are you sure mixing query and where along in the same request does work ? I think it should work, thanks to implicit actionUtil.parseCriteria(req) call on blueprints routes; but it'd be worth giving a try embedding your date query inside where. Sounds like where is the only handled condition, isnt't it ?

NB: will grab the link for parseCriteria later, I'm on iPad right now.

Upvotes: 0

Cyril CHAPON
Cyril CHAPON

Reputation: 3747

I usually use ISO 8601 strings in conjonction with where.

Moment has endOf() and startOf() function that you can use to get the "last/fisrt instant of a day", and unparametrized format() will convert to an ISO 8601 string:

CheckAvailability: function(arrival,departure,room){
  arrival = moment(arrival);
  departure = moment(departure);

  var bookingList = $resource(IP_ADDRESSES.dbserver+'bookings');

  return bookingList.query({
    or:[{                            
      departure: {'>=': arrival.startOf('day').format()},
      departure: {'<=': departure.endOf('day').format()}                                                     
    },{
      arrival:{'>=':arrival.startOf('day').format()},
      arrival:{'<=':departure.endOf('day').format()}
    },{
      arrival: {'<=': arrival.endOf('day').format()},
      departure: {'>=': departure.startOf('day').format()}
    }],
    where:{room:room}})
  .$promise;

Upvotes: 1

Related Questions