Search all doesn't work with latest Githubs!

juuser

juuser

Well-Known Member
Hello,

I have noticed a bad problem with search all function. When I edit element and select "Always render" search all from that field works fine. After some time it stops searching from that field. If I edit and save that field, it starts to work again for a while.

It occurs with both, main table and joined table elements and with all my tables.

I hope you guys can fix it as it is really confusing for users when they don't find the records that actually exist.

Thanks!
 
is this only for one element? If so which element is it - can you point us at the page?
thx
Rob
 
I'm struggling to understand how the always_render setting could affect searching.

The only place we use that is in formatData() in the list model, which is called way after all filtering and row selection is done.

All "Always Render" does is add any elements with that set to Yes to the list of elements we render (format for display), that are not set to show in the list. Usually, we don't render elements that aren't displayed in the list, as it's a pointless overhead. But in certain corner cases, we do need a non-displayed element to be formatted, for instance if you include it in something like a map viz bubble template, or maybe a calc element.

I've tracked everything that new code does, and I really, really don't see how it could be affecting the search all feature.

I'm not saying you aren't seeing bizarre behavior, I just don't think it has anything to do with that feature. Not entirely sure how to proceed with this one.

-- hugh
 
Thank you for the explanation. It just seemed (according to it's behavior) that the "always render" function was a substitute to "include in search all" as the latter disappeared from latest Githubs.

Anyway, with upgrading Joomla and to latest Github, this problem disappeared, but now having the same problem as described in this thread:
http://fabrikar.com/forums/showthread.php?t=30993

Getting the following error when searching joined element from search all field:

getData: Unknown column 'prefix_tooted_kliendid_0.Array' in 'where clause' SQL=SELECT COUNT(DISTINCT `prefix_tooted_vormi_liikumised`.`id`) AS t FROM prefix_tooted_vormi_liikumised LEFT JOIN `prefix_users` AS `prefix_users` ON `prefix_users`.`id` = `prefix_tooted_vormi_liikumised`.`viimati_muutis` LEFT JOIN `prefix_tooted_kliendid` AS `prefix_tooted_kliendid` ON `prefix_tooted_kliendid`.`id` = `prefix_tooted_vormi_liikumised`.`vormi_sihtkoht_id` LEFT JOIN `prefix_tooted_vormid` AS `prefix_tooted_vormid` ON `prefix_tooted_vormid`.`id` = `prefix_tooted_vormi_liikumised`.`vormi_id` LEFT JOIN `prefix_tooted_vormid_saatmise_pohjused` AS `prefix_tooted_vormid_saatmise_pohjused` ON `prefix_tooted_vormid_saatmise_pohjused`.`id` = `prefix_tooted_vormi_liikumised`.`saatmise_pohjus` LEFT JOIN `prefix_tooted_nimekiri` AS `prefix_tooted_nimekiri` ON `prefix_tooted_nimekiri`.`vormi_id` = `prefix_tooted_vormi_liikumised`.`vormi_id` LEFT JOIN `prefix_tooted_kliendid` AS `prefix_tooted_kliendid_0` ON `prefix_tooted_kliendid_0`.`id` = `prefix_tooted_nimekiri`.`klient` WHERE ( ( LOWER(`prefix_tooted_nimekiri`.`id`) REGEXP LOWER('searchterm') OR `prefix_tooted_kliendid_0`.Array REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_nimekiri`.`toote_kood`) REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_nimekiri`.`toote_nimi`) REGEXP LOWER('searchterm') OR (LOWER(`prefix_tooted_nimekiri`.`toote_staatus`) REGEXP 'LOWER(\'searchterm\')' OR LOWER(`prefix_tooted_nimekiri`.`toote_staatus`) LIKE '[\"LOWER(\\'searchterm\\')\"%' OR LOWER(`prefix_tooted_nimekiri`.`toote_staatus`) LIKE '%\"LOWER(\\'searchterm\\')\"%' OR LOWER(`prefix_tooted_nimekiri`.`toote_staatus`) LIKE '%\"LOWER(\\'searchterm\\')\"]') OR LOWER(`prefix_tooted_nimekiri`.`mallide_lisainfo`) REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_nimekiri`.`toote_lisainfo`) REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_nimekiri`.`joonise_versioon`) REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_vormi_liikumised`.`id`) REGEXP LOWER('searchterm') OR `prefix_tooted_vormid`.`meie_vormi_nr` REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_vormi_liikumised`.`toote_kood_nimi`) REGEXP LOWER('searchterm') OR `prefix_tooted_kliendid`.`kliendi_nimi` REGEXP LOWER('searchterm') OR `prefix_tooted_vormid_saatmise_pohjused`.`saatmise_pohjus` REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_vormi_liikumised`.`liikumise_lisainfo`) REGEXP LOWER('searchterm') OR `prefix_users`.`name` REGEXP LOWER('searchterm') OR LOWER(`prefix_tooted_vormi_liikumised`.`date_time`) REGEXP LOWER('searchterm') OR `prefix_tooted_kliendid_0`.Array REGEXP LOWER('searchterm') OR `prefix_tooted_vormid`.`meie_vormi_nr` REGEXP LOWER('searchterm') OR `prefix_tooted_kliendid`.`kliendi_nimi` REGEXP LOWER('searchterm') OR `prefix_tooted_vormid_saatmise_pohjused`.`saatmise_pohjus` REGEXP LOWER('searchterm') OR `prefix_users`.`name` REGEXP LOWER('searchterm')) )
 
I had a similar thing happen last week. The code was adding a "_0" to one particular joined column. However, I never figured out what was going on. I looked through the fabrik_joins table and didn't find anything out of the ordinary, except that there were some leftover joins (from long ago). It solved itself when I reimported the database (I'm moving servers right now. But all that probably doesn't help you.
 
It looks like I'm having a related problem. I have a table with three joined tables. The query takes at least a minute to complete when searching for a term through search all. I have limited the number of fields the search all would search.

Here's the fabrikdebug for table getJoinMergeTotalRecords:
(The search all term I searched for was "elbert")
I'm not getting an error, but should the query look like this?

SELECT COUNT(DISTINCT `snow_data`.`ID`) AS t FROM snow_data
LEFT JOIN `snow_layers` AS `snow_layers` ON `snow_layers`.`snow_id` = `snow_data`.`ID`
LEFT JOIN `snow_temp` AS `snow_temp` ON `snow_temp`.`snow_id` = `snow_data`.`ID`
LEFT JOIN `snow_other` AS `snow_other` ON `snow_other`.`snow_id` = `snow_data`.`ID`
LEFT JOIN `env_mon_sites` AS `env_mon_sites` ON `env_mon_sites`.`ID` = `snow_data`.`site_id`
LEFT JOIN `states` AS `states` ON `states`.`ab` = `snow_data`.`state` WHERE ( ( LOWER(`snow_data`.`observers`) REGEXP LOWER('elbert')
OR LOWER(`snow_data`.`location`) REGEXP LOWER('elbert')
OR LOWER(`snow_data`.`county`) REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')
OR LOWER(`snow_data`.`total_depth`) REGEXP LOWER('elbert')
OR LOWER(`snow_data`.`notes`) REGEXP LOWER('elbert')
OR (LOWER(`snow_other`.`type`) REGEXP 'LOWER(\'elbert\')'
OR LOWER(`snow_other`.`type`) LIKE '[\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`type`) LIKE '%\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`type`) LIKE '%\"LOWER(\\'elbert\\')\"]')
OR (LOWER(`snow_other`.`rating`) REGEXP 'LOWER(\'elbert\')'
OR LOWER(`snow_other`.`rating`) LIKE '[\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`rating`) LIKE '%\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`rating`) LIKE '%\"LOWER(\\'elbert\\')\"]')
OR (LOWER(`snow_other`.`quality`) REGEXP 'LOWER(\'elbert\')'
OR LOWER(`snow_other`.`quality`) LIKE '[\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`quality`) LIKE '%\"LOWER(\\'elbert\\')\"%'
OR LOWER(`snow_other`.`quality`) LIKE '%\"LOWER(\\'elbert\\')\"]')
OR `states`.`state` REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')
OR `states`.`state` REGEXP LOWER('elbert')) )
 
Okay, I think I found something. I rebuilt the joins above from scratch and the all started working. But then it started happening again (the \\\ stuff). If I went into the element param field for the problematic element and added "inc_in_search_all":"0" then started working fine and stopped spitting out the [\"LOWER(\\\'elbert\\\')\"%'-like stuff.
 
So, here's what I think is happening...at least for my problem. Wasn't there a short window when inc_in_search_all was a parameter. Could that parameter be still affecting the generated queries?
 
JUUSER, hope I'm not taking over this thread...but it might relate.

I'm looking at model/list.php public function &getFilterArray(). In
PHP: 
if (!array_key_exists($i, $sqlCond) || $sqlCond[$i] == '')
The $query is being generated properly sometimes. Sometimes it looks like this:
LOWER(`sal_data`.`board`) REGEXP LOWER('mingus')

Othertimes it pumps out this multiple sets of weirdness for a different element:
(LOWER(`sal_data`.`d_t1`) REGEXP 'LOWER(\'mingus\')' OR LOWER(`sal_data`.`d_t1`) LIKE '[\"LOWER(\\\'mingus\\\')\"%' OR LOWER(`sal_data`.`d_t1`) LIKE '%\"LOWER(\\\'mingus\\\')\"%' OR LOWER(`sal_data`.`d_t1`) LIKE '%\"LOWER(\\\'mingus\\\')\"]')

When it behaves like the second one, it doesn't seem to be calling this function: $elementModel->getFilterQuery from model/elements.php. Shouldn't it be?

I'm stumped.

Cheesegrits, I have upgraded this particular site to PHP 5.3.3...
 
I'm still having trouble. I've been recreating elements to try to fix it, but it seems to have to do with the fields that are dropdowns.
 
I found that if I rebuild my problematic elements, they worked. However, the bulky code:

LOWER(`sal_data`.`d_t1`) LIKE '%\"LOWER(\\\'mingus\\\')\"]')

...is still there for the dropdowns. And I just can't figure out why it's pumping that out rather than the correct code. (I suppose there might be a reason for it.)

On line 4448 of model/list.php
PHP: 
$query = $elementModel->getFilterQuery($key, $condition, $value, $originalValue, $this->filters['search_type'][$i]);
this line is not running properly for the elements with problems. So I think something's going on with the $elementModel which is defined earlier in the function. But it's not making sense to me.

But my site isn't broken anymore, but the search all queries that have all the /// repetitions are taking a long time for mysql to execute.
 
the tripple backslash is correct as well as the mutliple searches
We store dropdown data as json:

Code: 
["mingus", "other", "mingus2", "mingus3"]
so if you are doing a search all on that type of data then you are going to have to include several searches to try to match "%mingus%" based on whether its stored at the beginning, middle or end of the json array.

Have you checked to see that all search all fields are indexed in mySQL?
 
Okay. Thanks for the clarification. For one of my searches, it was so bulky, that it was taking 30-60 secs to process, but I've used your new method for adding elements in the list and that has helped. And rebuilding the elements that were causing trouble solved my errors.
 
Now have the following error:

getData: Unknown column 'prefix_tooted_kliendid.Array' in 'where clause' SQL=SELECT COUNT(DISTINCT `prefix_tooted_vormi_hindamised`.`id`) AS t FROM prefix_tooted_vormi_hindamised LEFT JOIN `prefix_tooted_vormid` AS `prefix_tooted_vormid` ON `prefix_tooted_vormid`.`id` = `prefix_tooted_vormi_hindamised`.`vormi_id` LEFT JOIN `prefix_users` AS `prefix_users` ON `prefix_users`.`id` = `prefix_tooted_vormi_hindamised`.`viimati_muutis` LEFT JOIN `prefix_users` AS `prefix_users_0` ON `prefix_users_0`.`id` = `prefix_tooted_vormi_hindamised`.`kande_tegija` LEFT JOIN `prefix_users` AS `prefix_users_1` ON `prefix_users_1`.`id` = `prefix_tooted_vormi_hindamised`.`vormi_hindaja` LEFT JOIN `prefix_tooted_nimekiri` AS `prefix_tooted_nimekiri` ON `prefix_tooted_nimekiri`.`vormi_id` = `prefix_tooted_vormi_hindamised`.`vormi_id` LEFT JOIN `prefix_tooted_kliendid` AS `prefix_tooted_kliendid` ON `prefix_tooted_kliendid`.`id` = `prefix_tooted_nimekiri`.`klient` WHERE ( ( `prefix_tooted_kliendid`.Array REGEXP LOWER('ensto') OR `prefix_tooted_kliendid`.Array REGEXP LOWER('ensto')) )

"prefix_tooted_kliendid" is joined table element. It has field filter on it :( When I disable "search all" on that field, it works ok.
 
It looks like something is happening with the WHERE statement ...kliendid`.Array.... part. Did you try resaving those joined elements? Also, try re-setting up the search for that element.
 
Try adding more elements to your search all within your List > Filters > Elements. Then paste the error here.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 152 (members: 3, guests: 149)
    Back
    Top