mongo db - map reduce and lookup
<p>Is it possible to perform both a map reduce with a lookup in the same query pipeline efficiently?</p>

<p>Let's say I've two collections:</p>

<ul>
<li>items: <code>{ _id, group_id, createdAt }</code></li>
<li>purchases: <code>{ _id, item_id }</code></li>
</ul>

<p>I want to get the top n item groups, based on the number of purchases on the most recent x items per group.</p>

<p>If I had the number of purchases available in the item documents, then I could aggregate and sort, but this is not the case.</p>

<p>I can get the most recent x items per group as so:</p>

<pre><code>let x = 3;
let map = function () {
  emit(this.group_id, { items: [this] });
};
let reduce = function (key, values) {
  return { items: getLastXItems(x, values.map(v =&gt; v.items[0])) };
};
let scope = { x };

db.items.mapReduce(map, reduce, { out: { inline: 1 }, scope }, function(err, res) {
  if (err) {
    ...
  } else {
    // res is an array of { group_id, items } where items is the last x items of the group
  }
});
</code></pre>

<p>But I'm missing purchase count so I can't use it to sort groups, and output the top n groups (which btw I'm not even sure I can do)</p>

<p>I'm using this on a web server, and running the query with scope variable depending on the user context, so I don't want to output the result to another collection and have to do everything inline.</p>

<p>=== edit 1 === add data example:</p>

<p>Sample data could be:</p>

<pre><code>// items
{ _id: '1, group_id: 'a', createdAt: 0 }
{ _id: '2, group_id: 'a', createdAt: 2 }
{ _id: '3, group_id: 'a', createdAt: 4 }
{ _id: '4, group_id: 'b', createdAt: 1 }
{ _id: '5, group_id: 'b', createdAt: 3 }
{ _id: '6, group_id: 'b', createdAt: 5 }
{ _id: '7, group_id: 'b', createdAt: 7 }
{ _id: '8, group_id: 'c', createdAt: 5 }
{ _id: '9, group_id: 'd', createdAt: 5 }

// purchases
{ _id: '1', item_id: '1' }
{ _id: '2', item_id: '1' }
{ _id: '3', item_id: '3' }
{ _id: '4', item_id: '5' }
{ _id: '5', item_id: '5' }
{ _id: '6', item_id: '6' }
{ _id: '7', item_id: '7' }
{ _id: '8', item_id: '7' }
{ _id: '9', item_id: '7' }
{ _id: '10', item_id: '3' }
{ _id: '11', item_id: '9' }
</code></pre>

<p>and sample result with <code>n = 3</code> and <code>x = 2</code> would be:</p>

<pre><code>[
  group_id: 'a', numberOfPurchasesOnLastXItems: 4,
  group_id: 'b', numberOfPurchasesOnLastXItems: 3,
  group_id: 'c', numberOfPurchasesOnLastXItems: 1,
]
</code></pre>