XANO - cannot get records assigned to logged-in user id for many to many relationship

Data Sources
,
1

Hello everyone,

I’m currently working on a project using Softr and Xano, and I’ve run into some challenges that I hope the community can help me with.

Context:

I have a simple many-to-many relationship between two tables:

  • Users: Contains user data.
  • Workspaces: Contains workspace data.

The relationship between Users and Workspaces is managed through an intermediate (pivot) table, which connects users to the workspaces they are part of.

Issues I’m Facing:

  1. Using the Intermediate Table:

    • Problem: When attempting to use the intermediate table to fetch data, I’m unable to reference the logged-in user’s ID within Softr’s query builder for Xano. I understand that in Softr, you can typically use variables like {{loggedInUser.id}} to refer to the current user’s ID.
    • Attempted Solutions:
      • Tried using {{loggedInUser.id}} in data filters, but it doesn’t seem to work when querying data from Xano via the intermediate table.
      • I’m aware that we shouldn’t use JavaScript references directly in the query, so I’m avoiding expressions like window.logged_in_user[‘record_id’].
    • Question: How can I properly reference the logged-in user’s ID in the query when using the intermediate table in Softr connected to Xano?

  2. Using JSON Fields:

    • Problem: As an alternative, I tried storing the relationships directly in JSON fields within the Workspaces table. Specifically, I added a users JSONB field that contains an array of user IDs associated with each workspace.
    • Attempted Action: I set a condition in Softr to filter workspaces where workspace.users contains the loggedInUser.id.
    • Error Encountered:
      Unable to fetch records for application with id=‘WorkSpace’ StatementCallback; bad SQL grammar [SELECT * FROM “xano-xtyr-n5fq-wnhy-db”.public.x3_126 WHERE ( () ) LIMIT 6]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near “)”
      Position: 64
    • Observation: It seems that the query generated by Softr is malformed, possibly because it’s not correctly handling the JSONB field or the condition syntax.
    • Question: Has anyone successfully filtered data based on JSONB array fields in Softr.io when connected to Xano? If so, what is the correct way to set up the condition or filter to include workspaces where the users JSON field contains the loggedInUser.id?

Additional Details:

  • Database: Using Xano as the backend database, with PostgreSQL under the hood.
  • Data Structures:
    • Users table has standard user fields.
    • Workspaces table includes a users JSONB field, which is an array of user IDs associated with the workspace.
  • Goal: To display on the front-end (built with Softr) all workspaces that the logged-in user is a part of.

What I’m Looking For:

  • Guidance on how to correctly reference the logged-in user’s ID in queries when using an intermediate (pivot) table in Softr connected to Xano.
  • Advice on filtering data based on JSONB fields within Softr queries.
  • Any examples or best practices from others who have managed many-to-many relationships in a Softr and Xano setup.

Appreciate any help or insights you can provide!

Thank you, Grzegorz

2

I think you would need to use conditional filters to filter data based on logged-in user… We did have a call planned yesterday however you did not join, perhaps you can try again ?

generally you would do your JOINS and let Softr do it’s conditional filters

3

I think you would need to use conditional filters to filter data based on logged-in user…

Hi! Yes this is pretty obvious solution which I tried as very first - the only problem is that I don’t know how to reffer to logged in user ID in XANO query - as I mentioned :

  • I’m unable to reference the logged-in user’s ID within Softr’s query builder for Xano. I understand that in Softr, you can typically use variables like {{loggedInUser.id}} to refer to the current user’s ID.
  • Attempted Solutions:
    • Tried using {{loggedInUser.id}} in data filters, but it doesn’t seem to work when querying data from Xano via the intermediate table.

If you know how to reffer to logged in user ID in XANO query than all my problems are gone :slight_smile:

PS. Thanks for invitation I set up a meeting in calendar.

4

Wondering if you figured this out. I’m trying to write a query that utilizes the user id as well. Did you use softr’s conditional filtering after pulling the data using the query, or were you able to use the user id in the query itself?