Search User List group field

1 month 2 days ago #324386 by ericmuc
Search User List group field was created by ericmuc
Hi,

I have two dependend fields (level1-level2) in a field group.
I have a user list with simple exact seach.
If I search for the level1 entry the user with this field value is shown.
If I search for the level1 and and wrong level2 entry the user is further shown, inspite of wrong level2.

I tested this scenario without a field group and it works.

Can you please check this?
Thanks, best regards
Eric

Please Log in to join the conversation.

1 month 1 day ago #324396 by krileon
Replied by krileon on topic Search User List group field
Field Group searching is always OR cases. It has to do this because we have to search inside of all of the potential rows. Example of how the SQL comes out.

  AND ((JSON_SEARCH( LOWER( ue.`cb_fieldgroup` ), 'one', '%test%', NULL, '$[*].cb_fieldgroup_text' ) IS NOT NULL OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[0].cb_fieldgroup_date' ) ) >= '1911-01-01' 
  AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[0].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[1].cb_fieldgroup_date' ) ) >= '1911-01-01' 
  AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[1].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[2].cb_fieldgroup_date' ) ) >= '1911-01-01' 
  AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[2].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[3].cb_fieldgroup_date' ) ) >= '1911-01-01' 
  AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[3].cb_fieldgroup_date' ) ) <= '1929-01-01') OR (LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[4].cb_fieldgroup_date' ) ) >= '1911-01-01' 
  AND LOWER( JSON_EXTRACT( ue.`cb_fieldgroup`, '$[4].cb_fieldgroup_date' ) ) <= '1929-01-01')))

Notice how cb_fieldgroup_date is checked 5 times. This is because the field allows for 5 rows. We've to extract the date and compare the search value against each row individually since JSON_SEARCH can't do comparisons like this. If those were set to AND it'd never find a user.

Have added a feature ticket to look into supporting AND case again, but it's likely not possible with our current search behavior without crazy amounts of nesting that'll unoptimize the query even more. I don't recommend using field groups if you depend on searching their contents as it's inaccurate searching into JSON strings.

forge.joomlapolis.com/issues/8467

It becomes an even bigger problem when we optimize away JSON_EXTRACT in favor of JSON_SEARCH as we then can't separate the OR cases by row.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: ericmuc

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.510 seconds

Facebook Twitter LinkedIn