I have set up a page where I use Nested Repeat Regions as described here Nested Repeat Regions.
Because it creates such a long list, I want to have a search facility to return a set of results but I can’t work out how to do it. I need it to search in the top level query and in the nested query. How do I do this?
Currently I can only get results from the top level query
If I search for something I know to be in the nested query then I get a blank result.
How are you trying to do this, at database level or with a data query filter?
Hi Brian, sorry for the delay, not the best of days today.
First query finds just the business information, queries in the repeat find addresses, people and contact information. All are many-to-many relationships with link tables.
I suppose I should do one query with joins to the three supplementary tables but I just couldn’t get it to work. I need ALL businesses to show and then all addresses, people and comms if there are any, per business.
Not sure what else you need to know. Oh, here are the table structures
And link tables are simply like this
Hi @Hyperbytes, I know you have got your hands full at this moment but do you have any quick thoughts or suggestions on this? Should I start again 
I think you need to use different logic . If you can create seperated server connect actions . You can use “where” conditions
Sorry Neil. having a few real,life issues with an ill 88 year old mother however I have to agree with Serhat, the logic needs to be changed. Separate queries would be more effective based on a parent child model
This is a difficult one, which tables and columns are all used for the filter? Problem is that you put a filter on the main query that it doesn’t look in the sub tables that did not match in the main. Joins could probably be the solution, but with so many many-to-many relations very hard to create.
I agree Patrick. I need to get my head around this. My mind says it should be one query with joins including a full outer join so need to do a custom query.
You can do all the filtering in the main query, use a custom query with subqueries in the where condition.
Something like:
SELECT * FROM business
WHERE business LIKE @filter
OR EXISTS (
SELECT 1 FROM people
INNER JOIN business_people
ON people.people_id = business_people.people_id
AND business_people.business_id = business.business_id
WHERE people.title LIKE @filter
OR people.forename LIKE @filter
OR people.surname LIKE @filter
)
OR EXISTS (
SELECT 1 FROM address
INNER JOIN business_address
ON address.address_id = business_address.address_id
AND business_address.business_id = business.business_id
WHERE address.street LIKE @filter
OR address.county LIKE @filter
)
Thanks @patrick, a great help. I will be diving into this later this evening. Cheers
Hi @patrick, I have been looking at this for most of the day and struggling to get it to work. Here is the MySQL as I would like it to be…
SELECT DISTINCT b.business_id, b.business, b.trading_name, b.notes
FROM tbl_business AS b
WHERE
b.business LIKE :P1 OR
b.trading_name LIKE :P1 OR
b.notes LIKE :P1 OR
EXISTS (
SELECT 1
FROM tbl_people AS p
INNER JOIN link_business_people AS b_p
ON p.people_id = b_p.people_id
AND b_p.business_id = b.business_id <<
WHERE
p.forename LIKE :P1 OR
p.midname LIKE :P1 OR
p.surname LIKE :P1 OR
p.nickname LIKE :P1 OR
p.notes LIKE :P1
) OR
EXISTS (
SELECT 1
FROM tbl_address AS a
INNER JOIN link_business_address AS b_a
ON a.address_id = b_a.address_id
AND b_a.business_id = b.business_id <<
WHERE
a.property LIKE :P1 OR
a.street LIKE :P1 OR
a.locality LIKE :P1 OR
a.post_town LIKE :P1 OR
a.county LIKE :P1 OR
a.postcode LIKE :P1 OR
a.notes LIKE :P1
) OR
EXISTS (
SELECT 1
FROM tbl_comms AS c
INNER JOIN link_business_comms AS b_c
ON c.comms_id = b_c.comms_id
AND b_c.business_id = b.business_id <<
WHERE
c.comms LIKE :P1 OR
c.comms_type LIKE :P1 OR
c.comms_purpose LIKE :P1 OR
c.notes LIKE :P1
)
I have been testing this directly in phpMyAdmin. The error I am getting is this…
#1054 - Unknown column 'b.business_id' in 'on clause'
From what I can tell and from what I have read, MySQL processes the outer expressions (outside of the parenthesis) and passes them inside, which is what we want, EXCEPT where a JOIN is concerned.
So, when the query picks up b.business_id from the very first line, it cannot pass it to the
AND b_p.business_id = b.business_id on the 12th, 25th and 40th lines (as marked).
I was convinced this would work. Any other thoughts or suggestions?
I have altered the query to work without a JOIN.
SELECT DISTINCT b.business_id, b.business, b.trading_name, b.notes
FROM tbl_business AS b
WHERE
b.business LIKE :P1 OR
b.trading_name LIKE :P1 OR
b.notes LIKE :P1 OR
EXISTS (
SELECT 1
FROM
tbl_people AS p,
link_business_people AS b_p
WHERE
p.people_id = b_p.people_id AND
b_p.business_id = b.business_id AND
(p.forename LIKE :P1 OR
p.midname LIKE :P1 OR
p.surname LIKE :P1 OR
p.nickname LIKE :P1 OR
p.notes LIKE :P1)
) OR
EXISTS (
SELECT 1
FROM
tbl_address AS a,
link_business_address AS b_a
WHERE
a.address_id = b_a.address_id AND
b_a.business_id = b.business_id AND
(a.property LIKE :P1 OR
a.street LIKE :P1 OR
a.locality LIKE :P1 OR
a.post_town LIKE :P1 OR
a.county LIKE :P1 OR
a.postcode LIKE :P1 OR
a.notes LIKE :P1)
) OR
EXISTS (
SELECT 1
FROM
tbl_comms AS c,
link_business_comms AS b_c
WHERE
c.comms_id = b_c.comms_id AND
b_c.business_id = b.business_id AND
(c.comms LIKE :P1 OR
c.comms_type LIKE :P1 OR
c.comms_purpose LIKE :P1 OR
c.notes LIKE :P1)
)
This works, when I go to the browser page all the records show.
When I use the search field, in this case two records are returned, confirmed by DevTools
But in the browser I get the two returned records AND everything else below them!
Another little headache to sort. I’m getting there, just very slowly 
Hi @patrick, I really can't work out what I am doing wrong here. Why are the address, comms and contacts still displaying? They are from the three queries inside the repeat.
So the main query is working and filtering correctly, it's the repeated queries, once something is entered in the search field, that are not.
The address query is as follows (copied from the action file)
SELECT address.\nFROM link_business_address\nLEFT JOIN tbl_address AS address ON (address.address_id = link_business_address.address_id)\nWHERE link_business_address.business_id = :P1 / {{business_id}} */
Use inner join instead of left join, left join will always return all results from the first table
Are the results really from the query, or is it the repeat region on the html page that is not refreshing the old data?
I wonder if this could be the real reason. I tried changing the JOINs before, but it didn’t any difference.
I have done the simple forcing cache to refresh, and clearing browser data, without any change.
Should I be forcing something to refresh in the server action or server connect?
If you inspect the json, are the extra addresses also in there? It they aren’t in the json, then the server connect is working correctly and the problem lies in the client-side.
Also I see a request for each key press, I suggest using a debounce on the event to have less calls to the server.
@patrick, as shown in this screenshot, only the two addresses are showing, and NOT the extra addresses. So this, as you say, points to an issue with the client-side.
I am staring at the code and struggling to see any issue with it.
HANG ON!
I have just realised that I had the repeat
is="dmx-repeat" id="business" dmx-bind:repeat="sc_contacts_q.data.sa_contacts_repeat"
on the <tbody> instead of the <table>
I have just cut and paste this part from <tbody> to <table>, saved and refreshed the web browser. IT WORKS as expected.
Panic over, apart from not sure how to do the DEBOUNCE. Can you guide me?
Just a question, why will the repeat not work on the <tbody> ?
Debounce is just a timed delay in milliseconds before a key press is processed and sent to the server action. Setting it to a short delay stops the input being sent to the server until the user stops or pauses input. 500ms is generally a good value
