Filtering SQL Connector Data

Questions
1

Hey All -

First off - really enjoying bubble so far! My background is more on the backend of things - Familiar with SQL/VB/JS/Scripting languages like python/AHK. But putting a full stack together with a front end quickly is just…too much work sometimes! Bubble seems like a good in between to tie alot of things together.

Im using bubble to pull in data from a MySQL DB hosted on AWS(This is working fine), my question is related to filtering the data after its brought into the repeating group for display.

I think its working fine, but I’m not sure if using the :filtered psuedo element each time its being applied so that the query needs to execute again? Or does bubble apply the filter to the list that has already been generated from the sql data?

I hope that makes sense?

My goal is to be able to query my DB like:
“SELECT * FROM Contacts WHERE FirstLastName LIKE ? LIMIT 200”

Populate a Repeating group from the above - and then based on dropdowns/input fields etc…further filter down the data, without re-querying the DB each time.

*Side Note - I was able to build a pagination feature in the case that i have more than 25 results(In order to keep queries fast) - which I was pretty satisfied about :slight_smile: without using procedures or anything crazy. just some SQL, and auto increment ID’s per each row.

SELECT * FROM (SELECT *
FROM Companies
WHERE com LIKE ? AND
CASE
    WHEN ?='true' THEN id > ?
    else id < ?
END
ORDER BY
CASE WHEN ? ='true' THEN id END ASC,
CASE WHEN ? ='false' THEN id END DESC
LIMIT 25) As AliasedSearch ORDER BY id ASC

Then used States to track the :first-item & :last-item repeat groups id’s to pass back into the next query!

Thanks all!

2

Nice pagination concept. Thanks! As for filtering, this is done on the list once generated and does not execute the query again. In fact it won’t execute the query again at all unless you force it to using a fake time field or other changing filed in the SQL connector’s parameters. So it will only rerun if something in the parameters has changed since the last query run, when I last checked.

3

Bubble applies the filter, but if your data changes your repeating group doesn’t automatically retrieve the current data. If you search the forum, you’ll find a few posts on this. You need to add an extra parameter to trick Bubble into thinking it needs to get fresh data. I like how you did the pagination. I’ve done similar with sql queries and it works great.

4

Awesome! Thanks for the replies guys.

So - Ive gotten it all working nicely, but I’ve noticed something interesting:

Say I load the first 25 - then next 25, and then go to previous 25. The query returns almost instantly. Is that a feature of my DB, or bubble doing some sort of caching? Not that im complaining…

GIF:
https://i.imgur.com/i0q3NUB.gif

Cheers!