How to join tables and do a sum?

Hi everyone -

I’m relatively new to Bubble and working on a practice project to get my feet wet. I have searched through the initial Academy tutorials and can’t find a solution to the following problem.

I have 3 tables:
a) User-Type: E-mail, Password, List of Votes
b) Vote: Question, List of Answers (Text)
c) Question: Question (Text)

For each question, a user can submit a list of answers. Each answer basically is a vote for that answer. The user can come up with their own answers as well. For example:

Question: Favorite fruit?

  • one user could enter “Apple”
  • another user could enter “Pear”

The issue I’m having is how to construct this. In a SQL database I could just join the tables together and sort by the number of votes.

What is the best way to tackle this as I don’t quite see a path forward.

Thanks
Vincent

Go to the Data tab. Inside User, create a new field of type Question, and another field of type Vote. Similarly, you can create Answer and put a list of Answers inside Vote. Bubble takes care of all the PK/FK automatically and behind the scenes.

Thanks for the helpful response. My questions in the above example is

  1. How would I show a repeating group of all voted answers to a question that a descending sum of all the votes to a particular answer?

  2. For example, if my question is “Best fruit”. User 1 has a vote that they created called “Apple”, User 2 has “Apple”, User 3 has “Pear”. I want to show
    Apple - 2
    Pear - 1

Right now the votes reside within the users. How would I go through all the users to sum up all the votes for a particular question?

Thanks
Vincent

Use constraints with “Do a search for” or perform :filter on a list. I encourage you to not think in conventional DB/SQL terms, as you might complicate things.

“Do a search for Answers” with constraint Answer text = apple and use :count to get a count of all apple answers. I’m sure there are other ways based on grouping, etc as well.

If you haven’t done so, I strongly encourage you to review Bubble’s tutorials where they create clones of various popular sites to see how to access, search, filter, and manipulate data. https://bubble.io/how-to-build

Spending a couple days on those will save you countless hours of replicating the wheel.

Got it and thank you for the color. I’ll look at those how-to-build tutorials.

Helpful to hear about not thinking in SQL context.