I have a requirement on search and not sure how to implement it.
So, in a simplified version, my database is like this. People are present on different days at different stations and they need to go to pick-up points to perform some tasks.
People’s attendance
Person
Station
Station
Name
Location
Station to Pick-up Point Distances
Pick-up Point
Station
Distance between Pick-up Point and Station.
At times they need to go to pick-up points and I need to show which people are closest to those pick-up points based on which station they are at and within a certain distance.
Now I am doing a search for attendance show the people, but I need to sort them on the basis of “distances” which are in other table. I can keep a list of “stations” to know which order to show the attendance in, but when I do a “search for” on attendance, I do not know how to sort those out in the same order as the stations that I chose.
Pick-up points and stations are fixed, but I still can’t put all the distances in “attendance” data type as there are many people, it keep changing every day etc.
Was I able to explain it? Would be great if someone could guide on how to achieve this.
Sounds like you want :
A repeating group with the type Station, and source Do a search for Station to Pick-up Point Distances, with a constraint Pick-up Point = [your pickup point in question] sorted by Distance, then after the search do :each item's Station
Then have another repeating group inside that with the type Person, data source Do a search for People’s attendance with a constraint Station = Current cell's Station then after the search do :each item's Person
Sounds like a (kind of) crazy data structure so I might’ve butchered that whole thing
You’ve got it exactly right. Maybe I could have done it better to explain though.
What you have suggested is what I am doing right now. However, that seems like way too many queries. One repeating group to arrive at sorted list of stations and then in each cell of repeating group I do the search again.
What I have set up right now is I have a station repeating group like you suggested, and a separate group for all the people’s attendance which match the criteria of distance. And then I use “:filtered” in each cell of the station repeating group on the “all the people’s attendance” for current cell’s station.
I wondered if I could do it all in one query and get it sorted at the server level and thereby optimising the number of queries I have to do. Would that be possible?
About the crazy data structure: Would you propose a better way to structure this?
I forgot you want a certain distance away, can you just add a search constraint on the first repeating group Distance < [your max distance]?
What I suggested should be too bad for performance because there is no :filtered operators…
I know it seems like a lot of the same search but Bubble is pretty smart with caching searches once it’s done once (usually this is the case). Maybe try what I suggested and see if performance is any better?
Well I think the alternative is a repeating group doing the search outside, then inside the cell you refer to the repeating group's list:filtered but it sounds like that’s what you are already doing (probably performs the same as I suggested)
@keith 's plugin Floppy lets you do searches with crazy filters like calculating distances between a Station and Pick up, then sorting by that on-the-fly
@keith If they use ListShifter, couldn’t the intermediate datatype “Station to Pick-up Point Distances” go away since the distance could be calced and sorted by that calc?
I just checked out ListShifter’s sort functionality. Seems like a good functionality. Probably may not be optimal in my case as having to create another list with exact number of items to be used for sorting would be more processing in itself. This is because I can have multiple attendance records per station.
But this is really good to know that this functionality is available. Thanks.
Remember that Bubble is giga-smart with searching and caching, so maybe just setup all the different ways to handle this and see if it all just comes out the same
I am not saying that sorting by your plugin would be slower. I am saying that in my case the task of creating another list which has equal number of items and has the number to be used for sorting would be quite a task. (If I have understood functionality of the plugin correctly)
Like I mentioned, in my case there are multiple attendance record per station, and hence the issue.
I am searching for attendance records for certain stations. Attendance records have station as the field. And then I have separate data type for distances that I can use to create a sorted list of stations. But the length of that list won’t be same as the list of attendance records that I get. So I will now have to iterate through the attendance records that I got and prepare a list of items that have the distances mentioned in them to be used for sorting. To my simple mind that is a long process (for myself as well as probably for the server processing).
