scoutforpets / bookshelf-jsonapi-params Goto Github PK

Hi!
Trying to use the plugin and can't understand what i'm doing wrong with hasMany.

export const Roles = pg.Model.extend({
  tableName: 'roles',
  users() {
    return this.hasMany(Users);
  }
});

export const Users = pg.Model.extend({
  tableName: 'users',
  roles() {
    return this.hasMany(Roles);
  }
});

await Users.forge().fetchJsonApi({
    fields: {
      users: ['id', 'firstName', 'lastName'],
    },
    include: ['roles']
  });

I got:

  error: column roles.user_id does not exist

The JSON API spec requires that requests for a single record are returned as an object. Alternately, requests for collections, even if the collection only contains a single records, should be returned as an array.

Unfortunately, there isn't a great way to detect whether data coming into the plugin needs to call fetch or fetchAll depending on whether the user is requesting a single record or collection. I tried a few hacky things looking at Model#attributes and the Model#query._statements, but alas I broke it a few times.

Need a better way to determine this. May just revert to a flag that should be passed to the method.

I was banging my head against the wall and then I had the bright idea to add a slash to the end of my URL. This fixed the issue with filtering not working with node-jsonapi-query-parser. Any thoughts on a permanent fix?

Hello,

I am trying to use RLS system on my app.
for that some of my queries should be in a same transaction.

with fetch, fetchAll, I done it without issue
so after I see the code of the fetchJsonApi, I supposed that there is no issue with standard fetch option, like : {transacting: t}, it is supported.

However, it seems that work not well, I get :
unrecognized configuration parameter app.userid
when I try to make a find query with SET app.userid TO ${userId}

So my database uses snakecase, however I'm outputting camelcase with the jsonapi-mapper package. So when I do ?fields[users]=orgId, it does not work since it expects org_id. Is there an option for this, or could I change it plugin so I don't have to convert the case every time?

Say for example you make a call like this:

PetModel.forge().fetchJsonApi({
  sort: ['petOwner.age', 'id']
});

You would get an error from your SQL database. This is because when internals.buildSort calls formatColumnNames the iteration has undesired behavior. On the first iteration of formatColumnNames, line 630 of src/index.js correctly sets columnNames[0] to 'petOwner.age'. On the second iteration however, line 651 modifies all columnNames via Bookshelf's Model.format. The resulting columnNames are thus ['pet_owner.age', 'id'], which raises this error at the database level.

select "pets".* from "pets" left outer join "pet_owners" as "petOwner" on "pets"."pet_owner_id" = "petOwner"."id" order by "pet_owner"."age" asc, "pets"."id" asc - missing FROM-clause entry for table "pet_owner"

I am getting around this problem now on a forked version by switching line 651 to be:

columnNames[key] = _keys(this.format({ [value]: undefined }))[0];

instead of what it is now which incorrectly modifies all indexes of columnNames

columnNames = _keys(this.format(columns));

When I get the cycles I will likely modify internals.formatColumnNames to formulate the columnNames using a map instead of the forEach loop which modifies indexes of the existing array and write a test case for with a relationship like petOwner that has a name which would be affected by this issue.

I've been playing a little bit with the filtering feature which is in beta, using this module + https://github.com/kideh88/node-jsonapi-query-parser

For the most part, it seems to work pretty well, which is awesome 👍

However, I'm having a problem using like.

E.g. A plain eq filter works just fine

/users?filter[email][email protected]

However if I change it to

/users?filter[like][email][email protected]

I get an error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."email") like LOWER('%@myaddress.com%')) limit 10' at line 1

The query that is being output is:

select count(distinct users.id) from users where (LOWER("users"."email") like LOWER('%@myaddress.com%'));

The problem is that whilst double quotes are correct syntax for postgres and sqlite3, this is not valid MySQL syntax. MySQL uses backticks.

There are some issues in knex around this:

• knex/knex#872
• knex/knex#723
• knex/knex#1761

The solution seems to be to use a private API, taken from knex/knex#723 (comment)

var formatter = new query.client.Formatter(); // Also this works too: new.knex.client.Formatter()
var columnName = formatter.columnize('member.name');

