New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support $search in query syntax #334

Closed
daffl opened this Issue May 20, 2016 · 27 comments

Comments

Projects
None yet
@daffl
Member

daffl commented May 20, 2016

I am consolidating all the individual issues and discussions here:

This is a proposed special attribute that allows you to fuzzy match a property. Possibly even multiple properties and/or nested documents.

Suggested syntax:

name: {
  $search: ['alice', 'Alice', 'bo', /$bob/i]
}

Following similar syntax to our other special query filters, this would allow you to filter by a singular value, multiple values (treated like an or) and/or regular expressions directly.

Service Adapter Completion

  • NeDB
  • MongoDB
  • Mongoose
  • Sequelize
  • Knex
  • Waterline
  • RethinkDB
  • Memory
  • Localstorage
  • LevelUp
  • Blob?? (maybe to support fuzzy matching filenames?)
@daffl

This comment has been minimized.

Show comment
Hide comment

@ekryski ekryski referenced this issue May 20, 2016

Closed

Feathers Auk Release #299

22 of 28 tasks complete

@ekryski ekryski added the Backlog label May 21, 2016

@ekryski ekryski added this to the Auk milestone May 21, 2016

@ekryski ekryski added the Feature label May 21, 2016

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jul 20, 2016

any news on this feature ?
if it is not implemented , how to fuzzy match a property now without this special attribute ?

ghost commented Jul 20, 2016

any news on this feature ?
if it is not implemented , how to fuzzy match a property now without this special attribute ?

@daffl

This comment has been minimized.

Show comment
Hide comment
@daffl

daffl Jul 20, 2016

Member

You can create a hook that for e.g. MongoDB converts the $search query to a $regex:

app.service('todos').before({
  find(hook) {
    const query = hook.params.query;
    if(query.name.$search) {
      query.name = { $regex: new RegExp(query.name.$search) }
    }
  }
});
Member

daffl commented Jul 20, 2016

You can create a hook that for e.g. MongoDB converts the $search query to a $regex:

app.service('todos').before({
  find(hook) {
    const query = hook.params.query;
    if(query.name.$search) {
      query.name = { $regex: new RegExp(query.name.$search) }
    }
  }
});
@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jul 21, 2016

the way to make fuzzy match for some field in collection before your comment was to use custom route and write native query with same service db engine like "mongo" or "nedb" .
but this way is very good and show me new use case to use hooks in featherjs

ghost commented Jul 21, 2016

the way to make fuzzy match for some field in collection before your comment was to use custom route and write native query with same service db engine like "mongo" or "nedb" .
but this way is very good and show me new use case to use hooks in featherjs

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jul 22, 2016

what about this hook as simple solution for search in mongodb & nedb

exports.searchRegex = function () {
  return function (hook) {
    const query = hook.params.query;
    for (let field in query) {
      if(query[field].$search && field.indexOf('$') == -1) {
        query[field] = { $regex: new RegExp(query[field].$search) }
      }
    }
    hook.params.query = query
    return hook
  }
}

and simply include it in the

src/services/ServiceName/hooks

like

exports.before = {
  all: [],
  find: [globalHooks.searchRegex()]
}

ghost commented Jul 22, 2016

what about this hook as simple solution for search in mongodb & nedb

exports.searchRegex = function () {
  return function (hook) {
    const query = hook.params.query;
    for (let field in query) {
      if(query[field].$search && field.indexOf('$') == -1) {
        query[field] = { $regex: new RegExp(query[field].$search) }
      }
    }
    hook.params.query = query
    return hook
  }
}

and simply include it in the

src/services/ServiceName/hooks

like

exports.before = {
  all: [],
  find: [globalHooks.searchRegex()]
}
@ruddfawcett

This comment has been minimized.

Show comment
Hide comment
@ruddfawcett

ruddfawcett Aug 17, 2016

For those interested, you can make the above code posted by @alnour-altegani case insensitive by changing

 query[field] = { $regex: new RegExp(query[field].$search) }

to:

 query[field] = { $regex: new RegExp(query[field].$search, 'i') } // note the 'i'

ruddfawcett commented Aug 17, 2016

For those interested, you can make the above code posted by @alnour-altegani case insensitive by changing

 query[field] = { $regex: new RegExp(query[field].$search) }

to:

 query[field] = { $regex: new RegExp(query[field].$search, 'i') } // note the 'i'
@beeplin

This comment has been minimized.

Show comment
Hide comment
@beeplin

beeplin Oct 7, 2016

Contributor

so why $search, not $regex?
hope it compatible with mongoDB's original syntax, which is available now out-of-box already, so we don't need to change it in the future.

Contributor

beeplin commented Oct 7, 2016

so why $search, not $regex?
hope it compatible with mongoDB's original syntax, which is available now out-of-box already, so we don't need to change it in the future.

@ekryski ekryski modified the milestones: Auk, Buzzard Oct 17, 2016

@ekryski ekryski referenced this issue Oct 17, 2016

Closed

Feathers Buzzard Release #436

5 of 18 tasks complete
@cklmercer

This comment has been minimized.

Show comment
Hide comment
@cklmercer

cklmercer Nov 14, 2016

Just stumbled upon this after fighting with the Mongoose adapter for half an hour. This would be a super nice addition.

Any suggestions for a work-around for the feathers-mongoose adapter?

cklmercer commented Nov 14, 2016

Just stumbled upon this after fighting with the Mongoose adapter for half an hour. This would be a super nice addition.

Any suggestions for a work-around for the feathers-mongoose adapter?

@daffl

This comment has been minimized.

Show comment
Hide comment
@daffl

daffl Nov 14, 2016

Member

There is a working hook two comments above. Mongoose already supports it, you just have to convert the query into a regular expression in a before hook.

By now, I am also leaning more towards not adding this. Defining an abstract search format for all databases isn't really possible. Some support %like% queries, others use regular expressions and some do not support searching at all. Fuzzy matches should be implemented with the features of the database you are using.

Member

daffl commented Nov 14, 2016

There is a working hook two comments above. Mongoose already supports it, you just have to convert the query into a regular expression in a before hook.

By now, I am also leaning more towards not adding this. Defining an abstract search format for all databases isn't really possible. Some support %like% queries, others use regular expressions and some do not support searching at all. Fuzzy matches should be implemented with the features of the database you are using.

@cklmercer

This comment has been minimized.

Show comment
Hide comment
@cklmercer

cklmercer Nov 14, 2016

Thanks for the quick reply.

cklmercer commented Nov 14, 2016

Thanks for the quick reply.

@frastlin

This comment has been minimized.

Show comment
Hide comment
@frastlin

frastlin Dec 7, 2016

One can just use $like in the find method when using sequelize. It is treated like $in or $or.
Does anyone know how to do re searches in sequelize? like throws an error when a re is passed.
One uses a % to represent random characters ({$like: "co%"} will find "corn", "correct", "cornucopia"... anything that starts with co. If the % is on the other side, it would find "poco", "banco" and anything that ends with co. If it was "%co%" any "co" in a string would work.) But I can't find any documentation that says what exactly % is or where it came from. If I could find something that explained % in this context, it would probably explain how to use it (or other symbols) in more complex ways.

frastlin commented Dec 7, 2016

One can just use $like in the find method when using sequelize. It is treated like $in or $or.
Does anyone know how to do re searches in sequelize? like throws an error when a re is passed.
One uses a % to represent random characters ({$like: "co%"} will find "corn", "correct", "cornucopia"... anything that starts with co. If the % is on the other side, it would find "poco", "banco" and anything that ends with co. If it was "%co%" any "co" in a string would work.) But I can't find any documentation that says what exactly % is or where it came from. If I could find something that explained % in this context, it would probably explain how to use it (or other symbols) in more complex ways.

@daffl

This comment has been minimized.

Show comment
Hide comment
@daffl

daffl Dec 8, 2016

Member

Most likely it is anything that the SQL LIKE operator supports for your database.

Member

daffl commented Dec 8, 2016

Most likely it is anything that the SQL LIKE operator supports for your database.

@daffl

This comment has been minimized.

Show comment
Hide comment
@daffl

daffl Jan 4, 2017

Member

@sajov Example linked in the comment above: #334 (comment)

Member

daffl commented Jan 4, 2017

@sajov Example linked in the comment above: #334 (comment)

@sajov

This comment has been minimized.

Show comment
Hide comment
@sajov

sajov Jan 4, 2017

got it, my fault. Thanks

my solution with implementation of $or for multiple $search

exports.searchRegex = function () {
  return function (hook) {
    const query = hook.params.query;
    for (let field in query) {
      if(query[field].$search && field.indexOf('$') == -1) {
        query[field] = { $regex: new RegExp(query[field].$search, 'i') }
      }
      if(field == '$or') {
        query[field].map((action, index) => {
            let f = Object.keys(action)[0];
            if(action[f].$search) {
                action[f] = { $regex: new RegExp(action[f].$search, 'i') }
            }
            return action;
        });
      }
    }
    hook.params.query = query
    return hook
  }
}

But how you guys deal with integer?

sajov commented Jan 4, 2017

got it, my fault. Thanks

my solution with implementation of $or for multiple $search

exports.searchRegex = function () {
  return function (hook) {
    const query = hook.params.query;
    for (let field in query) {
      if(query[field].$search && field.indexOf('$') == -1) {
        query[field] = { $regex: new RegExp(query[field].$search, 'i') }
      }
      if(field == '$or') {
        query[field].map((action, index) => {
            let f = Object.keys(action)[0];
            if(action[f].$search) {
                action[f] = { $regex: new RegExp(action[f].$search, 'i') }
            }
            return action;
        });
      }
    }
    hook.params.query = query
    return hook
  }
}

But how you guys deal with integer?

@ghost

This comment has been minimized.

Show comment
Hide comment
@ghost

ghost Jan 6, 2017

@sajov thank you for sharing your work . but, can you give example of how to use this code to search with $or operator ?

ghost commented Jan 6, 2017

@sajov thank you for sharing your work . but, can you give example of how to use this code to search with $or operator ?

@sajov

This comment has been minimized.

Show comment
Hide comment
@sajov

sajov Jan 6, 2017

@ruddfawcett i use it in a datatable context like this

for (var i = 0;i < opts.tableHeader.length; i++) {
                        let q = {};
                        q[opts.tableHeader[i]] = {$search: queryObj.search.value.value};
                        query.$or.push(q);
                    }

here is an working example https://github.com/sajov
I write README right now

sajov commented Jan 6, 2017

@ruddfawcett i use it in a datatable context like this

for (var i = 0;i < opts.tableHeader.length; i++) {
                        let q = {};
                        q[opts.tableHeader[i]] = {$search: queryObj.search.value.value};
                        query.$or.push(q);
                    }

here is an working example https://github.com/sajov
I write README right now

@MidnightP

This comment has been minimized.

Show comment
Hide comment
@MidnightP

MidnightP Jan 16, 2017

@sajov Were you able to write a README ? I'd be very interested to see how I could use this case in combination with an $or -array e.g. like

[
    { _id: searchInput },
    { type: searchInput }
]

MidnightP commented Jan 16, 2017

@sajov Were you able to write a README ? I'd be very interested to see how I could use this case in combination with an $or -array e.g. like

[
    { _id: searchInput },
    { type: searchInput }
]
@sajov

This comment has been minimized.

Show comment
Hide comment
@sajov

sajov Jan 16, 2017

@MidnightP

Hook example which treat $search as { $regex: new RegExp(value, 'i') };
https://github.com/sajov/riot-crud/blob/master/example/src/hooks/index.js#L50-L53

Client example
https://github.com/sajov/riot-crud/blob/master/tags/themes/bootstrap/views/crud-views.tag#L169-L170

query.$or = [
    {fieldA : {$search: value}},
    {fieldB : {$search: value}},
];

hope that answer your question

sajov commented Jan 16, 2017

@MidnightP

