How to call the get api by join to join tables?

Schema & Data
1

I have the following tables schema in my mysql database

751×390 16.9 KB

I’m trying to join these three tables from tw_venue_services to get the specific fields as below the sql query

SELECT tvs.venue_id,ts.name,ts.normal_price,tmd.duration,tvs.added_date,tvs.updated_date FROM tw_venue_services AS tvs
INNER JOIN tw_service AS ts ON tvs.service_id = ts.service_id
INNER JOIN tw_meta_duration AS tmd ON tmd.duration_id = ts.duration_id
WHERE tvs.venue_id = 96

output should be

I wrote the api calling as

http://localhost:81/api/v2/treat_well/_table/tw_venue_services?related=tw_service_by_service_id%2Ctw_meta_duration_by_duration_id

But it’s not listed the duration joining table info. why it’s ? what i have missed? @juniorconte

2

Hello, it would be possible to request from the service?

http://192.168.254.28/api/v2/treat_well/_table/tw_service?related=tw_meta_duration_by_duration_id,tw_venue_services_by_service_id

If not, I’m afraid you need a Stored Procedure or a Script post_proccess, which may impair the performance of the request.

best regards,

3

If i need to join from tw_service then it should be appropriate to call the api as you are said…

Actually i need to join from tw_venue_service table as i mentioned in sql query… Can’t we do in direct api call for this kind of scenario without going to store proc or scripting??

If not please tell me the best way with reference?..

5

Not sure if this answers your question exactly, but there are several join related sql tutorials in the documentation: https://wiki.dreamfactory.com/DreamFactory/Tutorials#SQL-Specific_Data

If these methods don’t address your concern, then you will need to use a stored procedure or script.