Hi guys, thanks for the wonderful plugin 👍 !
I want to trigger the NotFoundError of bookshelf. I normally did this by adding { require: true } parameter. Now if I add this to the object that comes out of the node-jsonapi-query-parser, I can't check it conditionally with if (error instanceof User.NotFoundError) { (I can do a regex though). Is there a quick fix for this? Thanks again!

@alechirsch I know we had briefly discussed a possible API to handle the case when you want to filter on x OR y rather than x AND y.

Want to restart that discussion here and figure out an API?

Stack:

• hapi
• bookshelf
• MySQL

controllers/driver.js

'use strict';
var server = require( '../server' );
var bookshelf = require( '../bookshelf' );

var Driver = bookshelf.Model.extend( {
    tableName: 'Drivers'
} );

exports.index = server.route(
    {
        method: 'GET',
        path: '/drivers',
        config: {
            handler: function( request, reply ){
                Driver
                    .forge()
                    .fetchJsonApi()
                    .then((drivers) => {
                        reply(drivers);
                    });
            }
        }
    }
);

My response/reply is:

[
  {
    "id": "1",
    "first_name": "Name",
    "last_name": "Name",
    "license_number": "123456",
    "createdAt": "2016-07-05T16:00:00.000Z",
    "updatedAt": "2016-07-05T16:00:00.000Z"
  },
  {
    "id": "2",
    "first_name": "Name",
    "last_name": "Name",
    "license_number": "123456",
    "createdAt": "2016-07-05T16:00:00.000Z",
    "updatedAt": "2016-07-05T16:00:00.000Z"
  }
]

Expecting the data object/array.

I fully accept that I'm probably doing something wrong, but the only thing I feel like I'm doing differently is a reply() instead of return. Any thoughts?

#59

Format the columns of aggregate function not the entire expression.

For example with the case-converter-plugin:

First:
count(personId) -> count_person_id

Now:
count(personId) -> count(person_id)

Hi,

We have a model that has a hasMany relation. For example:

const Tree = Bookshelf.Model.extend({
  ...,
});

const Forest = Bookshelf.Model.extend({
  trees: function() {
    return this.hasMany('Tree');
  },
  ...,
});

If we now make a query like this:

Forest.fetchJsonApi({
  filter: {
    'trees.name': 'maple',
  },
  page: {
    page: 1,
    pageSize: 12,
  },
});

we expect to get the first 12 forests that have maple trees inside them. In reality we get less than 12, as it seems that either:

• first the rows are fetched and then the filter is applied or
• the pageSize parameter refers to the number of trees and not to the number of forests as the join operation results in more rows being returned than there are forests due to the hasMany relationship.

Could you point us to a solution? We are willing to also contribute a PR, but would be grateful for some help.

Thanks in advance and best regards!

I'm running into a problem when trying to filter relations. In Bookshelf, I would normally run the following query which should hopefully help to illustrate what I'm trying to do:

await new Speaker().fetchAll({withRelated: [
	{
		"events": function(qb) {
			qb.where("format", "=", 1);
		}
	}
]});

Selecting fields for a relation seems to work in the following way:

/speakers?include=events&fields[events]=name

So I tried a similar approach for the above:

/speakers?include=events&filters[events][format]=1

But this doesn't work. I've also tried a few other things, but no luck so far. So I'm wondering if what I'm trying to do is even possible with this package?

