Hey!
Am just running through some queries using the new nested table functions. Generally these are really good and easy to use. I’m struggling to find a way to deliver a text search of the nested items. I have a table list_type with a sub table of list_type_item. I want to be able to search items and return the list type that includes a match.
This is broadly the approach I’d have used previously to do it using Wappler (rather than a view):
So here I’d have a SELECT DISTINCT query with the join field on item. This would give all the list_type that contain that item. I’d then run a repeat on that query to return all the linked items.
Nested queries using subtables looks like it could/should bypass the need for a query and then the repeat. I can add the subquery directly into the main query. THis works perfectly for the base data.
When I then try to add the search
- I can’t add the subtable as a join on the main query, it is only pickable as a subquery. If it were a join aswell as a subquery I could use it’s columns as conditions in the main query
- Adding a subquery doesn’t expose the subquery columns in the main query conditions
- Adding the search as a condition in the subquery filters the subquery, not the main query
The convenience of subqueries is really useful on the front end. Is there any way I can use them alongside this kind of text search?
This is resulting the table, there’s a search box above it which should filter the table to show any list type that matches the search text, or any list type that contains an item or model that matches the search text:
This is the query using subqueries:
Hope that makes sense!
