balderdashy / waterline-criteria Goto Github PK

Utility library for use in Sails adapters where you need to filter a list of output using a criteria object

JavaScript 100.00%

waterline-criteria's Introduction

waterline-criteria

Utilities for working with Waterline criterias, especially for applying them to in-memory datasets.

This module was designed for adapters which communicate with key/value stores such as sails-disk, sails-memory, and sails-redis (i.e. they already implement the semantic interface, but need to implement the queryable interface).

Installation

$ npm install waterline-criteria --save

Filtering an array

Filter an array of dictionaries.

var WLCriteria = require('waterline-criteria');

var results = WLCriteria(dataset, criteria);

	Argument	Type	Details
1	dataset	((array))	An array of dictionaries to filter/sort.
2	criteria	((dictionary))	A Waterline criteria dictionary. See Concepts > Models & ORM > Query Language for more information.

Returns a filtered result set.

Example

var WLCriteria = require('waterline-criteria');

var SOME_DATASET = [
  {
    id: 1,
    name: 'Lyra'
  },
  {
    id: 2,
    name 'larry'
  }
];

// Filter dataset.
var results = WLCriteria(SOME_DATASET, {
  where: {
    name: { contains: 'lyr' }
  }
}).results;

// x ==> [{name: 'Lyra', id: 1}]

.validateWhereClause()

Check a where clause for obviously unsupported usage.

This does not do any schema-aware validation-- its job is merely to check for structural issues, and to provide a better experience when integrating from userland code.

var WLCriteria = require('waterline-criteria');

try {
  WLCriteria.validateWhereClause(where);
} catch (e) {
  switch (e.code) {
    case 'E_WHERE_CLAUSE_UNPARSEABLE':
      // ...
      break;
    default: throw e;
  }
}

// ...

	Argument	Type	Details
1	where	((dictionary))	A hypothetically well-formed `where` clause from a Waterline criteria.

If where clause cannot be parsed, throws an Error with a code property of 'E_WHERE_CLAUSE_UNPARSEABLE'.

.validateSortClause()

Check a sort clause for obviously unsupported usage.

This does not do any schema-aware validation-- its job is merely to check for structural issues, and to provide a better experience when integrating from userland code.

var WLCriteria = require('waterline-criteria');

try {
  WLCriteria.validateSortClause(sort);
} catch (e) {
  switch (e.code) {
    case 'E_SORT_CLAUSE_UNPARSEABLE':
      // ...
      break;
    default: throw e;
  }
}

// ...

	Argument	Type	Details
1	sort	((dictionary)) or ((string))	A hypothetically well-formed `sort` clause from a Waterline criteria.

If sort clause cannot be parsed, throws an Error with a code property of 'E_SORT_CLAUSE_UNPARSEABLE'.

Bugs

To report a bug, click here.

This is a built-in module in the Sails framework and the sails-disk adapter. It is installed automatically when you run npm install sails.

Version notes

The master branch of this repository holds waterline-criteria used in Sails versions 0.10.0 and up. If you're looking for the version for the v0.9.x releases of Sails, the source is located here.

Contributing

Please observe the guidelines and conventions laid out in the Sails project contribution guide when opening issues or submitting pull requests.

License

The Sails framework is free and open-source under the MIT License.

waterline-criteria's People

Contributors

Stargazers

Watchers

waterline-criteria's Issues

Near gps location filter

Hi!
Im suggesting adding a gps proximity filtering option.
the criteria could look like that:
where: {location: {near: gps_here}}, limit:3}

the sql query to make this happen:

SELECT *
  FROM (
 SELECT 
        u.latitude, u.longitude,
        p.radius,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(u.latitude))
                 * COS(RADIANS(p.longpoint - u.longitude))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(u.latitude)))) AS distance
  FROM user AS u
  JOIN (   /* these are the query parameters */
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
                50.0 AS radius,      111.045 AS distance_unit
    ) AS p ON 1=1
  WHERE u.latitude
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND u.longitude
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
 ) AS d
 WHERE distance <= radius
 ORDER BY distance
 LIMIT 15

