SQL Select for users rows

Anvil Q&A
1

In our app users are connected to one or more organisations and an organisation can have many users.
How do I code the SQL select correctly to get all organisations of one specific user?
What I have tried (didn’t work): ```
SELECT * FROM organisation WHERE %s = ANY(user) ORDER BY org_updated DESC;", (user_id[1],))

2 
f""" SELECT * FROM organisation WHERE user = '{user_id[1]}' """

This is assuming user is the column name, organisation is the name of the data table and user_id[1] is some string which can be expected in the user column.

3

Thanks for your reply.
That works well if the field user is only one user.
In our case it is linking to many users (= users rowS) - that is why I used ANY(user)

4

Can you say more about your data model?

  • What are the relevant column names?
  • Are you using linked rows?
  • Is every row in the organization table linked to a bunch of user rows?
  • In what way is your query not working (error, data not right, etc.)
5

Hi Yahia,

Screenshot 2024-07-01 234902
Screenshot 2024-07-01 234902920×346 28.1 KB

the organisation table has a column user which contains linked users rows.
Neeecos solutions results in an empty list.

my solution throws an error

cur.execute(f"SELECT reports.* FROM reports WHERE ‘{user_id[1]}’ = ANY(users) ORDER BY reports.updated DESC;")

WrongObjectType: op ANY/ALL (array) requires array on right side LINE 1: SELECT reports.* FROM reports WHERE ‘475251394’ = ANY(users) …

6

It looks like the users column is a jsonb column containing a list of data structures rather than a list of simple user ids.

You could start a long conversation with chatgpt showing what a reports row looks like and trying to figure out a complex query that does the job you need. I started, I saw a little light, but not enough to give you an answer.

Or you could do what I do when I plan to use SQL more than Anvil queries, which is don’t use linked rows. Instead of using linked rows, I use a simple object column containing a list of user ids, which is much easier to use in queries like the one you are looking for.

1 Like
7

Thanks Stefano,
I started as well with chatgpt - got 2 insights from it: 1. it does know ANVIL quite well, 2. not so well to be of help.
Tried 12 solutions nothing worked.
I can not believe that I am the first one who is running into that problem.
@meredydd: I am sure you have a solution - please let us know.

8

I wouldn’t even mention that you are working with Anvil.

I have created a simple app, did a select * from users and a select * from reports to see what was in there.

Then I asked this to chatgpt (I’m pasting it here without even rereading it) and got something working out of it:

i have a postgresql database with two tables: users and reports.
users has an _id column containing numbers as primary keys.
reports has the jsonb users column containing a list like this, where the user ids are 1147978440 and 1147978441:

[{"id": "[790289,1147978440]", "mac": "23aa55b2dbdd0e1f112d080fcf9c8f484f75a14627a7621c03466d34ae7c86d4", "backend": "anvil.tables.Row", "methods": ["__anvil_iter_page__", "__getitem__", "update", "get_id", "__setitem__", "delete", "set"], "permissions": []}, {"id": "[790289,1147978441]", "mac": "aa6f48212b305e4f2b3ece53a2da8b746d2acc395ff00fcd39e234812551032e", "backend": "anvil.tables.Row", "methods": ["__anvil_iter_page__", "__getitem__", "update", "get_id", "__setitem__", "delete", "set"], "permissions": []}]

create a query that joins the two tables and finds all the users that have the id included in that jsonb structure

This wasn’t what you were looking for. It was just a way to explore and see how chatgpt would approach the problem. The result was way more complex than I expected, but it was kind of working.

As I mentioned earlier, I imagine this would be a long chat (I’m sure for those who know postgres better than me would be shorter).