Forum Academy Marketplace Showcase Pricing Features

Still learning, still struggle (or criteria and multiple tables this time)

OK, so I’m still getting to grips with this, but the help here is invaluable. This time I want to return a search for items using an OR criteria. I can return a simple or using “merged with” and that’s all good (i.e. status = Pending or status = Ready) . What I want to do though is execute my query against one table and return rows from another, joined table.

My data structure is an Event. I have a User that owns the Event, but that User can also be a participant in an other User’s Event. So, I want to return the Event’s owned by the User and also the Events that they are a participant in.

I have an Event thing and I have a column on there of EventParticipants, which is a Participant thing. On here I have a column of User which is of type User.

The search criteria I want is Events where Owner = Current User or Events where Current User is present in the EventParticipants (i.e. all the Events that the Current User has an interest in).

I’m able to get the first part, and I know how to use merged with to bring the sets together, but I can’t work out how to get Events where EventParticipant’s User = Current User.

In database terms this is a union, so I’d be interested to also understand if it would be possible to create a union all. I don’t need it in this scenario, as a user won’t be a participant in their own even, but I can see cases where I may want to get a union all so I’d be interested to understand how to achieve this.

Can anyone help me understand?

Hi there, @tim.foster98… after reading your post, a few thoughts came to mind.

First, some questions… I’m sure you have a reason for having a Participant thing, but do you really need that thing? Participants are just users, so couldn’t the EventParticipants field simply be a list of Users? If you do need the Participant thing, do you really need the User field on it? Wouldn’t you be able to use Created by = Current User to get a particular Participant? Finally, is the Owner field on the Event data type necessary? If the owner of an event will always be the user who created the event, you could get a user’s events with Created by = Current User instead of using a separate field.

The reason I ask those questions is because, without knowing more about your app, it looks like you might be able to simplify the data structure a bit. No offense, of course, if it needs to be structured the way it is, and if it does, I think you could use an advanced filter to do what you have described. and I believe something like this might work…

The part on the left is showing the details associated with clicking on :filtered on the right, and note that there are no constraints on the second search for events. Also note that if you have never used an advanced filter before, the Advanced option is all the way at the bottom of the dropdown when you go to add a constraint to the filter.

Now, the above being said, keep in mind that advanced filters happen on the client side, so that second search will have to return all events to the browser and then filter them there, and that can be pretty slow. So, if you are able to simplify your data structure so you can use searches with constraints, you would almost certainly end up with a much better user experience from a performance perspective.

Anyway, I hope any/all of that makes sense, and I hope it helps.

Best…
Mike

1 Like

Thanks Mike, that’s really useful. You’re right, I probably could simplify the structure, and with the benefit of a better understanding before I started I could have made different decisions. I suspect I probably could get rid of the owner for the event and use Created By as you suggest. I still don’t fully understand how Bubble is structuring the data underneath so it’s hard for me to apply database knowledge and good practices. I would hope that it’s just an FK between users and events so a column of Owner is just a numeric FK referencing the Users table, but it sounds like you’re saying it’s not like that.

The client side filtering might be an issue as you mention, so it could be that I need to look at getting the data directly from my API instead. I’d hoped that I could do the entire development with Bubble, and I guess someone more experienced with Bubble could, but I’m constantly coming up against things that I can do in native code but can’t work out how to achieve the same thing with Bubble.

My plan has always been to integrate my existing database and utilise the structure that I had before discovering Bubble. That’s a SQL Server database exposing everything through APIs, as the plan was (and still is) to have a phone app.

I am persevering with Bubble as I think its a fantastic tool, especially to get a working solution up and running quickly. The development time using Bubble is spectacularly faster than native development, but something aren’t so intuitive.

1 Like