Subquery on table links and files

Anvil Q&A
1

What I’m trying to do:
I’m ready with rewriting of all my databank fetch functions to use the new q.fetch_only() function. It really helps to imporve perfomance, but now I would like to write a completely new query type for me.

I want to do a query on a data table that has a linked table (users) and stored file and don’t know how to get it done.

With: q.fetch_only(file=q.fetch_only("name")) I can get the required column from a file or linked user table. Done it already for other search functions.

Is there a way to query the file[‘name’] and user['email] to show only the matching rows of the data table?

@anvil.server.callable
def data_loader_quick_filter(qacn,qshort):
  """quick filter via Data Loader"""
  return app_tables.assay_databank.search(q.fetch_only("status", "import_type", "objects", file=q.fetch_only("name") ,created_by=q.fetch_only("email")),tables.order_by("created_date", ascending=False),
        created_by=q.ilike('%{}%'.format(qacn)),
        file=q.ilike('%{}%'.format(qshort))
  )
2

I think you need this:

3

I’m already using it. It’s improving the data loading but as far I can tell. I don’t see a way to use it to query it.

q.fetch_only("status", "import_type", "objects", file=q.fetch_only("name") ,created_by=q.fetch_only("email"))

I need to query the linked user email and the file.name to select the row which I want to find.

In my database there is a row with a link to the user data table and a saved file. Is it possible to query the database for the file[‘name’] and user[‘email’] to find the searched row?

Maybe some extraction of the whole database with email and file name, then working with the extracted rows containing already extracted file name and user email to do the query on it. Then picking the rows IDs to rebuild the search and pass it into the data grid. Sounds crazy…

4

You query linked columns using rows from the linked tables. It’s awkward, but you have to fetch the rows from the linked table that matches your search criteria, and then use q.any_of on the original table.

Edit for example code now that I’m at a proper keyboard:

    users = app_tables.users.search(email=q.like('%'+filter+'%'))

    return app_tables.mysteries.search(q.any_of(author=q.any_of(*users), title=q.ilike('%'+filter+'%')))

This queries based on a linked field and a non-linked field.

2 Likes
5

I don’t think join queries are possible in Anvil.

You may be able to turn your query upside down and search the user table and get the linked data, but that may require some database refactoring.

6

Thanks for the help how to query the links. It works :smiley: !

  users = app_tables.users.search(email=q.ilike('%{}%'.format(quser)))

Sadly the file is a media object. It is possible to query the names of the media column?
You can acess the name attribute with database['column_name].name it has to be possible to query it somehow?
It says you can’t use ilike on a media object. Propably I need somehow to use it on name property of the object.

It worst case I could query the database and with fetch get only row_id and ['file'].name then query it and get row_ids back then query the database once again with *users and fetch all required columns.

Is there a way to use name of media object for query?

7

Habitually, get the file.name before saving the row and have it in a column alongside the media object.

3 Likes
8

As @socint suggests, it’s best to explicitly store in their own column all the fields that you know will be involved in a search. Not only this will make the search easier, it will make it also faster, because will automatically create an index for that column.

When I have two linked tables and I need to find all the linked values, then I do use linked columns.

But when I need to find one of the rows of one table linked to one of the rows of the other table, that is a join query with a filter on both tables, I duplicate the values of one table in the other table and filter the latter by two columns.

For example, instead of having the Users table with a linked column called blogs, or a Blogs table with a linked column called users, I would have the user_email column on the Blogs table, so I could do searches like blogs.search(user_email=e, title=q.ilike(f'%t%')).

This will require a second query to fetch the user, but it will usually be easier and faster (because Anvil doesn’t manage join queries) .

3 Likes