How to retrieve data with in related column in this senario

General
1

im having trouble understanding why this search doesn’t return data

i have a table A with a column type relation named B_link to table B

if i use this search query it will not return the expected result

B_link.objectId = 'test' AND B_link.objectId = 'test2'

even if there is a record in table A that have a relation with 2 records from table B

2

Hello @mohammad_altoiher!

The query B_link.objectId = 'test' AND B_link.objectId = 'test2' is trying to find a record where the B_link.objectId is both ‘test’ and ‘test2’ at the same time, which isn’t possible.
If you’re looking for records related to either ‘test’ or ‘test2’ in table B, you should use B_link.objectId = 'test' OR B_link.objectId = 'test2'.

Regards,
Alexander

3

but this with return records where only one B object in the relation

how can i return only the records in table A where there is both B objects in the relation? is there a solution?

4

Since SQL creates a table that is a combination of A and B, and then processes each record in the table, each record can never have two relations in a single row. It’s always a single related object. If ‘A’ has two related objects, then there will be two separate records in the result, not just one. To achieve a single record result, we use the group by operator.

Based on this, you can create a query using the REST Console as follows:

image
image3924×1902 475 KB

As a result, you will get the following URL:

data/A?where=B_link.objectId%3D'34A71742-9820-4EA4-A49A-95B249489835'%20or%20B_link.objectId%3D'D316E008-4348-450D-B0BC-748457B12B4D'&property=objectId&property=Count(%60objectId%60)%20as%20count&groupBy=%60objectId%60&having=count%3D2

Regards,
Alexander

1 Like