Making filtered database apps

January 15, 2021 7:15 AM

I want to build a few apps (or websites, where applicable) that are databases that can be queried and then filtered. The filtered part is the key. Like Zappos: you search "snow boots", it returns you 2000 results, and then you click "blue" and it reloads only the blue snow boots, and then you click "size 8" and you get only the blue size 8 snowboots. What system do I need to learn to make this?

In some cases, I need my filtered results to have additional functionality, such as a music database where you can play previews of the final filtered list items. Or a plants database where you can look closely at the photo of the plant and then return back to the filtered list.

posted by xo to Computers & Internet (17 answers total)

I'm the farthest thing from knowledgable about databases, but I know enough to know that looking at SQL will give you a hundred different potential threads for exploration.

I say this only so that someone more knowledgable can come and say "Yes, and..."
posted by vocativecase at 7:22 AM on January 15, 2021

Look at "no code" open source platforms like Rintagi or Saltcorn. Another direction is a full programming approach like Ruby on Rails (which twitter famously started on, but I doubt there is much original code).
posted by sammyo at 7:38 AM on January 15, 2021

Airtable?
posted by oceano at 7:39 AM on January 15, 2021

Hi all, thanks for the answers so far. Please only recommend resources that you know, and have used, that have filtering functionality.
posted by xo at 7:43 AM on January 15, 2021

Looks like Saltcorn has filtering. (I haven't used this product, just wanted to point out that it does address what seems to be your main requirement.)
posted by number9dream at 8:00 AM on January 15, 2021

What you are talking about is often called faceted filtering or faceted search. The go to backend tool for this is ElasticSearch. When it returns the results of a query it can also return data about these additional filtering facets. It is common these days to take your items or products and load them into ElasticSearch and basically use that as your DB.
posted by bdc34 at 8:05 AM on January 15, 2021

SQL can absolutely do this. The fancier databases are necessary if you need to do this on a massive amount of data. But if you do not have a massive amount of data, SQL solutions (MySql, Postgres for example) are extremely mature, have a ton of functionality, and integrate with everything etc.
posted by wooh at 8:06 AM on January 15, 2021

The feature you're describing is not a specific type of technology or tool, so much as exactly as you described, the general practice of 'filtering applied to a search'. There are a multitude of ways to do it, and all depend on the other tools and technology you're using.
posted by so fucking future at 8:21 AM on January 15, 2021

I have something like this for personal use (not commercial) and internal personal use at work.

I use MS ACCESS, which has a built in relational database, which you can then programmatically query via SQL and display on websites with basic Apache CGI server and connect to single-user Android apps via programming in Eclipse. Eclipse is free, programming for Apple apps costs money.

I recommend this because you can build all your tables in MS ACCESS front end, and you can build queries with the GUI-display of MS ACCESS to get the basic idea of SQL querying for your database layer.

You can also manage your data in MS ACCESS rather than via SQL queries.

I find managing data and building tables, adding columns, indexes, etc within a relational database framework harder than building queries and displaying data.

Now this solution will not be scalable to many users, but once you get it built out you can easily port to a more robust database solution.


Also I have the same basic framework for a shopping database, plants database, MP3 music, MP4 video, home weather station, book (all at home) and SECRET FINANCIAL and PROJECT TRACKING at work.
posted by The_Vegetables at 8:21 AM on January 15, 2021

For your question before the fold, if you can get this info into an Excel spreadsheet, you can build this very easily in something like Tableau. The other stuff you'll probably need to build custom.
posted by sandmanwv at 8:24 AM on January 15, 2021

BTW, learning how to build what you are suggesting above is basically a university-level business information science degree program, or Management Information Systems (MIS), or whatever the particular university calls their business school-administered programming degree program.
posted by The_Vegetables at 8:31 AM on January 15, 2021

I think it would help to know if the original poster wants a turnkey solution or an approach/libraries to use when writing code themselves. In either case, it would be helpful to know what platform/environment they're working in. I think from the question and follow-on that they're looking for a turnkey solution that can drive a website and/or an API backend, but it's hard to know.

Regardless, agree that "faceted search" is the term for what OP is describing.
posted by Alterscape at 8:54 AM on January 15, 2021

I think it would help to know if the original poster wants a turnkey solution or an approach/libraries to use when writing code themselves. In either case, it would be helpful to know what platform/environment they're working in. I think from the question and follow-on that they're looking for a turnkey solution that can drive a website and/or an API backend, but it's hard to know.

I haven't found a turnkey solution for this functionality, so if you know of one, please tell me its name. I am also open to writing it myself, if that turns out to be the only way to customize it to my liking, or if the turnkey options are subscription-based.
posted by xo at 9:18 AM on January 15, 2021

Airtable, Google Sheets, and Excel can all do this in various ways. That's why people are asking what your use case is - if you want something for... members of your hobby club to see what resources are available, that's different from wanting a solution for your million-dollar online shoe store.
posted by sagc at 9:58 AM on January 15, 2021

Searchkit or ReactiveSearch might help. They are both open-source customizable search UIs that let you do search and faceting and connect to an Elasticsearch backend. I've only used Seachkit myself and as someone without any familiarity with Elasticsearch, I found it comprehensible enough to get working.
posted by RGD at 3:46 PM on January 15, 2021

Almost any sort of a database engine will be able to do the filtering.

But I suspect your question is more about the UI than the actual filtering. Maybe something like Amazon's interface, or a typical shop like shopify where you can keep adding filters?
posted by kschang at 7:10 PM on January 15, 2021

If you want to build websites, starting with a content management system like Drupal or WordPress can make your life much easier.

I'm a Drupal fan, myself, so I've got a link for you to a Facet API plug-in for Drupal that lets you do this sort of thing pretty painlessly. (There's also Facets, if you'd like a second choice for any reason.)

It seems likely that there's something similar for WordPress, but I'm not as familiar with WordPress plug-ins.

CMS tools like Drupal and WordPress tend to be free and open source, and often have built-in features to take care of other features you might also want, like managing user accounts or scheduling content to be posted later, all of which can really help you focus on your content rather than having to re-invent all the functionality.
posted by kristi at 12:02 PM on January 21, 2021