I’m working on a new bubble app that has the following tables:
Users, Products, UserProducts. I’ve created multiple users in the Users table, and also multiple products in the Products table, what I’m trying to achieve is when a user adds a product to their profile, I insert the user’s unique id and the product’s unique id into the UserProducts table. All that works fine, however when the user goes to their Dashboard, I want to display all the products they’ve added to their profile, and the way I did it is to create a repeating group of Products and then set the type to Products and the Data Source to Search for Products. And now in this Search I try to constrain to only Products that the user added and is recorded in the UserProducts table by adding a constraint to the search that says Search for Products where unique id = Search for UserProducts where user_id = Current User’s unique id. This second conditional works, however the first one doesn’t work, because bubble complains that Search for Products unique id is text and I’m comparing it to a list of texts that get returned from “Search for UserProducts where user_id = Current User’s unique id”
Does anyone know how to solve this? I know I can create a List of things on the user’s table and add products to the User’s List of things, but I don’t want to do it that way, I want to create a separate table to track each product separately.
I could do that, but doesn’t solve the problem that my Search for UserProducts is returning a list of texts and I’m comparing it to a Product’s unique id which is a text
Searching on unique ID only gives you the is equal to option, you need “is in”
I put a unique value into each record in another field in tables I need to do this with (normally with an auto numbering action to call off a sequential number I keep track of in another table whenever I create an instance in my table)
This means if you’re on users, you can do a search for
Products where Autoonumber is in
Do a search for userproducts’product’s autonumber
Where user is current user
If you already have a unique product code and a unique user id, you can use those. Otherwise you’ll have to back fill your data with something unique.
If you number your user products too it will give you more flexibility for later.
Technically… this is a many to many relationship. That’s why you need the intermediate table.
See above. This is a very inefficient way of doing it, both from a search and build perspective. This is not a Relational Database, we don’t need foreign keys.
Technically … you don’t need the intermediate table. It just makes things simpler. Again, if you treat Bubble’s database like MySQL then you end up creating constraints that don’t need to be there.
thanks for this! It’s exactly what I did last night!
I redid the schema for the UserProducts table and now have two new fields User which is of type user and Product which is of type Product and I’m inserting a user and product into that table and not unique IDs. And funny enough when I add things to the table Bubble is actually adding what it looks like to be the unique IDs itself, but ok able to search for Products in the UserProducts table just by constraining to User = Current User
Hey @NigelG thanks for the comments, just to clarify so I understand better…
I never put unique ids in fields except as part of inbuilt bubble functionality creating a field whose type is another thing.
I never put my auto numbers if I have them, into other tables as a foreign key. That’s what the inbuilt bubble functionality is for to make a field a type of another thing
I definitely have created all of my many to many relationships as intermediate tables.
I have, for example, a Character table and a Media table. And I have a Character Starred in Media table.
I could have put a “Media I starred in” list field in the characters table, or a “Characters in this Media” list field on the media table, and those would be the alternatives.
In theory, a character would only be in a few media, and a media would star only a few characters.
My use case would be that on a Pin, you can see a character, E.g, Buzz Lightyear. So now without this pin being related to a media, I know that this pin is related to Toy Story.
On my Media page, I can go to the Toy Story page and see all the characters who starred in it, and list the pins that I might want to collect as a result.
Is there any major reason I should NOT have gone down the intermediate table route?
