Query with join


#1

Hi, I’m new on Dreamfactory and I’m searching a solution to my problem.

I have a MySQL db with no relations between tables defined at database level but it’s for me necessary to do some queries with join. I can’t create table views (I can only read data on this db).

How can I do this with Dreamfactory ? If it’s possible, there is an example ?

Thanks in advance for any help.
Danar


#2

Hi, you’re able to create a stored procedure and call that to perform joins on a mysql db… You simply create the procedure and then call it using a GET via the live api.

If you want to study up on this, familiarize yourself with how exactly the users/roles system works. Because this has an important impact on knowing what you’re giving your users access to when simply doing a GET on a stored proc. Effectively, data can be manipulated through the stored proc without even giving the user delete/update access to that rest verb.

Thanks,
Mark


#3

Thanks for you reply.
I try to do as you told me.

Danar


#4

Mark,

Is there any particular reason you are recommending the creation of a stored procedure instead of just creating a view that has a join?


#5

The OP said he could not create views.


#6

One possible reason I could think of is you can pass variables to the procedure and find your query to work on them . If its a view , the filtering could be done on top of reocrd retrievals only ( as far as I have discovered DS , I didnt find a way of passing variables - either bind or literal to the query).

Does that make sense ?


#7

Would it not be easier to use DataMesh
like described in the Blogpost below ?

Virtual Relationships Between Database Tables
Posted by Lee Hicks
Thu, Feb 4, 2016