We have a database of ‘Job Posts’ (think Indeed) that have a list of career targets (like who the Post would interest).
On the user type, we have that same option set of interest that the user populates by selecting what types of role interests them (engineering etc).
Then, we have a newsfeed of posts that should only show Job Posts that match the career targets the current user has self selected.
So, we’re using a Do a Search For to pull the Job Posts (there are over 100k in the DB) which gets them pretty fast. However, the issue is using the advanced filter with Intersect With to filter out any Job Posts that don’t have career targets intersecting with the current users preferences. It’s taking 30+ seconds to evaluate and will only get slower as the database grows.
What is a more performant way to filter out these Job Posts that don’t match the user’s preference.
The best is to avoid : filter and use search constraints and moreover you are comparing list to list using intersect in advanced filter This becomes very inefficient as you say you have 100k records so switch to search constraints to pull only required records by filtering them on server side rather than your current approach which pulls all records and then compares list to list on client side.
One way to think about it is to think further into the user experience and use case of the functionality and how to properly structure the data to match the needs of the platform and it’s users.
If a single job post only ever has one career target (which in my opinion it should) then the task is super simple and requires absolutely no advanced filtering at all, since the job post has just one single career target (as I believe it should) you only need a constraint on your search as below…FYI, I use two different potential approaches for the User who can or can not have more than one career target (as they should since people need a broader net of options in order to find a job).
So if you decide that users should be narrower in their search, use the single preference and do career-target equals current users career target. If you decide that users need a wider net, and so should have a list of career targets, do the second and use career-target is in current users career-preference-list
If for some reason, you are adamant that a job posting needs to target more than one career preference (again, I feel it doesn’t), you can use an alternative approach of the advanced filter with the ‘intersect’ operator.
Is this overall meant to provide the user with the entire list of 100K jobs in the DB to filter through like a search results page, or is it for some backend process of matching and sending emails?
No matter what, this
is likely due to Bubble built in ‘lazy loading’ on repeating groups that makes it feel pretty fast as it shows almost instantly just the items needed to show the current view of repeating group as loaded with results, but scroll down and you’ll realize the entire list of 100K items is not already populated into the RG. If you were to want to test this out, on your repeating group just check the box to show all items immediately and you’ll get a truer sense of how long it takes for all 100K to actually be loaded.
If we set aside the Job Post side of things now (in an ideal world yes it would just have one target).
But, consider if the feed was a social network feed like Facebook, where there might be an advert that has multiple targeting options (interests), and the Facebook algorithm matches the two lists up (the list of interests on the in-feed ad and on the user).
How would one achieve that at scale? The fundamentals and reasoning there is the same for this app, which is why it’s unlikely we can change the Job Post to one interest.
Is there literally no alternative to the advanced filter with the ‘intersect’ operator.?
There is no alternative way to compare 2 lists directly, other than the ‘Intersects’ operator as an advanced filter. So this can only ever be done client-side (as Bubble has no way to do this directly on the database request), so is never a scalable option (although perfectly reasonable on smaller datasets).
The solution (in most cases) is to avoid using list fields on datatypes - use a separate datatype instead as a joining-table.
