Related table doesn't appear


#1

I’m not able to detect a related table from a call here is what I am doing.
My data is in a MySQL db
From the API docs I run /tablename/_schema/{table_name}
The resulting response shows this:
“related”: [],

However this table is related to three other tables. The table structure is this
id | schedule_id | member_id | role_id
schedule_id is related to another table called “schedules” so it relates like this:
this table schedule_id = schedule table id

Since it doesn’t detect the related table I am unable to pull data from multiple tables.
Is there away I can manually tell the call that this table = another one? I’ve read on the docs that a stored procedure is the way to accomplish this but I thought maybe there was an easier way in 2.2

Ultimately I want to make one call and get data from more than two tables and indicate what data I’d like.

In an sql query it b something like this:
SELECT DISTINCT b.date_schedule, b.time_type, a.first_name, a.last_name, c.name
FROM dr_persons a
iNNER JOIN dr_schedules b
iNNER JOIN dr_types c

I saw there is a “related data when relationships don’t exist” post and it says it’s not possible but I’m not sure if it now is in 2.2 Any help would be great, thank you

UPDATE: I actually did this using data mesh. Virtual relations


#2

I am also facing same in google cloud platform. Dreamfactory version is 2.2.
Anyone find solution for this query???

Thanks in advance


#3

DreamFactory pulls the foreign key relationships from the database itself. If DreamFactory does not detect any relationships, you can create virtual relationships using the Schema editor.

http://wiki.dreamfactory.com/DreamFactory/Tutorials/Using_virtual_foreign_keys


#4

DreamFactory schema editor allows to create relationship with one to one. In my case, i have one parent and multiple child. All child table refer the same primary key column in parent table.

Example:
SELECT
p.people_id, p.firstname, p.lastname, pa.address, pa.city, pa.country, sa.address, sa.city, sa.country
FROM people AS p
LEFT JOIN primary_address AS pa ON p.people_id = pa.people_id
LEFT JOIN secondary_address AS sa ON p.people_id = sa.people_id;

How to make people_id as foreign key of both primary_address and secondary_address table?

Any Idea how I can apply this?

Thank you


#5

This is something you SHOULD be able to do, but I’m seeing some issues with it. Let me circle up with our engineering team and get back to you.