Accessing data via reference links - easy in Bubble, how do we do this in Wappler?

[mgaussie]

Hi all,

In Bubble, let’s say I had this scenario:

user_db - this contains only fname, lname, email and password
user_information - this contains avatar image, about me info, and some other data

In bubble, I would create a db entry in user_db that references user_unformation and vice versa.

To get to a users avatar image to display on a profile I would simply do this in the workflow:

get user_db’s user_informations ‘avatar’ - it was super easy to get unique data that was linked through db’s based on the users_id etc.

I can’t seem to find an obvious way for me to do something like this in Wappler. It seems if I wanted to get both users_id db data as well as user_unformation data - I’d need to create two separate server connects, put both under ‘app’ and then call that information that way?

[sitestreet]

I’ve not used Bubble so will try and steer you based on your description.

If you have two tables where they’re ‘joined’ by an id then you should create a query in Server Connect, start with the user_db and then add the user_information joining it with the userid field or whatever you have called it. This will give you a result where you have all the fields together to use as needed.

[mebeingken]

Definitely not two server connects! Bubble was merely performing a JOIN in the query. You might want to take a look at some of the videos by Caleb Curry: https://www.youtube.com/watch?v=zhu6jtlghIs&t=681s

In Wappler, you just add another table to your query. In the query builder hit the plus sign to add another table, and then select the field that connects the two.

Here’s an example:

Let’s get all users with a car:

[mgaussie]

Ok, got it - that makes sense. So not additional tables in ‘steps’ but in the actual query builder.

In this scenario I’d probably need 3 tables connected.

The first is fine, it populated a join option id = user_ud. The third didn’t present that option.

Can you advise if best practise is to join with inner, left or right?

Thank you - I will view those video’s too!

[mebeingken]

It’s not really a matter of which join is better to use, they just do different things.

In the example above it shows an INNER join which says give me all the users that have a car. In other words, don’t give me the user, if they don’t have a car.

On the other hand, a LEFT JOIN would say, give me all users, and if they have a car show that too, but even if they don’t have a car, give me the user.

[mgaussie]

Thank you Ken, that’s clear and this has been super helpful, I understand how to do this in Wappler now

[mgaussie]

@mebeingken quick question - I have a table that has a link to another table, let’s call it user_id. I’m using a form to input a new row of data to table 2 that references the user table. What I’m stuck on - do I take the security.id user_id and input that into the reference user_id cell? I’m getting errors trying to do this, telling me not a valid integer input - this is how you do it in bubble, but i’m wondering if the reference cell should be left alone, and I create a separate field that stores the user_id?

[mebeingken]

Sorry, not following this one. But the security id is for the logged in user and you can use the identity that results from an insert as a link to another table.

[mgaussie]

Thank you, this is helpful - I checked the ‘output’ for an insert and this presented the identity option.

[acedesigns]

Just resurrecting this old thread as I’m sure what I’m trying to do is simple, but I just can’t work out how to do it.

In my ‘users’ table, I created a ‘client_id’ reference field which is linked to the ‘clients’ table. I then created a ‘user’ API action as per the tutorial to get the details of the logged in user.

On my page, I am using this ‘user’ API action which displays the user’s email address as well as the id of the ‘client_id’ field correctly.

But instead of displaying the ‘client_id’ key value (i.e. 2), how do I get it display another column in the ‘clients’ table (e.g. name - ‘Org XYZ2’)?

As the OP mentioned, in Bubble, this is easy to access in-line. But I already have a ‘user’ API where I can access the user’s data, so I couldn’t see how I’d go about adding a JOIN to the existing ‘user’ database query.

[Teodor]

Hello, does your database query return the rest of the columns like name and short_name?

[acedesigns]

@Teodor

Hello, does your database query return the rest of the columns like name and short_name ?

I just followed the tutorial for adding the security provider so I don’t see any columns added.

Also, the fields name and short_name are columns in the reference ‘clients’ table, not the ‘users’ table.

[Teodor]

So why not join the tables to get the results you need?

[acedesigns]

@Teodor - I’m still new to databases and constructing queries, so I still need to learn more about this.

But I didn’t realise how easy it was to create a join (by just adding the table to the query) and I can select the ‘clients’ name column from the dynamic data picker now. Thanks heaps for your assistance!