Help with sorting by a linked field

[benjnse]

What I’m trying to do is have a repeating group display the projects name and priority sorted by the priority. I can get the unsorted results to appear but for the life of me I cannot figure out how to sort by the value of another table.

I’ve got 3 tables (or data types as buble calls them):
User which doesn’t come into play here
The 2 which are involved are:
Projects and “Project Priority”
Projects fields are:
name => text
priority => link to Project Priority
then the standard bubble fields (i.e. Creator, Slug, etc.)

Project Priority fields are:
priority => text
then the standard bubble fields

In sql assuming a database layout of:

---

Project (table):
id => int, primary key, etc
name => text
PriorityList_id => int

---

PriorityList (table):
id => int, primary key, etc
priority => text

---

The sql to accomplish what I want would be:
select name, priority from Projects, PriorityList where PriorityList_id = PriorityList.id order by priority;

I suppose the simple approach would be to not link the 2 tables and instead just make priority a text field that is entered via a dropdown whose values are based on the Project Priority table. But I get the feeling I’m going to need cross table sorting for other things.

Thanks,
Benjamin

[jdiaz]

This is one of the limitations of Bubble when it comes to sorting. You cannot use a join to sort a table. The work around I have used in the past is to keep the join on the table, but have a text field that is updated as well. So, I would have a status_id that is a join, then a status_text field and update both with the same thing in the database. That way, I can still use the join for other things, but the status_text field is available to sort on.

[sudsy]

Just to throw this out there for consideration…

This problem could be attacked from a slightly different angle. Reconsider the UI such that filtering is used instead of sorting. Use a dropdown, radio buttons, tabs, or similar to constrain projects to the selected priority…

search for Projects where Priority is <chosen priority>

With this approach, you’re also not relying on a string of text to identify the priority, which would allow the Priority data type to more easily evolve with time. I suspect it would also be more performant and result in a better UX - especially with a large number of projects.

Of course, you could still sort all projects (or any filtered subset) by any project field (such as due date) thereby enabling different “views”.

Anyway, just a thought…

-Steve

[ed727]

What happens when you choose “change which field” in the Sort By dropdown, and then do a search for the PriorityLists’s priority?

I have not tested this, but am just curious.

(PS: for these simple menu options I usually use option sets vs. having to set up another datatype)

[benjnse]

Thanks, that’s a good workaround. It’s annoying that it’s needed and causes duplication, but that’s most likely the approach I’ll take, given that the scenarios I’m thinking of (connecting customers/contractors to records that are connected to locations) will need the data linkage .

[benjnse]

Thanks, I’ll be keeping that in mind. Actually next on my hit list, is to figure out whether or not it’s possible to use the views you can create on the app data section of bubble’s data tab as a data source, or if there was some other way of creating data views other than on the page.
That and some type of stored procedure functionality would be nice.

[benjnse]

It just ends up returning an empty data set. https://bubble.io/page?name=index&id=playingaroundhaze I set it to public, in case you’d like to play around with it, as I’m just using that as a playground to figure out bubble.

Other than separation of concerns, is there a benefit on bubble to using option sets (I’m assuming they store the option sets in the same database, although that may be a bad assumption on my part now that I think about it.)? As yeah I was simply using the Project Priority as a data constraint.

[ed727]

Hmm… don’t know. Was just an avenue to explore.

Re: option sets, tons of info in the below post. Bubble says there’s a speed advantage, but whether it’s noticeable in your case I don’t know. I like option sets because they are easier to work with when building. You can pick the option in the editing panel, rather than having to do a search for a constraint based on its name. Note though that they should only be used for fixed constraints that are going to remain relatively static; changing them requires you going into the editor and pushing a new version of the app.

[New Feature] Option sets

Hello everyone! We’ve just released a new feature: Option sets. Option sets allow you to have a static list of choices or Options which are global to your app. This is great for situations where you know ahead of time what options are possible for a variable - things like days of the week, marital status, team name for a given user, etc. You can create them in the Data tab, add various options to them, and even add attributes to your options. You can use option sets anywhere you would use a nor…

[sudsy]

@benjnse

is there a benefit on bubble to using option sets (I’m assuming they store the option sets in the same database, although that may be a bad assumption on my part now that I think about it.)?

You can think of Options Sets as “static” Things (data types). They’re often used where one might use a constant or enumeration (in conventional programming), and as such, they can make your Bubble logic more readable and less error prone.

Of course, they’re also faster precisely because they don’t use the database.

• Bubble Manual: Options Sets

-Steve