http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

sort doesn't work if property contains uppercase characters

Lines 80 and 81 of index.js convert the sort key to lowercase, which prevents it from working on any properties which contain uppercase.

Limit Filter causes error when limit = 0

The Limit filter is improperly handling valid limit=0 query. Needs to be modified so that when limit is passed in as 0 it returns the full data set instead of "null" which causes the application to break. This is caused by valid API calls that override the default 30 record limit using the limit= querystring parameter.

/transactions/find?Paid=false&limit=0

The current code is:

module.exports = function (data, limit) {
  if( limit === undefined || !data ) return data;
  if( limit === 0 ) return null;
  return _.first(data, limit);
};

Returning null breaks the result set functionality in query.js.

The proposed fix is:

module.exports = function (data, limit) {
  if( limit === undefined || !data || limit === 0) return data;
  return _.first(data, limit);
};

Query "where" unexpected behavior.

[Originally posted at waterline repo, moved here. See original issue here]

I have an entity with a trackingNumber field of type string. The field is unique.

Querying by trackingNumber I expect to get one and only one record. Instead I get two. If you look at the trackingNumber value on each record, you can see that they are very similar.

333230389361289949034304032696
333230389361289949034304032726

// 20150707112846
// http://localhost:3030/api/packages?where={"trackingNumber":"420100389361289949054104032726"}

[
  {
    "uuid": "e9201f96-ec85-4faf-8a00-977f6a849222",
    "trackingNumber": "333230389361289949034304032696",
    "status": "delivered",
    "id": 2,
    "createdAt": "2015-07-06T15:14:51.446Z",
    "updatedAt": "2015-07-06T16:49:48.187Z"
  },
  {
    "trackingNumber": "333230389361289949034304032726",
    "status": "received",
    "uuid": "e9201f96-ec85-4faf-8a00-977f6a849498",
    "createdAt": "2015-07-07T15:27:31.344Z",
    "updatedAt": "2015-07-07T15:27:31.429Z",
    "id": 6
  }
]

If I query by uuid works as expected. Note that the values are also pretty similar:

e9201f96-ec85-4faf-8a00-977f6a849222
e9201f96-ec85-4faf-8a00-977f6a849498

/ 20150707130619
// http://localhost:3030/api/packages?where={"uuid":"e9201f96-ec85-4faf-8a00-977f6a849222"}

[
  {
    "uuid": "e9201f96-ec85-4faf-8a00-977f6a849222",
    "trackingNumber": "333230389361289949034304032696",
    "status": "delivered",
    "id": 2,
    "createdAt": "2015-07-06T15:14:51.446Z",
    "updatedAt": "2015-07-06T16:49:48.187Z"
  }
]

"waterline": "^0.10.24",
"sails-disk": "^0.10.8",

join.alias is undefined at associations branch

Hi,

when I run query with populate() I always get an error on this line
https://github.com/balderdashy/waterline-criteria/blob/associations/index.js#L164

Cannot call method 'toLowerCase' of undefined

I run a debug mode with breakpoint and notice that join.alias is undefined.

I cannot find where the alias is defined. I have searched this files:
https://github.com/balderdashy/waterline/blob/associations/lib/waterline/utils/normalize.js
https://github.com/balderdashy/waterline/blob/associations/lib/waterline/query/deferred.js

but none of them fill the alias attribute.

Getting 404 on waterline-criteria when installing Sails.js

I'm getting the following error when trying to install Sails.js:

npm ERR! fetch failed https://registry.npmjs.org/waterline-criteria/-/waterline-criteria-0.9.5.tgz
npm http 304 https://registry.npmjs.org/underscore.string/2.3.1
npm http GET https://registry.npmjs.org/inherits
npm ERR! Error: ENOENT, lstat '/Users/glenselle/.nvm/v0.10.17/lib/node_modules/sails/node_modules/ejs/support/compile.js'
npm ERR! If you need help, you may report this log at:
npm ERR!     <http://github.com/isaacs/npm/issues>
npm ERR! or email it to:
npm ERR!     <[email protected]>

Going to the .tgz url yields the following:

{"error":"not_found","reason":"Document is missing attachment"}

Waterline-criteria was recently updated to 0.9.5. It looks as if this may have caused the problem?

symbolic usage of not (and maybe other things) not working outside of a sub-attribute criteria modifier


'!': {
  name: 'bob'
}

NOTE: this should also be added to waterline-adapter-tests

Not query on optional document properties

I'm using sails-memory for the test suite. Everywhere else, I'm using MongoDB.

Consider a user collection with the following documents:

{
  id: "5694d5e6ae3e12001d000008",
  isSpecial: true
},
{
  id: "5694d5e80430b50012000004"
}

Consider the following query:

User.find({
  isSpecial: { '!': true }
}).exec(...);

Currently, this query will return 1 document in MongoDB: 5694d5e80430b50012000004. In sails-memory, this will return 0 documents, since 5694d5e80430b50012000004 doesn't have a property isSpecial (which is checked here).

Not sure whether you guys consider this an issue. I stumbled upon this and for me, it's a small issue. Will add isSpecial: false to my test database for now. Just wanted to let you know and decide for yourselves what to do with this.

sails generate model on disk db ,use'?limit=10',always return '[]'

on console in put

sails generate foo
sail lift

on browser input

http://localhost:1337/foo/create?name=a
http://localhost:1337/foo/create?name=b
http://localhost:1337/foo/create?name=c

then

http://localhost:1337/foo/limit=2

always return

[]

p.s

version 0.9
module: 'sails-disk'

I know what the problem is

controller.find.js 94line
req.param('limit') must be a Number!

lodash,js


    function first(array, callback, thisArg) {
      if (array) {
        var n = 0,
            length = array.length;

        if (typeof callback != 'number' && callback != null) {
   ...

user 'http://localhost:1337/foo?limit=5&sort=id%20asc',return undefined error

index.js

function sortData(data, sortCriteria) { line51

sort need array,but 'sort' is object


function sortData(data, sortCriteria) {
  var keyPair;

  data.sort(function (a, b) {
    var sortIndex = 0;
    function comp(key, val) {
      // Check if one record has the value and the other doesn't
      if(a[key] && !b[key]) {
        if(val === 1) return 1;
        return -1;
      }

      if(!a[key] && b[key]) {
        if(val === 1) return -1;
        return 1;
      }

      if(a[key] < b[key]){
        if(val === 1) return -1;
        return 1;
      }

      if (a[key] > b[key]) {
        if(val === 1) return 1;
        return -1;
      }
      if(!sortCriteria.length)
      {
        return 0;
      }
      if (++sortIndex >= sortCriteria.length) return 0;
      keyPair = getKeyPair(sortCriteria, sortIndex);
      
      return comp(keyPair.key, keyPair.val);
    }
    
    keyPair = getKeyPair(sortCriteria, sortIndex);
    var ret = comp(keyPair.key, keyPair.val);
    return ret;
  });
  return data;
}

It's work!

Memory Leak

So pretty much as the title says...

This module does tonnes of "cloneDeep" which has very massive memory issues when your querying a database that has more than 50k items...

You go from 14s ( without this module ) to 10m ( with the same datasets )

There is also problems with ".pluck" too, but since there is only one occurrence of that it's not too bad...

This module shouldn't need to clone datasets or even alter them...
It should just filter them...

Use in Browser?

Can this still be used in the browser?

The example in the readme references the index.js, but bower.json ignores index.js so it won't ever be installed.

Taking a look at the different js files it looks like they are all geared toward node.js.
I'm hoping to use waterline in the browser and it looks like this is a key piece to the puzzle, but I'm wondering if it's no longer supported in the browser. If so, the readme is out of date. It also references wlFilter which isn't in any of the installed bower code.

Please update lodash version to 3.10.1 like most of sails modules

Is there a good reason why it's still using 2.4.2 when almost everything else is 3.10.1?

Thanks,
Bren