How do I filter a repeating group based on values in different table? Please guide me how to set up my database.
Currently I have a table called product (producttype, price, latitude, longitude, etc.)
Also have a another table called preference (user, producttype, minprice, maxprice, max distance, etc)
Based on above database structure, how do I search a product showing in the repeating group with those filter from preference table?
So a user, has a list of product type in his type field such as microwave, refrigerator. He also has min price = 100 and max price = 500, but no max distance.
However, there will be another user who has different preference: max distance = 500, no min price, but has max price.
How do I create such filter? Can my table structure work? should I modify it?
@NigelG any idea? I saw you had responded to similar question before.
Do Users have a Preference associated to them? I see that âPreferenceâ has a user associated to it, but is the Preference also attached to the user?
Iâm trying to understand, you are asking if the table user table has a field linking to the preference table? No it doesnât. Should it be added? So both tables have links to each other? Would it also work if a user has multiple preferences? Meaning, each user may have multipe records in the preference table.
As this is quite complex in terms of data it would be good to post a link to your app.
It would be simpler if the preferences were on the User table, but if you want multiple preferences per user then it is worth have a reference on both the User (type Preference, tick the âIs a listâ box) and on Preference (type User).
Yes, you can have a âPreferencesâ field on the User data type that is a list of Preferences.
I like normalised tables so I tend not to do two-way links like this unless there is no other way. As a possible alternative, you should note that you can also search within a search or filter your search. So your RG data source can be a search of Products/ProductTypes and then filtered according to the current userâs preferences.
@louisadekoya I noticed thereâs a :filtered option in the repeating group data source. Being new I have never seen this and need to grasp what it does.
It looks like you âsearch for productâ then this one is filtered from the drop down. then you also add :filter option to further filter, based on maxprice. if you want to add more preference, add a new constraint under that dialog box?
how does it work when a user leave the âmaxpriceâ column empty? will it ignore that clause?
also I donât see table user and preference link to each other in your example. maybe we donât have to?
Bubble can be a little weird with null values in searches, so I think you should try it out and see.
I have only done a one-way link (the preferences table has a user field). I did this to illustrate that that the two-way link may not be necessary.
By the way, I only used the filtering option because with regular search constraints, the less than or equal to operator (â¤) is not available, but it is with filter constraints. If there are more constraints, you can add them to the original search constraints for the RG, so you have a smaller set of records to filter on.
Generally speaking, is filtering done client-side in Bubble - i.e. After the data are fetched from database? (Just trying to get my head around the Bubble-specific terminology.)
ao I created this page⌠I have no clude how to filter the list.
I was able to create the dropdown on the top of the page, but donât know how to link them to repeating group. canât just filter by preference name from the drop down as other use may have the same preference name. it needs to llok for current userâs preference name, find all the fields associated with itâŚ
I think your app is in private mode, so I canât access it.
I donât think you want to filter by preference name - you want to filter by preference. From your description of your data structure, a preference belongs to a user, so each preference in your dropdown should belong to a specific user, therefore if you filter the RG by the selected preference, you should get products matching the owning userâs preference.
@shot62, Iâm not certain but I think youâre correct that this is how it works.
If you can see I was able to use dropdownâs valueâs minprice, dropdown valueâs maxprice etc.
As you may notice I am trying to calc distance of product and filter based on the preference table. Thatâs where I am stuck⌠how do I calculate distance (user has lat long, product also has lat long), then how to filter it?
currently I could calculate based on my current geo location, but not userâs location given its lat / long.
perhaps I shouldâve stored actual address instead of lat/long?
you can test my app by run as (select user) then on index thereâs product list button
Oh, Iâm afraid I havenât done anything with maps/location in Bubble. A forum search for âdistance betweenâ yields some interesting looking results that you may want to check out though.
OK. I think I have figured it out the filter. This is what I have found:
If filter is numeric, value canât be NULL, but text can be NULL
if filter is TEXT, it is case sensitive â if product type is âNewâ, then filter needs to be âNewâ. ânewâ will not work.
to calculate distance using lat / log, you have to convert lat long to address first by using the âcalculate coord to addressâ option for the origin. then choose distance from, then do 'calculate coord to address again for the destination.
