Best practices for querying several tables at once - JOIN

Hey Folks,

I have 2 databases :

  • Events
  • Users

An event has a field “created by” (which is the user email).

On a specific page, when I query each event, I would like to display dynamically :
" [User’s restaurantName] - [event date] " in same text field (with the rich editor).

For now, I’m using a repeating group for each event. I display two text input :

  • one for the event date
  • one for the user’s restaurant, where I do a “Do search for”.

So I’m wondering if I could get all those information at once.
It looks like a JOIN on mysql.


You can’t do SQL-like joins with Bubble’s database so you can’t get data back in one straight list from two different tables. In your case, if the amount of data isn’t huge, rather than doing a search for… on each row/cell, you can set a dynamic expression for the restaurant name element of “Current Cell’s Event’s User’s restaurantName”. If you’re pulling down a lot of data in the list, as this is causing Bubble to perform nested searches which will hit performance, you may want to reorganise your data so that fields from linked tables which are regularly displayed in lists are duplicated in the main type eg events so that you’re not having to do a lot of nested searches within an RG.

Coming from a SQL background myself, it can be difficult to get your head around the fact that Bubble doesn’t operate like a SQL database but once you learn to work with it rather than try to make it work like a SQL database then it’s becomes easier.

Every data type entry in Bubble has a ‘created by field’ which is related to the User data type.

You can do, current cells creator/created by (I forget which way bubble labeled it as they didn’t do it consistently across the board) and from that you get all data for the User data entry of the user that created the event.

As other reply mentions, there are performance implications so you may just want to have a field on event data type of text which is restaurant name.

For me it’s not a problem to duplicate data through different database. I also use dynamodb everyday.
The thing is when the “main data” changes, we need to synchronize the change everywhere.
As developer, I know how to do it :smiley: but not specially with Bubble for instance.

But I will start with duplication, I’ll see this part about replicating a modification later!

Thanks to both of you !

To synch duplicated data and fields, you can do it manually eg add another action to your saving workflow for Change a thing … or, my preference, is to use DB Triggers at the backend to automate, where you have a backend workflow when a record or a field that you want to duplicate is updated. Check out the article below by @petter Amlie which gives a great explainer on using DB (backend) triggers.

Nice ! Good to know, thanks a lot.

Btw, I have another “issue” to optimize.

I list a plenty of “opened” events on a page.
My user can subscribe to the event. When he subscribes, I create a “Request” in my table “Request” which is linked to the event and to the current user.

But how can I list only the events where the user has not yet subscribed ?
Again, I found a way which is not really optimized (it scares me when we will have a lot of data) :
I do a query in a “conditional” to show the event or not.

Meaning :
1 - Getting all the events with the “open” status
2 - for each event, attach a “conditional” where we “Do search” to check if the event has been already subscribed by the user … :roll_eyes: