Filter by distance from database

aart.st · 2024-10-02T22:00:13.993Z

I have 3 data types:

TypeA:

location: geographic address

TypeB:

center: geographic address
max_distance: number

TypeATypeB:

matching_a: TypeA
matching_b: TypeB

I need to run backend workflow which will be run after TypeA is created and will find TypeB which entries are within TypeB.max_distance from TypeA.location (or put in other words distance(TypeA.location, TypeB.center) < TypeB.max_distance) and create TypeATypeB entries with the filtered results (or their IDs).

I spent 2 hours on looking for a solution for filtering by distance but unsuccessfully. Anyone knows how to do it?

nico.dicagno · 2024-10-02T22:24:32.574Z

Slightly hacky - but this should work:
Testing | Bubble Editor

You can’t really pull this off with a single search, this will require as many searches as there are unique values for maxDistance.
Depending on your database, data volume, and how frequently this function will be called, you might want to look into creating a system to contraint the search on the 0_Initialiser endpoint so that only plausible Type_Bs are queried instead of querying all the database. Something like a country/region/city constraint or similar.

aart.st · 2024-10-03T07:59:46.450Z

I managed to solve it with the following workflow:

Database trigger event: When TypeA is midified
Schedule API Workflow on a list

List to run on: Do search for: TypeB AND TypeB:filtered (advanced): TypeB.center distance from TypeA.location < ThisTypeB.max_distance
parameters to pass to the next workflow: TypeA, ThisTypeB

Next workflow simply creates TypeC based on the received parameters

Man, was that a journey!

@nico.dicagno I think this search query isn’t heavy, what do you think? I expect my database to be relatively small so I think the search shouldn’t cause any workload/performance problems.

nico.dicagno · 2024-10-03T14:16:41.393Z

Advanced Filters should generally be avoided for this type of usecase, as they are very inefficient for long lists. Using an advanced filter essentially means that the Bubble server cannot summarise the expression into a single Search Query on the server, meaning that the Search will actually return all the entries (in your case this might mean querying the entire database), and only after that will the filter be ran, and with much more inefficient processes than a search constraint on the server.

For operations on the frontend, this means that the entire db is returned to the client. Not only will the operation take very long to evaluate, but the data load will cause jittery and sluggish behaviour on the browser, or even simply crash it outright.
If this were a Frontend operation, this type of usage of Advanced Filters would not be recommended.
In the Backend, this problem is less pronounced, because there is no client-side browser to crash. But the operation is still very inefficeint.

I have always avoided Advanced Filters, unless i know that the number of entries on which the filter is running is very low (<50). Frontend and backend alike. I know as a fact they need to be used with care in the frontend, however I have never ran proper tests of how inefficient they are on the backend. I’m almost certain they are still very inefficient and should be avoided. You could run some tests on this and let us know what you find.

If i find some time today i might run some tests myself, as I’m quite curious about the results.

aart.st · 2024-10-04T07:56:51.232Z

understood.

Could you please then explain in more detail what you meant by “0_Initialiser” or how to build the workflow with multiple searches?

nico.dicagno · 2024-10-04T13:25:09.314Z

Did you check out the Editor I linked on my first post?
0_initialiser is the name of one of the backend workflows in the editor.

aart.st · 2024-10-04T19:23:57.806Z

I missed it it helped a lot! Thank you!

Based on what you did I created the workflows:

on TypeA change

call create TypeC workflow on a list of TypeB

create TypeC workflow

when TypeA.location distance from TypeB.center <= TypeB.max_distance: create TypeC

system · 2024-12-11T22:00:33.157Z

This topic was automatically closed after 70 days. New replies are no longer allowed.