query over multiple items

[Jason Cheek]

I want a query to return the list of users where all records associated with their id satisfy some requirement.

For example, given this simple data set:
id | data
1 d
2 a
1 c
3 c
3 b

How can I query to get the list of ids where data is never 'c'? A simple query of data not 'c' would return [1, 2, 3], but I would only want to receive [2].

Is there a simpler way than querying for all instances where data IS 'c', and then inverting the id list by performing a second query for all ids $nin the first list?

[maxkatz]

http://docs.appery.io/documentation/b...

Check the "not contained in" query.

[Jason Cheek]

That does not quite seem to do what I need. I need to find all ids which are never matched with the data value 'c'.

Wouldn't a query of '{"data": {"$nin": ["c"]}}' return:
1 d
2 a
3 b

But I only want to receive:
2 a

because both ids 1 and 3 each are matched with 'c' in at least one record.

A less abstract example would be, given a table recording users past addresses in two rows:
name | address
so a user with three past addresses would have 3 rows, each listing their name and one of the addresses.

How can I query to get the list of people who have never lived in Cleveland, OH?

[Illya Stepanov]

Hi -- You can use :: http://docs.appery.io/documentation/b...
with Logical AND ($and).

[Jason Cheek]

I must be missing something. I don't see how I can use an $and and $or to solve this.

For the following sample dataset,

name city
Illya Portland
Illya Cleveland
Illya Seattle
Jason Portland
Jason Reno
Jason Minneapolis
Max Chicago
Max Cleveland
Max Reno

How can I query for users who have never lived in "Portland"? A simple query where={'name': {'$nin': ['Portland']}} would return many records:

Illya Cleveland
Illya Seattle
Jason Reno
...

But all I want returned is 'Max', because Max is the only one without 'Portland' listed in ANY record.

What query would you use?

[Jason Cheek]

If it helps, I am working on a scheduling app where each user will have a list of appointments each with a start and end datetimes. And then given a new appointment with a start and end datetime, I need find which users do not have scheduling conflicts.

My plan is to store the appointments in a table with 3 columns:
name | start | end

[Kateryna Grynko]

Hi Jason,

I'm afraid there is no simple solution as Appery.io uses non-relational database. You can use one service to choose all users with Portland, and another service to filter them.

[Jason Cheek]

I was afraid it would need to be a 2 query process. Think you.

What is the underlying database? It doesn't act like SQL or Cassandra (the two I've used in the past).

[Maryna Brodina]

Hello! It's MongoDB.

[Jason Cheek]

Thank you