It looks like this this has already been raised in the past, and as far as I can tell, a fix was applied (#16) – but I'm not sure if this is fact working (and whether the above syntax is correct).

Any help would be greatly appreciated as I'm a bit stuck! Let me know if you need me to provide any additional info :)

You recommend using the node-jsonapi-query-parser module and that the produced object can be passed directly into the plugin without modification; however, when attempting to do so, nothing whatsoever happens... no filtering, nada. Any ideas? Your help is greatly appreciated!

Currently, the plugin only supports filtering on the top level model. For example, for a person that has many pets, you can only sort on fields that belong to person. If you wanted to find only people that have dogs, you cannot do something like GET /people?filter[pet.species]=dog.

When using the include parameter, the plugin currently creates a withRelated array based on the values of the parameter and passes it to fetch/fetchAll. In some cases, you may want to do something more advanced by passing a query builder expression.

We can check the options hash to determine if a withRelated function already exists for the included relation and use it rather than adding a generic param.

We've been experiencing issues with sort for a while, and haven't been able to figure out what was wrong.

Yesterday, we ran some tests, and determined the behaviour is as follows:

If you use sort descending, e.g. sort: ['-id'], it works the first time you run the query, and therefore passes tests / appears to work. However all subsequent requests go back to sorting by ascended.

I believe the culprit is the line of code value = value.substr(1);

In context:

// Loop through each sort value
_forEach(sortValues, (value) => {
    // If the sort value is descending, remove the dash
    if (value.indexOf('-') === 0){
        value = value.substr(1);
    }
    // Add relations to the relationHash
    internals.buildDependenciesHelper(value, relationHash);
});

If the sort value has a - at the start, it is overwritten without it.

I think this is a pass-by-reference error, where the option is getting overwritten so that it no longer has the - on subsequent passes.

Not 100% sure if this is the culprit, but the behaviour indicates that something like this is happening.

Hi, is the package currently support like operator statement, e.g., name like %john%.

Currently, I define the like operator as contains, so the URL would become like

/users?filter[email][contains]='gmail'

Then, I separated out that filter and build where statements manually based on that (I build a bookshelf plugin for this purpose). It would be really great if it is actually supported by bookshelf-jsonapi-params directly.

Pretty sure the issue is with this repo, rather than https://github.com/kideh88/node-jsonapi-query-parser.

Try to filter for null values doesn't work. If I try to set something like filter[deleted_at]=null in the query string, I get an empty result set.

If I try to set the filter programmatically before calling bookshelf-jsonapi-params, e.g.

options.filter = {
    deleted_at: null
};

I get this error:

TypeError: Cannot read property 'toString' of null
    at /Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/dist/index.js:385:50
    at /Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/node_modules/lodash/lodash.js:4411:15
    at baseForOwn (/Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/node_modules/lodash/lodash.js:2654:24)
    at /Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/node_modules/lodash/lodash.js:4380:18
    at forEach (/Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/node_modules/lodash/lodash.js:8634:14)
    at QueryBuilder.<anonymous> (/Users/hannah/Ghost/org/Daisy.js/node_modules/express-bookshelf-jsonapi/node_modules/bookshelf-jsonapi-params/dist/index.js:310:41)
    at Object.query (/Users/hannah/Ghost/org/Daisy.js/node_modules/bookshelf/lib/helpers.js:56:14)
    at query (/Users/hannah/Ghost/org/Daisy.js/node_modules/bookshelf/lib/model.js:1206:30)

Should this work? is there a special syntax that I should use?

A little stuck here. Getting ready to go live with my API using MySQL and I noticed that the filter query always returns the first result in the collection. Any thoughts?

Hey, great plugin!

I'm trying to use the like filter but it doesn't work on postgresql jsonb columns

select "mytable".* from "mytable" where (LOWER("mytable"."jsoncolumn") like LOWER($1)) - function lower(jsonb) does not exist

The request is generated by:

qbWhere.where(
    Bookshelf.knex.raw(`LOWER(${formatedKey}) like LOWER(?), [`%${typeValue}%`])
);

In postgresql, adding a ::text does the job:

select "mytable".*" from "mytable" where LOWER(("mytable"."jsoncolumn")::text) like LOWER('%test%');

Is there a way this could become a feature?

Given the below filter:

{
  active: true,
  or: [
   {
     account_id: 1
   },
   {
     group_id: 5
   }
  ]
}

Currently the generated SQL for the where clause will produce this:

where
  active=true
  or ( account_id=1) or (group_id=5)

The generated SQL should instead look like this:

where
  active=true
  and ( ( account_id=1) or (group_id=5) ) 

Related to #63

@massimo-ua I found this when trying to use the or filter feature, I will be looking into a fix for this. Can you confirm your intentions when building out the feature? I do not want to break anything for you. Everything outside of the or array should be and'ed together.

Currently, the plugin only supports sorting on the top level model. For example, for a person that has many pets, you can only sort on fields that belong to person. If for some reason you wanted to sort person by the weight of their pets, you cannot do something like GET /people?sort=pet.weight.

See #14 for a deeper discussion.

Although there is a basic test suite in place, it doesn't test combinations of different types of parameters. For example, there should be tests to cover requests like: GET /customers/1?include=pets&fields[pet]=name where sparse fieldsets are applied to included relationships.

As we have some data in nested JSONB columns, we use the additionalQuery to add select and ordering for these columns, we need to do this manually is because we need put null values last, which (afaik) is not possible with the JsonApiParams. However, this introduces certain complications, such as also needing to inject your own select clauses for the columns we want to sort on.

Is there a way we can define to sort the nulls last?

As I see this plugin doens't support filitering columns with datatype json. Latest versions of postgres support json data type columns so I think it would be nice if this plugin also supported filtering for columns with data type json.

A sample of filtering would be
filter[column_json][property_1]=example
or
filter[like][column_json][property_1]=exam

Hi,

The bookshelf-jsonapi-params generates a query which failes on postgres. Maybe it's a special case, because we are using the postgres schema.
The problem is on these lines. The joinTableName also contains the schema. Because the alias ("as") does not distinguish the schema it interprets the name as "roles_custom.roles_users". But the third argument distinguish the schema and interprets the name as "roles_custom"."roles_users"."role_id".
That's why postgres throws the error missing FROM-clause entry for table "roles_users".

My question: Is the alias in the join statement needed?
If I remove the aliases the code works as expected.

Here is an example to reproduce

const knex = require('knex')
const Bookshelf = require('bookshelf')
const bookshelfJsonApiParams = require('bookshelf-jsonapi-params')

const db = knex({
  client: 'pg',
  connection: {
    database: 'test',
    host: 'localhost',
    password: 'test',
    port: 5432,
    user: 'test'
  }
})

const bookshelf = new Bookshelf(db)

bookshelf.plugin(bookshelfJsonApiParams, {
  pagination: {
    limit: 25
  }
})

class Roles extends bookshelf.Model {
  get tableName() {
    return 'custom.roles'
  }

  users() {
    return this.belongsToMany('User', 'custom.roles_users', 'user_id', 'role_id')
  }
}

class Users extends bookshelf.Model {
  get tableName() {
    return 'custom.users'
  }

  roles() {
    return this.belongsToMany('Role', 'custom.roles_users', 'role_id', 'user_id')
  }
}

bookshelf.model('Role', Roles)
const User = bookshelf.model('User', Users)

new User()
  .fetchJsonApi({
    filter: {
      'roles.id': '1'
    },
    include: ['roles']
  })
  .then(console.log)
  .catch(console.error)

Here is the full error

error: select "custom"."users".* from "custom"."users" left outer join "custom"."roles_users" as "roles_custom.roles_users" on "custom"."users"."id" = "roles_custom"."roles_users"."role_id" left outer join "custom"."roles" as "roles" on "roles_custom"."roles_users"."user_id" = "roles"."id" where "roles"."id" in ($1) limit $2 - missing FROM-clause entry for table "roles_users"
    at Parser.parseErrorMessage (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 123,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '145',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3301',
  routine: 'errorMissingRTE'
}

When calling something like GET /customers/1?page[limit]=10&page[offset]=0, although a specific resource is being requested, paging parameters are still being applied to the query. This isn't having any adverse effects as of yet, but it's unnecessary.

We are seeing invalid results when passing in a descending sort argument. Basically it ignores this part of the JSON API Spec: 'The sort order for each sort field MUST be ascending unless it is prefixed with a minus (U+002D HYPHEN-MINUS, “-“), in which case it MUST be descending.' Sort arguments prefixed with a minus are not being properly processed.

We think you could resolve it in the buildSort function. You could to check for sort params passed with a minus (meaning descending sort), store the sort direction, remove the minus before formatting the arguments and call internals.model.orderBy() with the secondary optional argument supplying the sort direction you saved earlier.

Does this repo name make sense?

Currently the package allows for page[limit] and page[offset], however it does not work with page[number] and page[size]. I took a look at the 'bookshelf-page' package and found that they use 'page' and 'pageSize', however these parameters do not seem to work either. This is probably an issue with the 'bookshelf-page' package rather than this package.