Query over the field that is equal to other collection _id

[radswiat]

Expected behavior

Query should match documents in scenarios like this filter=[{"$match": {"categoryId": "58909536836094f025476510"}}] or that ?filter={"categoryId": "58909536836094f025476510"}

Actual behavior

Query returns nothing ( no results ).

Steps to reproduce the problem

categoryId value should be a STRING and it should be equal to _ID of any other collection.

When categoryId value is changed to anything else query will work - there seems to be a conflict if _id value from any other collection is used as a value of the field.

Same query works fine in mongo compass.

Package details

1.15.2, windows

[jimlambie]

Hi @radswiat, thanks for the issue report. I'd need to see your full request URL and collection schema files before I can help on this one. Can you attach the json files and let me know your full URL? Thanks!

[radswiat]

Hi @jimlambie, thank you for quick response, I created a git repo for you: https://github.com/radswiat/dadi-test

Hope that that's all you need ( unfortunately I can't expose whole project ) Thanks!

[jimlambie]

@radswiat thanks, that should be enough for me to use - I'll get back to you in a few hours (on a train at the moment)

[jimlambie]

@radswiat here's how I would do it.

Use a Reference field to create the link from documentRows > documents. Instead of the field documentId (as String), change it to document (as type Reference) and use the Reference field settings to point it to the documents collection.

collection.documentRows.json

{
  "fields": {
    "document": {
      "type": "Reference",
      "settings": {
        "collection": "documents"
      },
      ...
    },
    ...
  },
  "settings": {
    ...
  }
}

Now when you create new documentRows, you can still use the document _id:

{
  "document": "5895258e29a37ed4332c939a",
  "categoryId": "1",
  "data": {
    "a": "test"
  },
  "status": "OK"
}

So your endpoint can now use dot notation in the query, but ensure you pass compose: true in the query options. { compose: true } instructs API to resolve the Reference fields, replacing the ID value with the actual document in the returned results.

Example

model.find({ "document._id": "5894f8fee21a1704858fda59" }, { compose: true }), callback)

Endpoint

var chalk = require('chalk');
var path = require('path');
var url = require('url');
var model = require(path.join(__dirname, '/../../../node_modules/@dadi/api/dadi/lib/model'));
var rows = model('documentRows')

module.exports.get = function (req, res, next) {
  // extract the apiVersion from the URL to use in the query
  var parsed = url.parse(req.url, true)
  var apiVersion = parsed.pathname.split('/')[1]

  return rows.find({ "document._id": "5894f8fee21a1704858fda59", "apiVersion": apiVersion }, { compose: true }, function (err, result) {
    res.setHeader('content-type', 'application/json');
    res.statusCode = 200;
    res.end(JSON.stringify(result));
  })
}

I hope this helps!

[jimlambie]

Your issue report used categoryId in the example, but your test repo used documentId. The same technique can be used for your categories: make category a Reference field and point it to the categories collection, then query with { "category._id": "1233456af09..."}

[radswiat]

@jimlambie Thank you for deep explanation how to use Reference field ! Really appreciated. Just being curious - is there a particular reason why query doesn't work without using composed and references ? Is it in conflict with a compose feature ?

[jimlambie]

@radswiat sorry I should've explained why it wasn't working. Behind the scenes your string query is being converted to an ObjectID, so the query being sent to MongoDB is actually {"documentId": ObjectID(57377....)}

This is was originally designed to allow the system to work with our existing databases where ID values were stored as ObjectID. There is a case for making it work in the way you have tried, so I will turn this issue into an enhancement request. We have some large scale projects this would need to be tested against, so I'll get one of the team to start taking a look.

Thanks! Jim

[radswiat]

@jimlambie Thank you for deep explanation. I'll play with both ways then:

1. Reference fields
2. Store ObjectID instead of string in documentId and objectId Thanks Radek

[eduardoboucas]

This should've been fixed in API 3.1. Please reopen if the problem persists.

Thanks!