Getting Related Data Query

Schema & Data
1

Let’s say I have the following 3 Tables:-

A
a_id
b_id (Foreign Key from Table B)

B
b_id
c_id (Foreign Key from Table C)

C
c_id
c_name

Is there a way in which while fetching the rows of Table A, I can also fetch the related rows from Table C? I know I can fetch the rows from Table B by using the related property.
But due to normalization issues my data is lying in table C, which I need.

Since I can’t fetch it directly now, I have to fire additional query based on Table B.

I know there are options like View, Stored Proc, SQL Query, Server Side filter that I may be able to use.
However, what I am looking for is to fetch C based on the related field using the REST API call.

Help is appreciated !

2

Sorry but there’s no way to do this in a single call using the API.

3

Thanks Todd,

Is there a way for me to restrict the fields in Table B, i.e the related table.
I know I can restrict the returned fields in Table A.

4

Use relationshipname.fields=fieldlist

See “Getting the Related Data” here:

https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Related-Data

If parent = account and child = contact it would be

GET /rest/db/account?related=contacts_by_account_id&contacts_by_account_id.fields=id

1 Like
5

This may have been true in the past, but in 2.0, unless I’m mistaken, you can use the related field to get rows in table C, as long as you have your ‘related’ schema in A defined correctly (using junction tables). It needs to have an entry in ‘related’, which specifies the junction_* fields.
E.g.
’
{
“alias”: null,
“name”: “table_c_by_table_b”,
…
“junction_service”: null,
“junction_service_id”: null,
“junction_table”: “B”,
“junction_field”: “c_id”,
“junction_ref_field”: “c_id”
}
'
Apologies for bumping up an old topic. but when I searched for the issue, this was the first topic on the search list. Others may search for this and get the old answer.

2 Likes
6

Hi can you clarify a little more on this? I have a similar scenario and I don’t seem to be able to pull it off.
I have three tables
Table 1
Table 2
Table 3

Table 1 and 2 has a related table
Table 2 and 3 I has a related table.
But how do I get Table 1 to 3 to relate when there is no relatable column?

1 Like
8

Hey @omar_Guzman I would recommend that you would use DreamFactory’s virtual foreign keys.

Let me know how it goes!

Thanks,
@alexbowen

9

Hi @AlexBowen I tried that but getting from table 1 to table 2 is the part I’m having a challenge with.
The problem I’m having with is that table 1 and table 3 don’t have a matching column I can use as a foreign key. The scenario I have is where table 2 is like a “through” table where that can link to both. How would I go about it in this scenario?

1 Like
10

Can you share the schema of your tables to clarify the question?

In the past I’ve sometimes had to use a through table (if I’m understanding you correctly.)
I made the through table the parent in the relationships. And it just had 3 columns. 1 a record id (because I always start there), 2 a parent child relationship to the first table, and 3, a parent child relationship to the third table. So it doesn’t matter that 1 and 3 don’t have matching columns. You just have to create the records in 2 to match up the relationships. Then you can get records by calling Table 2 with related=* to see all the data related to one another.

1 Like
11

Ok this makes sense. Thank you I will try it.

12

One question thou. Is there away I can filter the results of the related table?

13

Related data filtering is not available, but you could accomplish this with a script. Just requires a bit of logic processing.

1 Like