How can I search a table for a count condition?

[mano]

Hi All,

I have a table of “Items” that each “Item” is owned by a different “User”.
I would like to return a list of all “Users” who own more than 50 “Items”.

How would you approach that?

Thank you,
Mano

[boston85719]

After you create your dynamic data there are lots of choices to select from when you press “more” after the dynamic data. One of those choices is :count which counts the number of those items.

So you do a search for users:

Constraint: item list count > 50

[mano]

“Items” is a separate table - it doesn’t live within “Users” table.

[boston85719]

Then you should make a relation between those two tables in your database to have a better ability to get to the data you want.

Using a relational database structure so you can get to any data from any path is essential to making life easier for data manipulation / extraction and retrieval.

If you are not familiar with database structure and relational databases, you can search the forum or online for more information.

Basically a data type is like a “table” and a data field is like an entry in that “table”. You can have a data field be a data type so as to create the relationship.

In my app I would have a data type of User and a data type of item

Then in my data type of item I would have a data field of user to relate to the user data type

In my data type of user I would have a data field that is a list of items to relate to the item data type.

Then I would be able to search the table for a count condition.

[mano]

I don’t like to include a list of items in a table. I think it will create problems down the road.
I prefer to point from each “Item” to the “User” who owns it.

[mano]

I eventually solved it by running a search on Users and using filter: with an Advanced: condition.
In the advanced condition, I did a search on the Items table for each User separately, then run :count>50

[boston85719]

@mano

I eventually solved it by running a search on Users and using filter: with an Advanced: condition.
In the advanced condition, I did a search on the Items table for each User separately, then run :count>50

I am glad you solved the issue. In bubble you will find there are usually a multitude of ways to make something work as you would like.

As a word of caution, your approach is going to slow down the app and cause issues down the road.

Take a look at three of the many posts on the forum on performance, database structure and use of search, filters and sorts…pay attention to the advice surrounding the use of the “advanced” function as well.

One-to-many-to-many-to-many performance

Let’s say I have the following relationships for datatypes A, B, C and D. A: List of B B: List of C, Single A C: List of D, Single B D: Single B, Single C If I request A will it also retrieve data in cascade from C and D? Or will it stop at B data?

Bubble best practices: structuring your database (by AirDev)

Hi Bubblers, Wanted to share a new resource that I hope you find helpful. We at AirDev believe that Bubble will create a huge number of developers who haven’t necessarily built software before. As a result, we spend a lot of time thinking on how to train them in the best practices of building, which is especially important given how flexible Bubble is. As anyone who’s Bubbled extensively knows, there is a big gap between just building something on Bubble and building it well, using best design…

Performance Q&A guide

I’m going to be checking this thread periodically over the next week to answer questions on performance best practices, inspired by (among other people who have asked for this): This will probably get turned into a performance section in the manual, but I figure the forum is a good place to kick things off. I have to run for now but will chime in later today with some general tips + answers to blueback09’s questions above.

Also, could you expand on this:

@mano

I don’t like to include a list of items in a table. I think it will create problems down the road.

What kind of problems do you expect? If it is an issue of making sure workflows are setup correctly to get the data to where it belongs, that can be solved with just a bit of planning before building. Otherwise, I can’t really think of any problems it may create down the road.

Also:

@mano

I prefer to point from each “Item” to the “User” who owns it.

My example of how to set up the database with the relation, would do exactly that.

Think about this. If you have a user data type with a data field of list of items on it, then the user who owns all those items will have it stored there.

Also, if you have a data field of user on the data type of item, then again the user who owns that item will be right there on it.

I apologize, but it sounds like you are not very familiar with database design, as I wasn’t when I first started out on bubble. Taking the time to read through the posts I linked will help understand bubble more, and reading online from other sources on database design and structure will help you understand how they work and the enormous benefits of relational database structure.

I have created my database so that any data type can be accessed from any other data type in the database so that I can quickly perform data retrieval.

Think about it like this…if you have a list of items owned by a user, then all you need to do to get that list of items is to say “current page user’s list of items” and very quickly, much more quickly than doing a search, you would get a list of all items owned by that user.

Alternatively, if you wanted to do what you are trying and you follow my advice then all you need to do is search for “users” with a single constraint “item list count: > 50” and much faster will you get the results than by

@mano

I eventually solved it by running a search on Users and using filter: with an Advanced: condition.
In the advanced condition, I did a search on the Items table for each User separately, then run :count>50

Because in your solution you do two searches and one of them is using an advanced filter which is the slowest way to filter and recommended by most to avoid.

In the end, it is up to you to take the advice from advice givers…I only offered mine because I struggled a lot in the beginning and after two years on the platform I have come to understand most of the best practices and that came from following the advice of the experienced bubblers that came before me.

Good luck

[mano]

Thank you for your detailed reply @boston85719.
Let me explain why I avoid using a list of items in a field.
If you add a new Item and as part of it’s creation you put the User as the Owner of this Item, you are guaranteed to have it correctly in the DB.
However, if you need to run another action to also add it to a User field that stores a list of items, it might fail (bubble actions can fail anytime) and you will end up with an Item that points to the User as its owner but the User will not have this Item in its list of Items he owns.
If you rely on the list of items in the User’s table, you might end up with a lot of problems.

I understand that Advanced filter is slow, but I’m not willing to give up the reliability of my database to improve the speed.

[boston85719]

I think you may be missing a key part to what I am focusing on. The relationship between the two tables is not lost if you were to have an uncommon error of the item not getting placed into the list of items on the user data type, because the item data type still would have the relation to the user as a field.

I think ultimately it is your decision. I personally feel the likelihood of an error occurring that an item is not stored properly in the database as part of a list, is not the fault of bubble, but the app creator using workflows that may not be set up correctly.

I haven’t ever had an issue with an item not getting stored in a list correctly when items are created individually. I have experienced recently an issue with this when using an API workflow that runs on a list of numbers and creates multiple items consecutively and subsequently adds them to a list.

I have been in communication with the bubble team about this, and they are trying to figure a way to improve my workflow to find a resolution to the issue, which is mostly the fault of the application capacity getting maxed out during the API workflow.

I personally choose to make data retrieval simpler for me in development and quicker for the user as the experience of waiting for a long time and possibly having the app time out on data retrieval is something I’d rather avoid.

If you haven’t read the performance Q&A created by one of the founders of bubble, I highly recommend reading at least that forum to get a sense of what the community consensus is on best practices. And if you truly feel that there may be issues with data integrity, reach out to the bubble support directly to query them on the issue.

[kngf222]

This seems like it would evaluate to a yes/no data type:

[boston85719]

yes it would

[kngf222]

So then that wouldn’t really answer the question as to how to procure a list of all the Users/Type of Thing that have a list greater than X. Is there another way to get that list returned when the list is greater or less than X?

[boston85719]

@boston85719

So you do a search for users:

Constraint: item list count > 50

I didn’t really understand what you were trying to get at with the last post you put up. I scanned the thread and saw the part where I quoted above.

That should help you understand how that will return a list of all users whose item list count is greater than 50. When you use a constraint on a search it will return only those results that match the constraint…so a constraint that evaluates to a yes/no is saying only return the users whose list count is greater than 50 is yes…if it is not greater than 50 it evaluates to a no and the user would not be returned in the list of results.

@kngf222

Is there another way to get that list returned when the list is greater or less than X?

Yes, two constraints. One for greater the other for less than.

[kngf222]

I don’t see where in the constraint of the “Search for Users” how I can to “item list count > X” when the list is a Custom Data Type. It only gives me the option to do Contains, Doesn’t Contain, Is Empty, and Isn’t Empty.

[boston85719]

maybe do a search for the list of requests thing and then use the group by operator and try to group by the creator.