Search constraint?

[stephencharles]

I am stuck with a type of constraint I am trying to setup.

In my database I have 2 data types, that each have fields:

User
> Credits (number)

Notes
> Notes (text)

I want to do a search with the following constraints:
Show me all users whose Credits value is greater than the amount of notes they have created.

In my workflows, I do a search for users. Then I add a constraint:

Credits > Search for Notes:Count

The above search is not correct because it is looking for users that have credits value that is greater than the count of note entries for all users. Now I need to add a constraint to “Search for Notes” so it only compares the users credits to notes that user has created.

When I click “Search for Notes” I can add a constraint. I can choose “Created by”. I am not sure what to make this equal to.

Any ideas?

[fayewatson]

Hi @stephencharles Can you share a link to your app? One way to achieve that search is to create a Notes field within the User called “Notes” (Type: Note, List: Yes). When a User creates a note, add another action in the workflow which adds that Note to the Current User’s Notes field (Data --> Make Changes to Thing --> Thing to Change: Current User --> field to change: Notes add (Result of Step 1: Create a Note).

Then the constraint could be “Credits > Current User’s Notes: count.” That should do it!

[stephencharles]

That may work if I can do Credits > this user’s notes: count.

I can’t use current user because the search is for many users.

[fayewatson]

Ohh, I see. Sorry I misunderstood - you can use :filtered and an advanced constraint for this (leave the constraint in the Search for Users empty and the list filter will appear after you add :filtered to the dynamic expression):

[stephencharles]

Continuing the topic of relational databases, I have a new challenge:

Three different tables (data types):

1. Restaurants (user)
2. Meals (created by restaurants)
3. Restaurant Reviews (created by other users)

I want to show a list of the top 10 Meals sorted by the most # of reviews that each meal’s creator (restaurant) has received.

Based on previous posts it seems this is not possible, but ideally I can sort by:
This Meal’s Creator’s Reviews Received Count

This is a little tricky because there are 3 related tables to use when sorting. It is even more tricky because this app is already created with active users. I think the obvious answer is to create a list for each user that our reviews(type). However it would certainly be easier if there is some way of using dynamic sort/advanced/filter to figure this out without having to backtrack on all of the already created entries in the database.

Thanks

[louisadekoya]

I am not able to try this out myself just now, but I’d be surprised if you couldn’t do this. Are you aware that you can do a search within a search? It wasn’t obvious to me when I first started on Bubble. So in your case, I’m thinking that you could do a search for meals, where created by is in do a search for restaurants sorted by count of reviews. Something along those lines might help.

You may find it easier to store the count of reviews on the restaurant data type as well. So whenever a review is posted, you add 1 to a restaurant field ‘reviewCount’.

[stephencharles]

Yep, tried all sorts of searches within searches with a dynamic sort. However, I haven’t been successful in getting this to work.

Yeah, storing the count in the user type would definitely work. However, if I need to build a lot of sorting features, the user type would quickly become crowded with fields, which I’m trying to involve. Also, the site already has hundreds of reviews, so I would have to go back and update all of the user data. I think this may be the solution, but I want to see if there is actually a way to do this without storing the count as a field.