Get limited children based on query

Data Service
1

Hi All,

Hopefully a quick one.

I have a table called Bar, Bar has a column called openingTimes which has a 1:N relation to the OpeningTimes table.

The OpeningTimes table looks like this:

image
image944×349 10.8 KB

In my query I’m returning all Bars in a certain radius and want to just return a specific OpeningTimes row for a given day.
At the moment when I run my query it pulls back the Bar objects and all of their OpeningTimes rows not just the child I want.
I.e…:

DataQueryBuilder queryBuilder = DataQueryBuilder.create();
        String whereClause = "
distanceOnSphere('POINT(-0.10854542936716162 51.51638512966117)', location) < mi(1)
and openingTimes.weekDay = 7";
        queryBuilder.addRelated( "information" );
        queryBuilder.addRelated("openingTimes");
        queryBuilder.setRelationsDepth(1);
        queryBuilder.setWhereClause( whereClause );
return Backendless.Data.of( Bar.class ).find( queryBuilder );

I’m guessing this is because I’m using .addRelated("openingTimes"), so my question is, can I just return that one OpeningTimes object which is in my where clause? I attempted using queryBuilder.setRelationsPageSize(1); this did in fact did only pull back one OpeningTimes object, but it was the wrong one (didn’t care about my where clause).
Please bare in mind that I also need to still return the other addRelated Information object, but that’s fine as its 1:1.

2

Hi @Reece_Smith

We will look into this and get back to you as soon as possible.

Best Regards,
Maksym

3

Hi @Reece_Smith

The where clause is applied to the table Bar so

Backendless.Data.of(Bar.class).find(queryBuilder)

with

whereClause = "openingTimes.weekDay = 7"

will return the Bar objects that have at least one openingTimes record with weekDay value of 7.
Thus you filter the Bar objects that are not opened at 7th day.

There is no standard way to make “inner” where clause for child objects. The easiest way is to receive all OpeningTimes objects and then filter them on the client side.

If you want you can share more information about your case and tell us why do you need to receive exactly one OpeningTimes object. So we can suggest you the best solution for this.

Best Regards,
Maksym

4

Hi @Maksym_Khobotin2,

I wanted to filter the children as I didn’t want to have to send 7 objects for every bar object to the client, it will cause extra processing and bigger payloads being sent (say for example I needed 100 bar objects, that’s an additional 700 opening time objects).

It looks like it was discussed on this topic: Using where clause to filter a related table - #4 by Andriy_Konoz I think you guys committed to implementing something this year? Do you know if that is still the case?

5

Hi @Reece_Smith

We’ve created the internal issue BKNDLSS-20750 for this case and I will update you when it’s done.

Best Regards,
Maksym

6

Thanks Maksym