ArangoDb get latest document from all collections

Question

I'm beginning to think I've modeled my data a bit incorrectly, since I'm having trouble querying it.

Currently what I have is a bunch of Customers (modeled as a Database per customer) These Customers have a bunch of Devices: Device1...n (modeled as a collection per device) These devices generate messages (modeled as documents within the device collection).

In order to give good feedback to customers, I now want to support retrieving a customers latest messages (one message per device).

I'm having trouble to find documentation describing how to query over multiple collections, as there can be 1000s of devices for a customer, I'd rather not do 1000s of queries.

Thanks!

Answer 1

If there can be 1000s of devices per customer, and device messages are stored in device-specific collections, searching for the latest message for a customer would require you to find the latest record in a variable number of collections, which will not only hard to express in a query but also inefficient.

Is it possible to put the messages of all devices for a given customer into a single customer-specific collection, and store the device id as an attribute in each document?

For example:

// create customer-specific collections
db._create("messages_customer1");
db._create("messages_customer2");

// create an index on `dt` attribute in each collection 
// so messages can be queried efficiently sorted by date
db.messages_customer1.ensureIndex({ type: "skiplist", fields: [ "dt" ]});
db.messages_customer2.ensureIndex({ type: "skiplist", fields: [ "dt" ]});

// insert some messages for customer 1
db.messages_customer1.insert({ device: 123, dt: Date.now(), message: "foo" });
db.messages_customer1.insert({ device: 123, dt: Date.now(), message: "bar" });
db.messages_customer1.insert({ device: 456, dt: Date.now(), message: "baz" });

// insert some messages for customer 2
db.messages_customer2.insert({ device: 999, dt: Date.now(), message: "qux" });
db.messages_customer2.insert({ device: 888, dt: Date.now(), message: "wut" });

Now it will be relatively easy to find the latest message for a given customer:

• determine customer id via request and or business logic
• with customer id (e.g. id 1), query customer-specific collection

For example:

var query = "FOR m IN @@messages SORT m.dt DESC LIMIT 1 RETURN m";
var id = 1;
var params = { "@messages": "messages_customer" + id }
latestMessage = db._query(query, params).toArray()[0];

If the messages are all that's customer-specific, then there's also no need to create separate databases per customer, as all customer-specific collections could go into the same database. You should of course care about access control to the data, either via application business logic or Foxx.