Retrieving children that does not fit in search request

Server Code Codeless
1

I am trying to get from a User’s perspective, all the services he has assigned to them. Since I cannot query from child to parent, I am going from the highest point and filtering down. This is just filler code for now with actual user variables coming later.
When searching by user’s name, i am receiving the entire service results where the serviceProvider has the name i want, but I can also see the other services (1:N) that do not belong to that user. How do I not get those extra results?

image

image
image701×400 55.5 KB

2

This is a wrong assumption. You can definitely query from child to parent.

As for getting relations, you are using the “having clause” which should be used only with aggregating functions.

Instead, merge the following two conditions together:

status != 'Closed'

and

opennings.services.serviceProvider.lastName = 'Hart'

so it will look like one single where clause:

status != 'Closed' and opennings.services.serviceProvider.lastName = 'Hart'

In the “relations” connector, since you’re asking to get the related services, I’d change it to “opennings.services”.

Regards,
Mark

3

Can you explain more on this? I’ve seen on other support tickets it was said that it was not possible. If i have the service details, how do I work up to the House table that it belongs to?

For the fix, I tried that in the beginning and had this same issue. I thought using the ‘having’ clause would fix it since Houses → Openings and Openings → Services are 1:N. Here is what I am receiving.

image
image650×681 76.3 KB

4

Any thoughts on this @mark-piller ?

5

For the fix, I tried that in the beginning and had this same issue. I thought using the ‘having’ clause would fix it since Houses → Openings and Openings → Services are 1:N. Here is what I am receiving.

The reason is you looking for Houses that fit your requirements - and it’s correct, you get one that has service with provider Hart, but you load ALL related service objects for that House.
If you want to get only one needed service you could:

  1. Manually filter current response.
  2. Make a request in the service table - not Houses.

Can you explain more about this?

Here you could find some information about it.

Also, I could offer you a request to the service table with next whereClause

Opening[services].Houses[openings].status != 'Closed' AND serviceProvider.name = 'Hart'

https://backendless.com/docs/rest/data_inverted_relation_retrieval.html

6

Regarding child-to-parent navigation. Suppose you have a specific service object. Here’s a query to get all the houses that indirectly reference the service:

openings.services.objectId = 'objectId of the service object goes here'

For the issue with the wrong serviceProvider getting pulled in, please let me know your application ID and I will give it a try.

Regards,
Mark

7

Thank you Dima and Mark.

I was trying to work on a filter for it, but being that the filter item is so deep and I still need all parent data, it’s beginning to confuse me. Thank you Mark for looking at this for me.

8

Hi Tyler,

The query with a condition to get Houses with the service provider having a specific last name gets you the houses that indeed have that related service provider. However, when you request relations, the service will return all related objects (not the ones constrained by the where clause). The reason is by design the where clause applies only to the primary table (Houses in this case).

A solution may be a two-step approach to get services:

  1. Get Houses objects without relations.
  2. Iterate over Houses and get related services with the following query:
Opening[services].Houses[openings].objectId = 'objectId-of-the-current-Houses-object' and serviceProvider.lastName = 'Hart'

Hope this helps.

Regards,
Mark

9

that sounds like it would severely impact performance. there could be 100,000+ houses

10

I would question the design from the UX perspective then. You want to load only as much data as you can fit on the screen.