Text search of Nested Query data

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):

Displaying multiple values in a table field with multiple joins - Server Side Components / General - Wappler Community

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!

Just to add to this…

With a junction table I can add the table as a join in the main query. This works fine for the search, though if I add the junction table as a subquery aswell the query then returns duplicate values.

I can’t add a sub table as a join in the main query so can’t use this as a condition in the search. With this added as a subquery it also returns duplicate values.

With this query:

I get 4 lists returned:

WIth this (models subquery added):

I get duplicated lists:

This condition works fine:

and when searched the query returns distinct records.

I think for now I’ll move forward with running multiple queries and joining them on the page, but something seems wrong either with how I’m expecting subqueries should work, or with how they actually function. Most likely me!

I guess to summarise I’m left with 2 queries…

  • Should I be able to add a subtable as a join, as well as a subquery?
  • Is there anything in the way subqueries function that would stop distinct value queries returning distinct values?