Update Include in list query - All Elements

[Geller]

Hi,

I discovered it was easy enough to update the fabrik_elements table, params column, to set the "inc_in_adv_search":1, string to "inc_in_adv_search":0, and this is how the params value looked before the change. Reason I include this is I found after changing the string, the element was not included in the Advanced Search which was great as I did this for all but the elements showing in the list so a user would not get overwhelmed by the number of selections.

{"rollover":"","comment":"","sub_default_value":"","sub_default_label":"","element_before_label":1,"allow_frontend_addtocheckbox":0,"database_join_display_type":"dropdown","joinType":"simple","join_conn_id":-1,"date_table_format":"Y-m-d","date_form_format":"Y-m-d H:i:s","date_showtime":0,"date_time_format":"H:i","date_defaulttotoday":1,"date_firstday":0,"multiple":0,"allow_frontend_addtodropdown":0,"password":0,"maxlength":255,"text_format":"text","integer_length":6,"decimal_length":2,"guess_linktype":0,"disable":0,"readonly":0,"ul_max_file_size":16000,"ul_email_file":0,"ul_file_increment":0,"upload_allow_folderselect":1,"fu_fancy_upload":0,"upload_delete_image":1,"make_link":0,"fu_show_image_in_table":0,"image_library":"gd2","make_thumbnail":0,"imagepath":"\/","selectImage_root_folder":"\/","image_front_end_select":0,"show_image_in_table":0,"image_float":"none","link_target":"_self","radio_element_before_label":0,"options_per_row":4,"ck_options_per_row":4,"allow_frontend_addtoradio":0,"use_wysiwyg":0,"my_table_data":"id","update_on_edit":0,"view_access":1,"show_in_rss_feed":0,"show_label_in_rss_feed":0,"icon_folder":-1,"use_as_row_class":0,"filter_access":1,"full_words_only":0,"inc_in_adv_search":0,"sum_on":0,"sum_access":0,"avg_on":0,"avg_access":0,"median_on":0,"median_access":0,"count_on":0,"count_access":0}

When I took the same approach to the params string "include_in_list_query":"0", since I use only ten of the close to 300 elements in this List I needed to speed up the queries as they are very slow, and huge when dumped in a debug as so many elements are needlessly being included. I then noticed this params string is only in several elements I had manually set this from the default of Yes or 1 to No or 0. Then I noticed in the above the string was not there. I changed the value to No or 0 using Fabril and now this is the table cell's params value for the same element:

{"bootstrap_class":"input-medium","width":"40","height":"6","textarea_showlabel":"1","textarea_placeholder":"","use_wysiwyg":"0","wysiwyg_extra_buttons":"1","textarea-showmax":"0","textarea-maxlength":"255","textarea_limit_type":"char","textarea-tagify":"0","textarea_tagifyurl":"","textarea-truncate-where":"0","textarea-truncate-html":"0","textarea-truncate":"0","textarea-hover":"1","textarea_hover_location":"top","show_in_rss_feed":"0","show_label_in_rss_feed":"0","use_as_rss_enclosure":"0","rollover":"","tipseval":"0","tiplocation":"top-left","labelindetails":"0","labelinlist":"0","comment":"","edit_access":"1","edit_access_user":"","view_access":"1","view_access_user":"","list_view_access":"1","encrypt":"0","store_in_db":"1","can_order":"0","alt_list_heading":"","custom_link":"","custom_link_target":"","custom_link_indetails":"1","use_as_row_class":"0","include_in_list_query":"0","always_render":"0","icon_hovertext":"1","icon_file":"","icon_subdir":"","filter_length":"20","filter_access":"1","full_words_only":"0","filter_required":"0","filter_build_method":"0","filter_groupby":"text","inc_in_adv_search":"0","filter_class":"input-medium","filter_responsive_class":"","tablecss_header_class":"","tablecss_header":"","tablecss_cell_class":"","tablecss_cell":"","sum_on":"0","sum_label":"Sum","sum_access":"1","sum_split":"","avg_on":"0","avg_label":"Average","avg_access":"1","avg_round":"0","avg_split":"","median_on":"0","median_label":"Median","median_access":"1","median_split":"","count_on":"0","count_label":"Count","count_condition":"","count_access":"1","count_split":"","custom_calc_on":"0","custom_calc_label":"Custom","custom_calc_query":"","custom_calc_access":"1","custom_calc_split":"","custom_calc_php":"","validations":[]}

