Hi again everyone 
Here’s a specific issue I’m having that points to a more general challenge I’ve been running up against.
I have a Supabase table that has a foreign key reference to another table, which in turn has two foreign key references to two other tables. In other words the table I’m accessing in WeWeb has a parent table and two “grandparent” tables.
At the child level I’m trying to concatenate the “name” property of each level that has a foreign key linked, something a little like this:
“grandparent1.name” “grandparent2.name” “parent.name”
The issue is when any of these is null, which is a perfectly normal case. When I use a lookup and the looked up property is empty for any row, I get the following “Cannot read properties of undefined” error. The data displays fine for non-null rows but null rows show as empty in WeWeb and I can’t even control what shows instead of blank values (like “n/a”, “-”, or similar). I tried using if x = null without success, and the following doesn’t work either.
I could (begrudgingly) live with this, but it gets worse when I try to go up one level. The moment I try to concatenate one expression that evaluates to null, I get the “Cannot read properties of undefined” error and the element value shows as blank even if other parts of the formula don’t evaluate to null.
For example, if I try to do a “double lookup” (lookup the grandparent item linked to the looked up linked parent item) and it returns no value, the formula won’t even return the result of the first level lookup (the parent).
How can I solve this?
Thanks,
GD
When I see people using lookup, I’m wondering, have you considered JOINing the tables on the backend directly, and then just handle the nulls?
My understanding from the WeWeb Academy courses was that it’s faster to lookup since it’s using the collections that are already loaded, but granted my understanding of how all this works is very rudimentary.
From what I know of postgres, what you’re saying would imply querying the backend to do the join operation, correct? I get that in theory but again I don’t know how to actually do it in the context of the Supabase plugin for Weweb.
I don’t know what you mean by “just handle the nulls” in this context either. My entire question is about how to handle nulls.
1 Like
Yeah, so what I would do, is get the tables JOIN into one table, there is many ways to do this,
- Create a view
- Make a join via the Collection Fields → Advanced
- There is other more complex ways
Then you’ll get your collection with nulls, which you can handle with a simple check such as
null_value ?? "n/a" // This will return "n/a", if your null_value is null or undefined
But you can do this with lookup too, but yeah, it’s more complicated logic in my opinion. More steps → More complexity → More occasions to make errors
I could write you the query, but I’d need to see how the tables are structured (colums) and how they’re connected (FK relationships)
1 Like
Ahhhhh I see, thank you that’s very helpful.
Is there documentation somewhere on the syntax to use for inputting postgres queries in Collection Fields → Advanced? It’s failing to parse the query I’m trying to input
https://postgrest.org/en/stable/references/api/resource_embedding.html
here is the docs, for the Advanced, you want anything that is after the select= …