Filter is not applied on count() about loopback-connector-mysql HOT 18 CLOSED

The count() method only takes the where clause instead of the filter. Your REST URL should be:

http://localhost:3000/api/malts/count?where[maltName][like]=%25ap%25

from loopback-connector-mysql.

+1 this needs better documentation

from loopback-connector-mysql.

+1 to needing this better documented

from loopback-connector-mysql.

The count() method only takes the where clause instead of the filter. Your REST URL should be:

http://localhost:3000/api/malts/count?where[maltName][like]=%25ap%25

I don't know how to count with two filters, it works only with one, can you help me please?
I try with that:
http://localhost:3000/Users/count?where[profileC][ilike]=%25Designer%25[additionalSkillsC][ilike]=%25figma%25

from loopback-connector-mysql.

This is being fixed by #32.

from loopback-connector-mysql.

Fixed by [email protected]

from loopback-connector-mysql.

Unfortunately, this is not fixed neither v1.2.3 nor v1.3.0.

from loopback-connector-mysql.

How did you call the count()? The 1st arg should be an object for filter.where, see:
https://github.com/strongloop/loopback-connector-mysql/blob/master/test/mysql.test.js#L299

from loopback-connector-mysql.

I call it thru REST API. I do it on a listing page, and after having called find(), I call count() with the same filter settings to create a pagination control:

http://localhost:3000/api/malts?filter[fields][maltKey]=1&filter[fields][maltName]=1&filter[limit]=10&filter[order]=maltName+ASC&filter[skip]=0&filter[where][maltName][like]=%25ap%25

http://localhost:3000/api/malts/count?filter[fields][maltKey]=1&filter[fields][maltName]=1&filter[limit]=10&filter[order]=maltName+ASC&filter[skip]=0&filter[where][maltName][like]=%25ap%25

The resulting SQL statements on the database:

SELECT malt_key, malt_name FROM malt WHERE malt_name LIKE '%ap%' ORDER BY malt_name ASC LIMIT 10

SELECT count(*) as cnt FROM malt

The last one sould be the following:

SELECT count(*) as cnt FROM malt WHERE malt_name LIKE '%ap%'

The fields, order, limit and skip arguments are ignored, that is right.

*** It would be a nice feature if find() would have an includeTotalCount Boolean argument, and it optionally would return the total item count using the filter settings.

from loopback-connector-mysql.

O.K. This way it works in deed. However, the documentation is rather gappy, and sometimes confusing. E.g. in Model REST API, at Find first instance section it says the argument is filter. On the other hand, in LoopBack reference, DataModel class, at findOne() it says the params is a where object.

from loopback-connector-mysql.

@marmarosi @jamesw6811 Please create a new issue with specifics (ie. why the docs are bad, what do you need to see, which parts need improvement, etc) so that we can get it assigned to @crandmck to get it fixed. We do not take action on comments in closed issues.

from loopback-connector-mysql.

The count() method only takes the where clause instead of the filter

@raymondfeng We should probably add an alias functions that take a filter and do the same thing. Probably something to add for my LB3 juggler proposal.

from loopback-connector-mysql.

Hey @cfjedimaster I tried to clarify Where clause for queries and Where clause for other methods about the where clause for count(). LMK if it can be clarified further or somewhere else.

from loopback-connector-mysql.

@crandmck The where object is the same for all methods. What's different is that some methods (such as count) take a where object directly while others (such as find) take a filter that can contain where. The filter object also supports other child items such as limit and order.

from loopback-connector-mysql.

OK, but we went through this whole fire-drill some time ago to describe the difference in the syntax (which we document and give examples of) because sometimes the where clause is wrapped in {where: ... } and sometimes not.

The docs say:

For query methods such as find(), findOrCreate(), or findOne(), use {where: {property: value}}

and

... for methods that update and delete (and count()), don't wrap the where clause in a { where : ... }, ... so use {property: value}

I just tested that myself and it seems to be accurate.

I think perhaps it amounts to the same thing, but we're saying it in different ways. Perhaps your way of describing it makes more sense...?

from loopback-connector-mysql.

You are right. The key is that find/findOne takes a filter object while count/deleteAll/updateAll takes a where object.

from loopback-connector-mysql.

It kinda makes sense now - to be honest, I probably would have stopped reading before "Where clause for other methods". Honestly, it seems weird and arbitrary a bit as well - but I'm still new to LB.

One suggestion. The first doc section includes REST API examples, the second does. It should, shouldn't it?

from loopback-connector-mysql.

The count() method only takes the where clause instead of the filter. Your REST URL should be:
http://localhost:3000/api/malts/count?where[maltName][like]=%25ap%25

I don't know how to count with two filters, it works only with one, can you help me please?
I try with that:
http://localhost:3000/Users/count?where[profileC][ilike]=%25Designer%25[additionalSkillsC][ilike]=%25figma%25

@philip13

Hello dear,
I was in the same case as you.

To apply multiple filters just add a "and" property with an array of your "conditions"

{
   "or":[
      {
         "statusId":"5"
      },
      {
         "statusId":"6"
      },
      {
         "statusId":"7"
      }
   ]
}

from loopback-connector-mysql.