How do you search a GridPanel for linked field contents?

Anvil Q&A
1

Hi
How are you?

The scenario is as follows (a very common one I guess):

I have 2 tables: employees, teams
The field teams from employees" is linked to a field “name” in teams
I have a datagrid that displays employees (including the linked field contents)
I managed to display the text in the teams table field using a label and data bindings in the Form1.RowTemplate inside the repeating panel,

Now, I have a search box implemented with : (from one of the examples)
@anvil.server.callable
def search_employees(query):
result = app_tables.employees.search()
if query:
result = [
x for x in result
if query in x[‘first_name’]
or query in x[‘last_name’]
or query in str(x[‘pay_grade’])
or query in x[‘team’]
]
return result
Which by the way does not work for the field “team” (in its raw form). It does for the other fields.

Now that I have the text with the team name in the repeating panel, how do I make the search to search for this field too?
Here I’m clueless; is it in the search function, in the row template Class or somewhere else?

Your help will be much appreciated!!
Thank you!!
Warm regards
Ricardo

2

When searching through linked fields you have to build a list of rows that match in the linked table, and then use the query operators to match one of those rows. Start with the blog post on query operators: Querying Data Tables to get a feeling for how to use them to avoid needing to do the filtering in Python.

Then think in terms of doing a .q.any_of on your teams field in employees, using a list of rows from the teams tables. So you’ll end up with two searches, the first one on teams and the second on employees using the list of rows from the first search.

3

Hi js
As always thank very much you for your answer!!
At first glance, and being a newby in Anvil it sounds a bit complicated, or at least not straightforward (I don’t know if I’m being clear, but it sounds a like a too much of a programatic approach)
Of course I will review your suggestions, but I feel that if it takes too much work (programming), as soon as it becomes frequent, I will probably end making views at the sql level to simplify code as much as possible.

Warm regards!!
Ricardo

4

If you have direct SQL access, that’s likely to be more convenient. For those of us working with data tables, the query operators are the closest we get to SQL.

1 Like
5

Not always, but sometimes I do have access.
I sometimes feel a little bit (a little meaning a lot) lost in the modern development world, because I come from a more data/database world (which from my point of view is much simpler), but I guess the opposite will happen to those who come straight from the programming universe :smile:

6

The subject asks how to search a GridPanel, but the code seems to be searching a table. Then you mention making views or reorganizing the database in some way.

It is not clear if you are asking (1) how to search a table, (2) how to search a GridPanel or (3) how to reorganize your database.

1. Searching a table

If you want to search a table, @jshaffstall gave you the correct answer: you can’t do join queries in Anvil, so you need to first do a query on one table and then use the list of rows returned by the first query for the second query. In this case I would first search the teams table, then use the returned list when searching the employees table.

I would also let the database server do that, not with a list comprehension like you did in your example.

Something like this (untested) example should work, and it should be much faster than your list comprehension:

teams = app_tables.teams.search(name=q.ilike(f'%{query}%'))
employees = app_tables.employees.search(
    first_name=q.ilike(f'%{query}%'),
    last_name=q.ilike(f'%{query}%'),
    pay_grade=q.ilike(f'%{query}%'),
    team=q.any_of(teams),
)

2. Searching a GridPanel

If you want to search a GridPanel, I’m going to make a few assumptions: the GridPanel contains a small number of rows that can be efficiently scanned with a list comprehension, and the objects inside items are row objects of the employees table.

If this is the case, then the list comprehension you show almost works, but the last line should be changed from query in x['team'] to query in x['team']['name']

3. Reorganizing the database

If the teams table has only the name column, then I would get rid of it and make the team column a text column rather than a linked column.

Creating a linked table is the correct approach when you have full access to the SQL world, but in Anvil you have a limited access to the tables, you don’t have access to constraints, triggers and join queries.

Whenever I can, I use simple object columns rather than linked tables. For example I have an app that manages projects with trucks with crates with panels. In a normalized database they would be (at least) 4 tables, I have all in one trucks simple object column. In some projects the project['trucks'] value can exceed 200kb, but when I load a project I know that I need all its trucks, crates and panels, and I will never search one panel or one crate or one truck. I will only search for a project first, then I can dig as much as I like in memory.

1 Like
7

Hi Stefano,
How are you?

1. Thank you very much for your answer!
You went were Jay did, and beyond.

All your assumptions are right.
I thought (and I was wrong) that you could do like joins inside Anvil.
Now it is clear to me how to proceed, and what are the options.

2. What I was trying to do was indeed search inside the GridPanel (the data had been already loaded from the database and I just added a search field to filter the results); your addition to the solution worked perfectly!

3. I agree, however sometimes the database comes already designed, and also I was trying to test and solve different situations that will eventually have to face when developing
De-normalizing is a dangerous game. Sometimes it can really makes things easier, and some others turn them into a nightmare. Depends a lot on the case.

Regarding the linked field, I have a spin off question. If you consider it is worth making a new post please tell me and I will do so.
The question is the following:
I want to add a row to the employees table, which will have a team name, and when I input the data on screen I will enter a string for the team name.
How do I transform that into the linked object that should be saved in the employees table?

Thank you very much!!
Warm regards
Ricardo

8

Something like this:

team_row = app_tables.teams.get(name=team_name)
if not team_row:
    team_row = app_tables.teams.add_row(name=team_name)
app_tables.employees.add_row(name=name, [...], team=team_row)

or:

team_row = app_tables.teams.get(name=team_name) or app_tables.teams.add_row(name=team_name)
app_tables.employees.add_row(name=name, [...], team=team_row)
1 Like
9

It worked!
and I’m happy for it!
On the other hand, I’m a bit worried because this line of reasoning (rows turning into objects and similar transformation events, are far away from my usual way of thinking (I come from an older world); and leaving aside these particular cases you are so kind to help me with, I still feel a bit lost when I face new similar situations.

Would you recommend any path for getting closer and getting the grip of it?
I’m following the tutorials and reading the documentation, but I’ve found that sometimes it is assumed that you already know how to interpret things in this “objectifed” way (which is not my case)
I’ve already taken a python course, and an OO programming course, but somehow, sometimes the dots do not connect :smiling_face_with_tear:

Any recommendations will be much appreciated!!

Thank you very much!!!
Warm regards
Ricardo
P.S. and my apologies for my lack of expertise in these matters

10

Now, if this had its own subject and was its own post, it could attract other forumer helpers and end up with helping someone else in the future :slight_smile:

11

Understood. Loud and clear! :grin: