Query bug with foreign key with name different from the referenced table.

[elrancid]

I've found a bug making query with foreign key with name different from the referenced table. The bug occurs, for example, with a query like this:

Mytable.find({
    where: {
        or: [
            {
                name: {contains: 'foo'}
            },
            {
                othertable: {
                    name: {contains: 'foo'}
                }
            }
        ]
    }
})

In this example othertable is a foreign key property to a table with the name different from "othertable", in example:

...
attributes: {
...
    othertable: {
        model: 'secondtable',
        columnName: 'othertablerealname',
    }
...

I've partially solved the issues in waterline-sequel/sequel/lib/criteriaProcessor.js in findChild() function:

/**
 * Check if given `child` is in fact a child in the currentSchema, and if so return the key.
 *
 * @param {string} child
 *
 * @returns {boolean}
 */
CriteriaProcessor.prototype.findChild = function findChild (child) {
  var schema        = this.currentSchema,
      definitionKey = schema[child] ? child : _.findKey(schema, {columnName: child});

  return definitionKey && _.isPlainObject(schema[definitionKey]) && schema[definitionKey].foreignKey
    ? definitionKey
    : null;
};

If there is a foreign key, the definitionKey in findChild function returns the name of the property, but "findChild" is used in "processObject()->expandCriteria()" function:

...
  // Expand criteria object
  function expandCriteria (obj) {
    var child = self.findChild(parent),
        sensitiveTypes = ['text', 'string'], // haha, "sensitive types". "I'll watch 'the notebook' with you, babe."
        lower;

    _.keys(obj).forEach(function(key) {
      if (child && !self.isOperator(key)) {
        self.tableScope = child;
        self.expand(key, obj[key]);
        self.tableScope = null;

        return;
      }
...

and child (= findChild(parent)) is used to set the tableScope in order to save the table name referenced by the foreign key (to make the query). findChild must return the table name (or the table name must be grabbed before self.tableScope = child;). If the property has the same name of the table name (referenced by foreign key), then this bug doesn't occurs (because the name is the same). I've found it because I've the property name different from the table name. I've resolved, for now, changing the return of findChild:

  return definitionKey && _.isPlainObject(schema[definitionKey]) && schema[definitionKey].foreignKey
    ? schema[definitionKey].model
    : null;

However, this solution can't be considered completely correct because it returns the model name, not the table name. So, if the table name is different from the model name, we must get the real table name from the model. I don't know much about this module works, so I don't know how to achieve this task.

[sailsbot]

@elrancid Thanks for posting, we'll take a look as soon as possible. In the meantime, if you haven’t already, please carefully read the issue contribution guidelines and double-check for any missing information above. In particular, please ensure that this issue is about a stability or performance bug with a documented feature; and make sure you’ve included detailed instructions on how to reproduce the bug from a clean install. Finally, don’t forget to include the version of Node.js you tested with, as well as your version of Sails or Waterline, and of any relevant standalone adapters/generators/hooks.

Thank you!

[sgress454]

Hi @elrancid, it looks like you're trying to do something that's just not supported by Waterline. Waterline does not currently allow you to reference associations inside of queries -- that is, it doesn't create subqueries for you. It's definitely something we want for the next major version, but in the meantime if you need queries like "Give me the union of all users named 'Bob' and all users with brown pets", you'll either need to use .query to do a native MySQL query, or do two queries and combine the results.