Help needed with where clause - retrieve data for logged in user

[Louis Adekoya]

I have an appery.io database with three collections - Users, Group, GroupMember. A group can be active or inactive (active = true or false). A groupmember record has a group and a member (points to users) attribute. I want to retrieve and list for the logged in user all members of the active group to which the logged in user belongs. How can I achieve this? I assume I need to use one or more query services but I’m struggling with the where clause. I could do this easily with SQL but I’m not as familiar with NoSQL dbs. I have read the docs on database queries and in particular the section on pointers, but the penny hasn’t quite dropped for me unfortunately.

[Igor]

Hello,

This should help http://docs.appery.io/documentation/b...

[Louis Adekoya]

Thanks Igor. I had read this, it gets me close and I can successfully test to retrieve all groups for a member or all members for a group, but I can't quite work out how to restrict to active groups because the active column is in the group collection, not in the groupmember collection that I'm querying. Also, how to restrict the query dynamically to the logged in user, though I seem to be making more progress with this second problem (I hope).

[Igor]

[quote:]how to restrict to active groups[/quote]
You can save active groups to localStorage variable and use this value for GroupMember where clause request.
http://docs.appery.io/documentation/r...
[quote:]how to restrict the query dynamically to the logged in user[/quote] You could use ACL to restrict the query for user.
http://docs.appery.io/documentation/b...

[Louis Adekoya]

Igor, thanks again.

As I see it, I need two queries:

1. A query on the Group collection to find a single active group that the current user belongs to (a user can only belong to one active group at a time). For this I'm trying to use the query service for the Group collection but I need to include the GroupMember collection where the relationship is defined between user and group. I cannot yet see a way to do this. If I could find this group then could put it in a local variable as you suggest,

2. A query on the GroupMember collection to find all members of the group found above, as stored in the local variable. This I think I can do.

   Can you suggest a way please to achieve #1 above? It is the query itself that I seem to be struggling with. Or perhaps there is a better way altogether. One thing that might help actually, in the example of ToDo and Subtask (see below), does the Subtask point to the ToDo and is the column 'priority' on the ToDo collection or on Subtask?

   "curl -X GET
   -H "X-Appery-Database-Id: 526fdbd8e4b07c3286b537f1"
   -G --data-urlencode 'where={"subtask": {"$inQuery" : { "priority": "High"}}}'
   https://api.appery.io/rest/1/db/colle..."

   By the way, I don't think ACL will help me in any of this because as I understand it is about restricting access to only the user who created a record. In my case, users do not create or assign themselves to a group, this is done by a script.

[Maryna Brodina]

Hello! Sorry for delay. You can get everything using one request (more information is here http://docs.appery.io/documentation/b...). In your case request to GroupMember table should look like this pre{"groupPointer": {"$inQuery": {"active": true}}, "memberPointer": {"$inQuery": {"_id": "xxxxxxxxxxxxxxx"}}}/pre where groupPointer, memberPointer - pointers to Member, Group tables column names

[Louis Adekoya]

Hi Maryna, this looks good - many thanks. I will try it as soon as I get a chance. The group part works, I know because I got that far yesterday and it worked. For the member bit, I'll be looking to use the userid local variable (luser id for the logged in user).

[Kateryna Grynko]

Hi Louis,

Let us know if you need help.

[Louis Adekoya]

Thanks both. I'm so close now I think. I now just need the query above to return just the group id - nothing else. I thought this was what the Include feature was for but the response still shows data other than the id when I set Include to group.

Then I thought I'd try adding /distinct/group to the url (group is the pointer to the Group collection) but this returns an error "code":"BCXX002", "description":"Cannot consume content type".

I'm sure there must be a way to return just one column, but I'm either getting the method or syntax wrong.

[Louis Adekoya]

Actually this should be fine - I should be able to simply map the group ifd from the response to a local variable. But it would still be good to know if there is a way to return just one column.