[MongoDB] Query customers with conditional

help
1 
// Server
export const Companies = new Mongo.Collection('companies');
export const Customers = new Mongo.Collection('customers');
export const Orders = new Mongo.Collection('orders');

Customers.schema = new SimpleSchema({
    ...
    companyId: {regEx: SimpleSchema.RegEx.Id},
    ...
});

Orders.schema = new SimpleSchema({
    ...
    customerId: {regEx: SimpleSchema.RegEx.Id},
    orderDate: {type: Date()},
    ...
});

Would you help me get all Customers in specify Company that have Orders in specify orderDate

Thanks!

2

Hi,

Does anybody can help me? Thanks!

3

My understanding is that with Mongo and other nosql dbs you perform the logic for queries like this in your application instead of in Mongo.

In your case something like this:

// These should probably be passed as params to a function
const companyId = 'exampleIdString';
const ordersSince = new Date(2017, 6, 1); // 1st July 2017

// Find all customers in the specified company. Fetch the array of results 
// and immediately filter them
return customers = Customers.find({ companyId }).fetch().filter(customer => {
  // For each customer, search if there is an order after the specified date. 
  // `findOne` returns undefined if no records are found. use `!!` to force boolean response
  return !!Orders.findOne({
     customerId: customer._id, 
     orderDate: {
       $gte: ordersSince
    }
   });      
});
1 Like
4

You can use an aggregation package to get distinct customer ids from orders.

1 Like
5

Better yet you can use rawCollection

Orders.rawCollection().distinct('customerId', {orderDate:{ordersSince}}).then(...
1 Like
6

I use the Meteor package reywood:publish-composite # supports more complex publishing functions

1 Like
7

A quick suggestion for an aggregation, using meteorhacks:aggregate to add the aggregate method:

Customers.aggregate([
  { // All customers in company
    $match: {
      companyId,
    }
  },
  { // All orders for each customer into "orders" field as list
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "customerId",
      as: "orders"
    }
  },
  { // Make a doc for each element in orders
    $unwind: "$orders"
  }
  { // Find only the orders at orderDate (Not sure this works with nested fields in aggregation)
    $match: {
      "orders.orderDate": orderDate
    }
  },
  { // Group the orders back up into customers with a list of matching orders
    $group: {
      _id: "$_id",
      orders: { $push: "$orders" }
    }
  }
]);
1 Like