Join 3 tables in API workflow

Stuck for two days on this one…

Three tables:

products: upc, photoURL, description
ratings: upc, whoLikes(_id from profile)
profile: _id, name

I need a workflow API that returns whoLikes name from profile based on the _id, along with the photoURL and description. Any ideas?

You don’t need to implement Foreign Keys in Bubble.

So you would (assuming this is many-to-many) have a list of ratings on the product table.

And instead of _id you would have a field of type “profile”.

Then you just list of out the product’s list of rating’s profile’names.

Thank you @NigelG. Problem is permissions - the product table has to be able to be viewed by all in order to do lookups. If the ratings are stored in the products table the permissions would allow all users to view all ratings - even those not created by current user.

Can’t you add that as a constraint ?

Not sure. Can constraints be applied to specific columns on a table instead of the entire table?

You can just restrict the list of ratings that you show to be ones created by this user.

ratings field is added to products table.
all users need to be able to access upc, photoURL, and description in order to do look ups
none of the products would be created by a user
if a rating field is modified then only the last user that modified would have access to that row.
perhaps I’m misunderstanding your advice?
is there not a simple way to do joins in an SQL-ish way?

Hi, does anyone have a solution for this?
I want a workflow API (data API would do as well) to return a LIST with fields taken from each of the two different tables(things) that are related (thing 1 is a field in thing 2).

It’s a typical case for some reference kind of data - e.g where descriptions of some items are stored in a separate table and we want them to be shown in the list of these items.