How to select parent based on children's value using mongoose and GraphQL?

Question

I'm trying to achieve something equivalent to a conditional JOIN query, but then with GraphQL. I'm using Mongoose for my db model and MongoDB as database.

I'll illustrate my problem with the following graphQL schema:

type Booking {
    _id: ID!
    client: Client!
    rooms: Room!
    activities: Activity!
    nrOfAdults: Int!
    arrivalDate: String!
    departureDate: String!
}

type Room {
  _id: ID!
  name: String!
  contents: String
  priceNight: Float!
  maxAdults: Int!
  reservations: [Booking]
}

The Mongoose schema:

const bookingSchema = new Schema(
  {
    client: {
      type: Schema.Types.ObjectId,
      ref: 'Client'
    },
    rooms: [{
      type: Schema.Types.ObjectId,
      ref: 'Rooms'
    }],
    nrOfAdults: {
      type: Number,
      required: true
    },
    arrivalDate: {
      type: Date,
      required: true
    },
    departureDate: {
      type: Date,
      required: true
    }
  },
  { timestamps: true }
);

const roomSchema = new Schema({
  name: {
    type: String,
    required: true
  },
  priceNight: {
    type: Number,
    required: true
  },
  maxAdults: {
    type: Number,
    required: true
  },
  reservations: [
    {
      type: Schema.Types.ObjectId,
      ref: 'Booking'
    }
  ]
});

I can query rooms, for example, if I want to get the rooms for 3 or more adults I run:

       Room.find({
         maxAdults: { $gte: 3 }
       });

This works fine.

However, I'd also like to show the available rooms, which means I need to impose a condition on the booking objects which are hold in reservation. I thought this would be fairly easy, using something like:

       Room.find({
         maxAdults: { $gte: 3 },
         reservations: { $elemMatch: { arrivalDate: { $gte: *some date*}}}
       });

But it returns an empty array, while it should return some value, based on the data in mongodb:

To make things a little more clear, I'd like to achieve the same outcome as the following SQL query would give me:

SELECT *
FROM room
JOIN booking ON room.id = booking.roomId
WHERE
room.maxAdults >= 3
AND
(
booking.arrivalDate > CAST('2020-05-15' AS DATE)
OR
booking.departureDare < CAST(2020-05-06' AS DATE)
)

If you can add your mongoose schema as well then it will be easier to answer your question. — vijaykrishnavanshi, Mar 19 '20 at 15:04

vijaykrishnavanshi · Accepted Answer · 2020-03-24T12:30:43.080

0

Assuming that you have saved the values similar to what you have mentioned in the mongoose schema.

Explore the how to do join in mongodb. Aim is to do the join before executing the query on the sub fields from the different collection.

Relevant Answer: How do I perform the SQL Join equivalent in MongoDB?

I suggest using aggregate pipeline for accomplishing what you want.

Suggested code :

Room.aggregate([
    {
        $match: {
            maxAdults: { $gte: 3 }
        }
    },
    {
        $lookup: {
            from: "bookings",
            localField: "reservations",
            foreignField: "_id",
            as: "booking"
        }
    },
    {
        $unwind: '$booking'
    },
    {
        $match: {
          booking.arrivalDate: { $gte: *some date* }
        }
    },
])

edited Mar 24 '20 at 12:30

answered Mar 19 '20 at 15:25

vijaykrishnavanshi

785
7
13

Unfortunately that doesn't solve the problem for me. It returns an empty array, no matter what condition I put on booking.arrivalDate – Ujlm Mar 20 '20 at 20:13
1

The solution works when I change ```from: "Booking" ``` into ```from: "bookings" ``` , as Mongoose apparently makes this plural. – Ujlm Mar 20 '20 at 22:19
The problem right now is that each room-booking pair gets evaluated. If a room has for example three bookings, one of which conflicting with the given dates, then still the room gets twice returned, because of the two other dates. I would like to return all the unique rooms which are available the given dates. – Ujlm Mar 27 '20 at 13:02

How to select parent based on children's value using mongoose and GraphQL?

1 Answers1