Hook example which treat $search as { $regex: new RegExp(value, 'i') };
https://github.com/sajov/riot-crud/blob/master/example/src/hooks/index.js#L50-L53

Client example
https://github.com/sajov/riot-crud/blob/master/tags/themes/bootstrap/views/crud-views.tag#L169-L170

query.$or = [
    {fieldA : {$search: value}},
    {fieldB : {$search: value}},
];

hope that answer your question

@MidnightP

This comment has been minimized.

Show comment
Hide comment
@MidnightP

MidnightP Jan 18, 2017

@sajov Thanks!

I rewrote things a bit and ended up using the hook below for a client side query containing this:

orRegex: [
  { 
    fieldA: {
      $search: 'searchinput'
    }
  },
  { 
    fieldB: {
      $search: 'searchinput'
    }
  },
  et cetera...
]

Hook:

return function(hook) {
    const { query } = hook.params

    if( Object.keys(query).includes('orRegex') )  {
      const or = query.orRegex.map((field) => {
        console.log('Object.keys(field)[0]: ', Object.keys(field)[0] )

        const attribute = Object.keys(field)[0]

        return {
          [attribute]: {
            $regex: new RegExp(field[Object.keys(field)[0]]['$search']), $options: 'ix'
          }
        }
      })

      delete(query.orRegex)

      hook.params.query['$or'] = or
    }
    return hook
  }

Suggestions welcome of course

MidnightP commented Jan 18, 2017

@sajov Thanks!

I rewrote things a bit and ended up using the hook below for a client side query containing this:

orRegex: [
  { 
    fieldA: {
      $search: 'searchinput'
    }
  },
  { 
    fieldB: {
      $search: 'searchinput'
    }
  },
  et cetera...
]

Hook:

return function(hook) {
    const { query } = hook.params

    if( Object.keys(query).includes('orRegex') )  {
      const or = query.orRegex.map((field) => {
        console.log('Object.keys(field)[0]: ', Object.keys(field)[0] )

        const attribute = Object.keys(field)[0]

        return {
          [attribute]: {
            $regex: new RegExp(field[Object.keys(field)[0]]['$search']), $options: 'ix'
          }
        }
      })

      delete(query.orRegex)

      hook.params.query['$or'] = or
    }
    return hook
  }

Suggestions welcome of course

@daffl

This comment has been minimized.

Show comment
Hide comment
@daffl

daffl Jan 18, 2017

Member

Maybe @eddyystop has some thoughts if this can be turned into a common hook.

Member

daffl commented Jan 18, 2017

Maybe @eddyystop has some thoughts if this can be turned into a common hook.

@eddyystop

This comment has been minimized.

Show comment
Hide comment
@eddyystop

eddyystop Mar 23, 2017

Member

Just saw this now. I've created a link in feathers-hooks-common feathers-plus/feathers-hooks-common#141

Member

eddyystop commented Mar 23, 2017

Just saw this now. I've created a link in feathers-hooks-common feathers-plus/feathers-hooks-common#141

@arve0

This comment has been minimized.

Show comment
Hide comment
@arve0

arve0 Jun 25, 2017

Contributor

Here is a fuzzy match for NeDB, it searches all properties case insensitive:

module.exports = function (options = {}) { // eslint-disable-line no-unused-vars
  return function (hook) {
    if (hook.params.query && hook.params.query.$search) {
      hook.params.query.$where = fuzzySearch(hook.params.query.$search)
      delete hook.params.query.$search
    }
    return hook
  }
}

/**
 * Returns a $where function for NeDB. The function search all
 * properties of objects and returns true if `str` is found in
 * one of the properties. Searching is not case sensitive.
 *
 * @param {string} str search for this string
 * @return {function}
 */
function fuzzySearch (str) {
  let r = new RegExp(str, 'i')

  return function () {
    for (let key in this) {
      // do not search _id and similar fields
      if (key[0] === '_' || !this.hasOwnProperty(key)) {
        continue
      }
      if (this[key].match(r)) {
        return true
      }
    }
    return false
  }
}

I actually found this to be a bit quicker than single property regex. With fuzzy search I got 47 ms average time for service.find(), versus ~68 ms for single field $regex. Times are average of 50 times loop over 7 different search terms, and the DB contains about 14000 rows, each object has four properties, all of them text. Only one property has a lengthy, 140 chars, text field. I guess the implementation is vulnerable to DOS attacks, but $regex should also be.

Contributor

arve0 commented Jun 25, 2017

Here is a fuzzy match for NeDB, it searches all properties case insensitive:

module.exports = function (options = {}) { // eslint-disable-line no-unused-vars
  return function (hook) {
    if (hook.params.query && hook.params.query.$search) {
      hook.params.query.$where = fuzzySearch(hook.params.query.$search)
      delete hook.params.query.$search
    }
    return hook
  }
}

/**
 * Returns a $where function for NeDB. The function search all
 * properties of objects and returns true if `str` is found in
 * one of the properties. Searching is not case sensitive.
 *
 * @param {string} str search for this string
 * @return {function}
 */
function fuzzySearch (str) {
  let r = new RegExp(str, 'i')

  return function () {
    for (let key in this) {
      // do not search _id and similar fields
      if (key[0] === '_' || !this.hasOwnProperty(key)) {
        continue
      }
      if (this[key].match(r)) {
        return true
      }
    }
    return false
  }
}

I actually found this to be a bit quicker than single property regex. With fuzzy search I got 47 ms average time for service.find(), versus ~68 ms for single field $regex. Times are average of 50 times loop over 7 different search terms, and the DB contains about 14000 rows, each object has four properties, all of them text. Only one property has a lengthy, 140 chars, text field. I guess the implementation is vulnerable to DOS attacks, but $regex should also be.

@arve0

This comment has been minimized.

Show comment
Hide comment
@arve0

arve0 Jun 26, 2017

Contributor

Based on my comment above I've made to plugins:

Contributor

arve0 commented Jun 26, 2017

Based on my comment above I've made to plugins:

arve0 added a commit to arve0/feathers-docs that referenced this issue Jun 26, 2017

Add note about searching documents
Ref feathersjs/feathers#334:

> We will add documentation for searching to the adapters individually.

ekryski added a commit to feathersjs/docs that referenced this issue Jul 24, 2017

Add note about searching documents (#707)
* Add note about searching documents

Ref feathersjs/feathers#334:

> We will add documentation for searching to the adapters individually.

* add REST example to $search section
@ulrichborchers

This comment has been minimized.

Show comment
Hide comment
@ulrichborchers

ulrichborchers Aug 1, 2018

Hi,

stumbled into this thread from querying.md:
https://github.com/feathersjs/docs/blob/master/api/databases/querying.md

... which is referring here.

Noticed a little mistake in the querying sample URI for the $in,$nin example.

If I am not mistaken it should be:
/messages?roomId[$in][]=2&roomId[$in][]=5

instead of:
/messages?roomId[$in]=2&roomId[$in]=5

because $in is an array in the query.

ulrichborchers commented Aug 1, 2018

Hi,

stumbled into this thread from querying.md:
https://github.com/feathersjs/docs/blob/master/api/databases/querying.md

... which is referring here.

Noticed a little mistake in the querying sample URI for the $in,$nin example.

If I am not mistaken it should be:
/messages?roomId[$in][]=2&roomId[$in][]=5

instead of:
/messages?roomId[$in]=2&roomId[$in]=5

because $in is an array in the query.

@sajov

This comment has been minimized.

Show comment
Hide comment
@sajov

sajov Aug 1, 2018

Hi Ulrich,

/messages?roomId[$in]=2&roomId[$in]=5

get parsed by qs into

/messages?roomId[$in][0]=2&roomId[$in][1]=5

see the option extended

sajov commented Aug 1, 2018

Hi Ulrich,

/messages?roomId[$in]=2&roomId[$in]=5

get parsed by qs into

/messages?roomId[$in][0]=2&roomId[$in][1]=5

see the option extended

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment