API Connector - Querying an Array of multiple values for Repeating Group data

I am running into some trouble when using API Connector to query a database that contains baseball games by requesting games that have a user’s role, and in my case each user may have multiple roles stored as an array of comma separated values. (ex. [pitcher, left_fielder]). The repeating group inside of Bubble will not display any data if I “insert dynamic data” into the API call from the repeating group settings because the user’s role is an array with multiple values.

In other words, because “pitcher, left_fielder” is not a role, the repeating group shows nothing…whereas if “pitcher”, and “left_fielder” are separately called for the repeating group, it will display the desired data for each role. I need to display data for all existing roles. How can I “insert dynamic data” of an array from Bubble with mutliple values into API Connector to display ballgames that have open positions for EVERY role a user may have?

What I really need bubble to do, is something similar to PHP’s in_array() function. Is there anyway to achieve this without using blockspring?

To clarify, do you mean a Bubble user, with a field of type “list of strings”, that shows commas when displayed as a string?

The general solution would be for Bubble to update the API plugin to allow a list parameter.

While waiting in happy anticipation for that, there are some workarounds, depending on the number of parameter values.

If there is a small number, the query can have a parameter for each one:
SELECT role, colouredhat FROM baseball_info WHERE role = ? OR role = ? OR role = ? LIMIT 199
Then in the repeating group data source, each API argument can pick the list’s element by numerical position.

If there is a large number, consider a two-pass approach, first populating a temporary table with the options, then querying with a join to the temporary table.

I hope you hit a home run soon!

Thanks for the help! That is exactly right, except I am using a service rather than an SQL database (i.e. think Google Sheets)

Here is the current solution:

  1. Create a handful of Database Types under “User” to cover enough multiple roles on any given user (i.e. role1, role2, role3, role4, role5, role6)
  2. Query item numbers from the spreadsheet (which are stored as comma separated values)
    role = ? OR role = ? OR role = ?,

The problem - I need to be able to identify item #s inside of the query (Bubble API connector returns the the array as a “list” which just appears as list of text with comma). Do you have any idea how to turn this text string into an array or at least pull out items into the role1, role2, etc. (max of 5 roles per user)?

I feel like I’m missing something very obvious

It is the list operator :item #

Type in the number manually

repeat a few times

It will probably be fine with the empty items.

This pulling of list items individually isn’t done very often, so it’s easy to overlook this method.

Under User, you could have just one field “Roles”: list of text.

1 Like

I’m so glad that you posted that. Your call is almost identical to what I had in the API Connector…but I was having a frustrating problem: even through the user that I am testing my calls on has two roles (pitcher, left_fielder), it still returned both roles for each item number. I wrote a test and continued to receive the following result for a user with two roles:

test_role_item1: pitcher, left_fielder
test_role_item2: pitcher, left_fielder
test_role_item3:
test_role_item4:
test_role_item5:

I looked at the call you posted and realized that the problem with my app’s API call was that I had been defining “item#” before the the list of roles inside of the team record (i.e. “ballteam’s records’s item#1 field’s role”)…and instead it should have been “ballteam’s records’s field’s role item#1”). That did the trick!

Many thanks @mishav

2 Likes