Hi,
I have 2 tables: TableA, TableB.
TableA include relation field 1:N with TableB.
So I can get information from TableB…
[
{
Item_from_A: {
"items_from_B": []
}
}
]
now, I need way to send backward query:
send query to TableB and get related information from TableA and I want to get something like this:
[
{
Item_from_B: {
"item_from_A": {}
}
}
]
How I should describe this relation in the query?
or there is only one way is create a duplicate relation 1:1 in table B…
Please advise correct flow.
Hi Sergey,
Your question is a bit incorrect: if you only have a relation from A to B, then you can’t get a structure { B -> A }, only { A -> B }.
I’ll try to rephrase your request, please correct me if I get it wrong: you either need to fetch all TableA entries which relate to a particular TableB entry or you need to fetch TableA entries which relate to any of TableB entries.
So, in the first case you can use the following where clause (assuming tableB is a relation column):
tableB.objectId = NEEDED_OBJECT_ID
and in the second case essentially what you need to do is select TableA entries which have a not null relation:
tableB.objectId IS NOT NULL
thanks for the answer.
but no, I need to be able to receive information in both directions in different requests.
If I understand your answer correctly, I should duplicate the keys, right?
so I can’t use relation TableA->TableB from TableB, please confirm.
because it’s looks wrong to have the same key twice(1:1 in one table and 1:N in second, that duplicate the same relation)…
No you can’t, because no such relation exists internally either.
But you can query TableB objects while specifying its parent object’s properties, like the following:
tableB.someOwnProp = 'xxx' AND TableA[tableBrelation].someParentProp = 'yyy'
(in case it helps in some way)
you are talk about “where” statement, but data is still empty.
please advise how to join it.
f.e. currently we have
tableA, tableB, and relation: tableA.tableBrelation = tableA->tableB
tableB does not have any relation with tableA.
our goal is retrieve a list of result from tableB that include tableA record.
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setRelated("tablea", "tableA.tableBrelation");
Backendless.Data.of(tableB.class).find(queryBuilder);
This code(above) return only records for tableB and ignore “relation”, so tableBRecord does not include TableA related record.
I don’t think that it will change something if I’ll add the “where” statement:
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setRelated("tablea", "tableA.tableBrelation");
String whereClause = "TableA[tableA.tableBrelation].objectId=TableB.objectId"
queryBuilder.setWhereClause(whereClause);
Backendless.Data.of(tableB.class).find(queryBuilder);
or maybe I understand your answer wrong, please advise.
This code(above) return only records for tableB and ignore "relation", so tableBRecord does not include TableA related record.
Your tableBRecord cannot have any other fields than you have in TableB as columns. So I suppose your only option will be to create an additional 1:1 relation column in TableB to TableA.
I think it will cause a loop in autoload mode, no?
tableB->include tableA->include tableB->…->include tableB
or if set queryOptions.relationsDepth=2… then I will don’t get information from other related tables…
maybe there is some other way?
The other way is to rethink and redesign your data model. 
For example, you might have only B to A relation and workaround the one you already have (A to B).
By the way, this approach won’t cause a loop because relationsDepth is hardly restricted at 10, thus you’ll get only 10 levels of relations. But of course it might greatly influence the perfomance, because you’ll have to load much more unnecessary data.
not possible, it’s 2 different lists and representation of the information.
That’s why I need a classic sql “join” flow for this with pager 
The alternative to a classic sql join in this case will be to select all TableA records with related TableB records (which will be the one use-case), and then filter unique TableB records for the second use-case.
For example, you might have only B to A relation and workaround the one you already have (A to B).