Forum Documentation Showcase Pricing Learn more

Data structure query

Hi,

I have a query about data structure. Imagine I have an app that allows a user to search for a hot drink based on a few criteria: a) drink type (coffee, tea, hot chocolate, other) b) cost c) distance from current location.

The search will return a list of drinks with name, coffee shop name, distance from location, cost, and the addresses will also be shown on a google map.

When you think about the data behind this, you can imagine a very large list of all drink items in all coffee shops, with one row per drinks item x coffee shop. However, as may of the coffee shops will be members of a chain of shops, it would be much better to have a database that has 2 tables:
a) a table showing a list of unique items per ‘coffee shop chain’ and
b) a table with a list of coffee shops which includes the field ‘coffee shop chain’

If a) had 5 items per chain and I have 10 chains each with 20 shops, then using one big table would need 5 x 10 x 20 = 1000 rows, while using the two tables gives table a) with 5 x 10 = 50 rows and table b) with 10 x 20 = 200 rows, so overall 250 rows. With real life data you can imagine the numbers would become very large for one big table.

Here is the question - how do I do the second method in Bubble? I can imagine how to do it in SQL with a query and where I have mapped the ‘coffee shop chain’ fields across the tables. But I do not see an option in the data tab where I can define such a link.

Many thanks

Simon

1 Like

Knowing how you would do it in a relational database, and then trying to force that onto “things” is, I think, quite common but can be a bit frustrating. Particularly as I would imagine the back end is something NoSQL like :smile:

I have to force my self into working out what my real “things” are (instead of tables) and what the “real” relationship is between those things. So model my nouns as business concepts, and … just set it up like I say it.

In this case, you could have a set of coffee chains, each with a list of stores (so of type, Store, and “this type is a list of elements” checked) and a list of available drinks (type, Drink, and a list again). Set up the Store first (with address etc) and Drinks (Name, Cost) and then embed these in Chain.

Or…Store has a Chain, and Available Drinks are embedded as a list in that.

But it does sound like your primary “thing” is the drink.

So… a list of drinks. Each of which has a “available_at_chain” field which is a list of chains. Then chains have locations.

Several ways of doing it, have a play and see what search parameters make sense.

TL;DR - I think you need to look at the “this type is a list of elements” function to do what you want to do.

1 Like

Thanks, this is helpful. It would help to know what exactly is powering the back end from a data perspective I guess.
I will let you know how I get on.

Simon,

If I understand your question correctly, It’s been something that I’ve been struggling with for quite some time. From having a SQL background I believe you’re basically asking how to perform a GROUP BY clause in your query and also implementing a function [like SUM() or COUNT()] on whatever specific data you’re selecting. To make your example for just sales by location a SQL query would look something like this:

SELECT LocationName, SUM(SaleAmount) FROM sales GROUP BY LocationName

I have not found a manner to accomplish this in Bubble with a single select. I hope there is a better way, but to do this (with the same example) I’d create a repeating group object with a data source of Search for Sales’s Location:Unique Elements. This essentially performs the “group by” operation. So the first text object in the repeating group is simply Current Cell’s text which is a list of distinct values. In this case the distinct location names.

From there each additional text field to display in the repeating group is a new search based on the Current Cell’s text. For example, to list total sales by location the next text field in the repeating group would be a Search of Sales SalesAmount:Summed where LocationName = the Current Cell’s text.

From there, you can repeat this logic of new searches where LocationName = the Current Cell’s text for each text object to display in the repeating group.

At least this is the way I have been doing it. It does not seem very efficient to me. That is, each object in the repeating group is performing it’s own independent query. The biggest limitation I see for this is it depends on a repeating group object being displayed; which I imagine takes more processing than calculating everything in the backend and simply displaying the results to the user. I’ve tried to do similar calculations through Workflow only, but have not been successful.

1 Like