It appears the use of the Fabrik component to update a default of Yes to No for the element added many other strings to the params string, including the missing "include_in_list_query":"0", string. If the string was included by default in the original params for the element as "include_in_list_query":"1", (which keeps it working the way it does now) would allow changing it as easily as I did to change the "inc_in_adv_search":1, string in params to "inc_in_adv_search":0, it would alleviate what appears to be having to manually submit close to 300 elements to add the string to params and change it to 0.

My question is: Is there any way to add the "inc_in_adv_search":1, string to params and have Fabrik recognize it as being disabled. My assumption is it has to have the same format as the second params value above. Or is there an easier way to do this as I am sure the params must vary somewhat by element.

Sincerely
Stony Creek Consulting, LLC
Joseph J. Geller

 

[Geller]

Hi,

Guess I should have waited a few more minutes before posting.

I now realize this would vary by element plugin. It appears params is initialized with standard default values regardless of the plugin, after installation It also appears the element's params is updated with all the parameter values for the element if you use Fabrik to change one of them, at least that's what it appears to be for the element's List view settings.

Since Include in advanced search (which is a parameter in Edit List, Details) is in the params after Fabrik installation, that was easy to change for all the elements I did not want to appear in Advanced Search. Update all to 0 in each element and then update ones you want to display in the Advanced Search list to 1. Very simple, very easy. I am going to use the same approach to update the params for each element I am not displaying in the list, using the string Fabrik updates the element with, depending on the plugin, but have the "include_in_list_query":"1", parameter set to "include_in_list_query":"0", to override the Fabrik default of including all elements in a list.

I understand why the default exists for both. It allows maximum flexilbility and assurance the queries will work out of the box and they do. But including two columns in the Element List with these parameters, with toggling allowed on each column's check boxes in their entirety, would provide a way for a user to change these easily. In my case, 300 elements which I do not need in the list query. As a developer, trying to debug large queries, with most of it consisting of elements that are never used, plus the impact on delivery time for the result set, is a bit frustrating. Including this in a future release would be much appreciated and benefit an excellent extension.

I have used Fabrik for many years and will continue to do so. It's the best extension I have used in close to ten years. If the push is to be taking care of some priorities, I hope this is placed among them. Since I am a developer I can handle this, but for a user, to tell them to manually maintain 300 elements? They would tell me "I don't think so."

Sincerely
Stony Creek Consulting, LLC
Joseph J. Geller

 

[Geller]

Hi,

Caution! - The approach I am using will cause all elements to revert to their default states for the plugin. I have used it and it works on a complicated list involving multiple tables and joins with group-by. I ran into this issue before I started changing any elements from their standard params value, inserted at install according to the plugin assigned to the element. Also this is risky and can easily trash your list so be careful.

I am changing the two parameters this way to save the days of work to manually exclude the element from the Advanced Search. That can be done with no impact to the element's params as long as you know what you are doing and change only the 0 in the "inc_in_adv_search":0 string in params to a 1 each element in your list, regardless of whether it is displayed or not in the list. It will still appear in the Advanced Search.

It's easy to do with a query that updates params in fabrik_elements table by replacing the 1 with a 0 in the "inc_in_adv_search":0 string within the params value. I changed the second parameter only to to see if excluding 300 elements from the query will improve performance. If not it's time to look at the MySQL server performance as my client's main complaint was - too slow. But this may be an issue with the MySQL server tuning or missing indexes, etc. and not with Fabrik.

Sincerely
Stony Creek Consulting, LLC
Joseph J. Geller

 

[Geller]

Hi,

Post Mortem:

This worked very well to cut execution time and size of the query way down. Page load time went from 29 to 9 seconds!!! I am verifying this benchmark but I was impressed; my client stopped tapping their fingers waiting for a response. Nine is bad enough but page had a five second overhead to begin with and needs some optimization. Query is not trivial with five joins and several Calculated Elements using a lot of PHP string functions. Query itself went down from two pages to only a half page and I verified using Debug only Elements that are needed are used. Pretty much took 290 elements out of the query that were not necessary.

I am looking at this some more to create a Fabrik plugin that initializes elements this way by element plugin for "include_in_list_query" and "inc_in_adv_search" strings in the fabrik_elements table, params column, from 1 to 0. Then you can select just the elements you want to include in the List's Navigation Bar drop-down lists for Group By and Advanced Search so the List functions correctly. I am sure some other items will need to be addressed by the plugin such as checking if items have been changed from default so they are left as is, etc., plus other safeguards. This simple usage did work in concept and something I have been meaning to do for some time after discovering the defaults, especially in the inc_in_adv_search.

More to come.

Sincerely,