Search User List group field
- ericmuc
- OFFLINE
-
Platinum Member
- Posts: 411
- Thanks: 30
- Karma: 2
Add-ons
Add-ons
3 years 1 week 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
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.
krileon
Team Member- OFFLINE
- Posts: 68528
- Thanks: 9093
- Karma: 1434
3 years 1 week ago #324396
by krileon
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.
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.
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.
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: beat, nant, krileon
Time to create page: 0.192 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Developer Members Support
- Search User List group field