Searching on a string with multiple records of the same value, only brings back one

[roddy-cesoft]

If I have 2 records with the field 'PINK' in them, when I search for 'pink', only one of them is returned.

[seanthomasdevlin]

Hi Roddy, do you mean that you have 2 records with 'PINK' in the same column for both? Could it be that you have other search terms which omit one record. Please provide a sample, or more details. You could also double check the query that is generated from the DB performance page and include that.

[roddy-cesoft]

https://user-images.githubusercontent.com/72715897/121611644-0f940580-ca9c-11eb-8a42-03685cb8507d.mov

https://user-images.githubusercontent.com/72715897/121611654-14f15000-ca9c-11eb-95e8-27c17a3a0832.mov

Here are two sample videos of searching with a single 'z'. The first uses multiple search providers, including the family name, and it is missing a few of the records. The second is when I remove the other search providers, leaving only family name and this works.

When you mention search terms that omit a record; I had considered the matching to be autonomous against each search provider passed in. Does this entail that not finding a value in a search provider can omit a record?

[paronne]

Hi Roddy,

hard to tell what is the issue. If you can share a sample solution where the issue can be reproduced we can investigate. Few things you can check yourself, verify if there is any error in log (if something goes wrong the search may not run at all)

Also you can verify the query generated by the search by printing (after the search executes) databaseManager.getSQL(foundset) & databaseManager.getSQLParameters(foundset) for the form's foundset.

[roddy-cesoft]

Hi,

I have not seen any messages from the search, there are DEBUG messages that show the normal processing but there are none that occur in relation to this search. I will have a go at the SQL being generated after the search.

Thanks,

Roddy

On Sat, 12 Jun 2021 at 00:37, Paolo @.***> wrote:

Hi Roddy,

hard to tell what is the issue. If you can share a sample solution where the issue can be reproduced we can investigate. Few things you can check yourself, verify if there is any error in log (if something goes wrong the search may not run at all)

Also you can verify the query generated by the search by printing (after the search executes) databaseManager.getSQL(foundset) & databaseManager.getSQLParameters(foundset) for the form's foundset.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Servoy/svySearch/issues/26#issuecomment-859627201, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARKY46NGRLHMSX3L6AHSLODTSINR5ANCNFSM46NBGG3Q .

[roddy-cesoft]

Hi,

Thanks for the suggestion; I have been running through different examples this morning. I think the issue is that all relationships used as search providers have to resolve to a related row (the search uses inner joins, which is essential for any kind of speed). In our case, we have an email address relationship which is optional; should a patient not have an email address, then the search will not find any other matching search providers.

This may also have been what you meant by searches occluding results? Would it be the case that all relationships used as search providers have to resolve for any of the providers to match?

Thanks,

Roddy

On Sat, 12 Jun 2021 at 00:37, Paolo @.***> wrote:

Hi Roddy,

hard to tell what is the issue. If you can share a sample solution where the issue can be reproduced we can investigate. Few things you can check yourself, verify if there is any error in log (if something goes wrong the search may not run at all)

Also you can verify the query generated by the search by printing (after the search executes) databaseManager.getSQL(foundset) & databaseManager.getSQLParameters(foundset) for the form's foundset.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/Servoy/svySearch/issues/26#issuecomment-859627201, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARKY46NGRLHMSX3L6AHSLODTSINR5ANCNFSM46NBGG3Q .

[seanthomasdevlin]

Hi Roddy,

The search will use the same join as the relation defines. The criteria are the same when searching foreign tables: ALL search tokens must match ANY of the providers. If you can share the SQL which is generated and the data which is expected we can analyze any potential issues.

[paronne]

Hi Roddy

most likely the different result you see will be due to the Inner Join indeed. As Sean mentioned the search will use same join type as defined in the relation. Therefore changing the join type, or making use of a new relation with joinType = LEFT OUTER JOIN will solve your problem.

Regards, Paolo