Skip to content
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

Nested And / Or Statements Fail #85

Closed
MattNewberry opened this issue Sep 27, 2018 · 14 comments

Comments

Projects
None yet
3 participants
@MattNewberry
Copy link

commented Sep 27, 2018

Describe the bug
When creating nested AND / OR statements for WHERE, the search fails.

Example
Works, but does not match my desired query:

[
    ['account_id', '=', this.account.id],
    'AND', 
    ['name', 'LIKE', `%${this.searchQuery}%`]
]

Does not work:

[
    ['account_id', '=', this.account.id],
    'AND', 
    [
        ['name', 'LIKE', `%${this.searchQuery}%`]
    ]
]

Does not work, but matches the final query desired:

[
    ['account_id', '=', this.account.id],
    'AND', 
    [
  	['name', 'LIKE', `%${this.searchQuery}%`],
  	'OR',
  	['vin', 'LIKE', `%${this.searchQuery}%`]
    ]
]

Expected behavior
I would expect nested operators to correctly resolve themselves. For example, I would expect the final code example above to resolve to a similar SQL statement as:

SELECT * FROM Vehicles WHERE account_id = 1 AND (name LIKE "%chevy%" OR vin LIKE "%chevy%")

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 2, 2018

Could you provide your data models so I can test this?

@ClickSimply ClickSimply added the bug label Oct 2, 2018

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 3, 2018

Thanks for helping debug this!

Here's a simplified version of the data model, but it includes the relevant fields:

nSQL('Vehicles')
.model([
    { key: 'id',              type: 'int',     props: ['pk']   },
    { key: 'account_id',      type: 'int',     props: ['idx']  }, 
    { key: 'name',            type: 'string' 		       },
    { key: 'license_plate',   type: 'string'                   },
    { key: 'vin',             type: 'string'                   }
]);

I don't assume there to be anything out of the ordinary with this model, and please let me know if I can provide any further details.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 9, 2018

@ClickSimply Any updates on this? This is a blocker currently and I'm facing removing the library and starting over with another framework.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 10, 2018

I've narrowed the problem down to an issue with the secondary indexes, should be fixed in the next day or so.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 10, 2018

This should be resolved as of v1.7.7, just pushed it to NPM

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 11, 2018

Thanks! I can confirm this fixes the issue.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 11, 2018

Actually - When not using a nested where like: ['account_id', '=', 18], I'm now seeing duplicate results returned. I've verified that duplicates do not exist in the database.

Duplicate results are however fixed when making the filter a nested array

[
   ['account_id', '=', 18]
]

@MattNewberry MattNewberry reopened this Oct 11, 2018

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 12, 2018

I'm having trouble recreating the new bug as described, here's a codepen. https://codepen.io/clicksimply/pen/xyrQOK

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 16, 2018

Hey Matt, just curious if this is still an issue or if it can be closed.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 16, 2018

I'll have to double check my local code, then, but it does indeed appear to be working for you. Happy to close and review my local codebase again.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 20, 2018

@ClickSimply I've been able to identify this as an issue specifically with the cordova-plugin-nano-sqlite. It works as expected in the browser (as demonstrated by your codepen example above), however when running on an actual device or iOS simulator the issue can be reproduced. Yesterday, for example, I had several unexpected issues on devices that were all solved by wrapping the where() with extra [].

Does not work:

[
  ['inspection_form_id', '=', form.id],
  'AND',
  ['vehicle_id', '=', form.schedule.vehicleId]
]

Does work:

[
  [
    ['inspection_form_id', '=', form.id],
    'AND',
    ['vehicle_id', '=', form.schedule.vehicleId]
  ]
]

@MattNewberry MattNewberry reopened this Nov 20, 2018

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Dec 16, 2018

This should be resolved as of NanoSQL v1.8.0 and Cordova/SQLite3 adapters v1.6.0. You may need to export your databases and reimport them, the issue was due to a type conflict with the primary key of the SQLite database.

@plentylife

This comment has been minimized.

Copy link

commented Mar 31, 2019

I'm using the 2.1.9 version and something very similar is happening
So this query returns nothing:
[[['userId', '=', 'user-10'], 'AND', ['cardId', '=', '1']], 'AND', ['status', '=', 'promised']]

but this one returns expected rows
[['userId', '=', 'user-10'], 'AND', ['cardId', '=', '1'], 'AND', ['status', '=', 'promised']]

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Apr 1, 2019

2.2.4 was just released, should be good to go now!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.