I couldn't find an appropriate subject from you list of subjects, so I picked one.
This ticket is really about search and foreign keys while trying to edit a table.
If I have the table set to no editing with a foreign key the search works fine. The instant I add editing for the table, search no longer works.
I suspect the problem is that allowing editing automatically sets the 'Enable server-side processing' to on which I always need to set off to allow search to work.
If yes - I'll ask you to install the Duplicator plugin. It will generate a couple of files which you can send me (along with the log-in credentials), and then I can create an exact copy of your website, see what the issue is and try to resolve it.
Please note that the files will be too large to attach to the ticket, so you can upload them via wetransfer.com and just send me the link.
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
We were looking at tables ID 23 and 27, from what we can see the origin header RecordID is still the same in both tables. You need to update this in the base file and then update it in the SQL query as well.
Do let us know if you need any further assistance.
Sorry, I checked the real website, not the staging website.
Search doesn't work even with Editing disabled, if the table is a server-side table. So the issue is coming from Server-Side. Can you send me the dump of these two database tables, so we can test it locally?
As a reminded, I want to use the s_records_images or s_record_image1 table as the base table and link the s_records table so I can show the Name field and search by name like 'gary'.
I may have missed it in your documentation, but apparently the key used to link as a foreign key must by an integer. The one I was trying to use is a varchar. The query your search was doing was erroring because it didn't enclose the values in the 'in' condition (apparently expecting the values we integer.
Here is an entry from my php error.log file generated when I ran the wpdatatable while testing:
Foreign keys are mainly made to work with integer values, which means if you want to connect a column with a column from another table you have to have integer values. If it's string like in your case it will work with the change but other functionalities like search will not.
For it to work with string values you can try replacing in file
This needs a lot of testing before it can be included in the plugin. Until then you will have to change this until it is included in the plugin's core version which has passed all tests.
You will have to use s_records_images1 and s_records table.
I couldn't find an appropriate subject from you list of subjects, so I picked one.
This ticket is really about search and foreign keys while trying to edit a table.
If I have the table set to no editing with a foreign key the search works fine. The instant I add editing for the table, search no longer works.
I suspect the problem is that allowing editing automatically sets the 'Enable server-side processing' to on which I always need to set off to allow search to work.
Is this a known bug? Is there any workaround?
Hi Ernie
Thank you for reaching out to us.
Could I kindly ask you to send us the query you are using so we can take a closer look and find the reason behind this issue?
Thanks for helping.
Here is the query:
select s.ID, s.Selected, s.RecordID, s.ImageURL, s.ImageURL as ImageLink, s.PageURl, s.Width, s.Height, s.Alt, s.Title from s_records_images as s
Hi Ernie
Could you please try without the alias so it would look like this
select ID, Selected, RecordID, ImageURL, ImageURL, PageURl, Width, Height, Alt, Title from s_records_images
Do let us know if this worked for you.
Hi,
Sorry for the late reply. I decided to create a video, so you could see the issue for yourself.
https://drive.google.com/file/d/1UMV9VMQwrUQcyMXZc0WEnsso-PgK8EZf/view?usp=drivesdk
I hope there is a solution to the problem, or that you are working on an enhancement to your editing functionality.
Best,
Ernie
Hi Ernie
Can you clone your website?
If yes - I'll ask you to install the Duplicator plugin. It will generate a couple of files which you can send me (along with the log-in credentials), and then I can create an exact copy of your website, see what the issue is and try to resolve it.
Please note that the files will be too large to attach to the ticket, so you can upload them via wetransfer.com and just send me the link.
I instead set up a staging area and gave you admin privileges.
Use this URL
https://www.staging2.meetaneed.org/
un = blazenka
pw = TMSpass5%
You should have received a separate email to gain access to wordpress. The login folder is /needlogin.
Hi Ernie
Thank you for that.
If you go to the column settings in the top left corner you will see origin header.
If "store value" or "display value" header is the same as in the table you want to configure the foreign key it will not function properly.
You need to change the origin header in the base as well as in the table.
Do let us know if this helps.
I changed 'RecordID' in s_records_images to 'rid' and the Original Header from 'rid' to 'Expected Name' as the Display Header.
The 'Name' is Display Value I am using from s_records and the Store Value is 'RecordID'.
But that didn't work.
I guess I need more help identify which field names in the underlying tables need to change to what.
And then what should be the set as Display Header, Display Value, and Store Value.
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
Balenka,
I have set you up with an admin account. You should have received an email.
I also created a version of s_record_images with RecordID chaged to rid as you would have seen in the staging site.
So, the two flavors in wpdatatables are s_record_images and s_record_inages1.
Feel free to edit those as much as you need to, as when I ran into this problem I decided not to use them until the problem is resolved.
Best,
Ernie
Hi Ernie
We were looking at tables ID 23 and 27, from what we can see the origin header RecordID is still the same in both tables. You need to update this in the base file and then update it in the SQL query as well.
Do let us know if you need any further assistance.
I'm not sure why you are looking at 23.
IDs 27 and 28 are the base tables I am using and ID 12 is the table I am trying to connect using a foreign key.
ID 12: RecordID
ID 27: RecordID (the two fields I tried to link have the same field name so didn't work).
ID 28: rid (I duplicated ID 28 and after changing the RecordID to rid in the database table as I thought you suggest, it still doesn't work.)
Please provide specifics on what the field name should be in the database tables. I will change it to whatever you suggest.
s_records_images1 (now 'rid')
s_records (now 'RecordID')
Hi Ernie
Sorry, I checked the real website, not the staging website.
Search doesn't work even with Editing disabled, if the table is a server-side table. So the issue is coming from Server-Side. Can you send me the dump of these two database tables, so we can test it locally?
I've provided all three tables.
As a reminded, I want to use the s_records_images or s_record_image1 table as the base table and link the s_records table so I can show the Name field and search by name like 'gary'.
I figured out the issue.
I may have missed it in your documentation, but apparently the key used to link as a foreign key must by an integer. The one I was trying to use is a varchar. The query your search was doing was erroring because it didn't enclose the values in the 'in' condition (apparently expecting the values we integer.
Here is an entry from my php error.log file generated when I ran the wpdatatable while testing:
[15-Mar-2021 22:25:23 UTC] WordPress database error Unknown column 'mlb_rathga01' in 'where clause' for query SELECT SQL_CALC_FOUND_ROWS ID, Selected, rid, ImageURL, PageURl, Width, Height, Alt, Title FROM s_records_images1 WHERE (`s_records_images1`.`ID` LIKE '%gary%' OR `s_records_images1`.`Selected` LIKE '%gary%' OR `s_records_images1`.`rid` IN (mlb_rathga01, nfl_GaryCl00, mlb_durhado01, mlb_rosedo01, mlb_kolbga01, mlb_greenga01, nfl_AlleGa20, mlb_brownga02, mlb_haughga01, nfl_AndeGa20, nfl_andergar02, nfl_AndeGa00, mlb_carigan01, mlb_varshga01, mlb_waynega01, mlb_sheffga01, nfl_ArthGa00, mlb_martzga01, nfl_BaldGa20, nfl_BallGa00, nfl_BankGa00, nfl_BarbGa00, nfl_BarnGa00, nfl_BarnGa01, mlb_brownba01, nfl_BaxtGa20, nfl_BebaGa00, nfl_BerrGa20, nhl_GaryBettman_2018, nfl_BoldGa20, nfl_BracGa20, nfl_BrowGa00, nfl_BrowGa20, nfl_BugeGa20, nfl_BurlGa20, nfl_ButlGa00, nfl_CampGa20, mlb_hollega01, mlb_peterga01, nfl_ClarGa00, mlb_coopega02, nfl_CollGa00, nfl_CranGa20, nfl_CuozGa00, nfl_CutsGa20, mlb_timbega01, nfl_DaniGa00, mlb_bennega01, nfl_DaviGa00, mlb_lancega01, mlb_moorega01, mlb_rossga01, nfl_DownGa00, nfl_DuliGa20, nfl_DunnGa20, mlb_cartega01, mlb_gentrga01, mlb_wagnega01, nfl_ElleGa00, mlb_krugge01, mlb_redusga01, mlb_knottga01, nfl_FamiGa20, nfl_FencGa00, nfl_FinnGa20, nfl_GarrGa00, mlb_grayga01, mlb_pettiga01, nfl_GibsGa99, nfl_GlicGa20, nfl_GreaGa20, nfl_GreeGa01, nfl_GuytGa99, nfl_HaddGa20, nfl_HammGa00, nfl_HarrGa20, nfl_HayeGa20, nfl_HaymGa00, nfl_HensGa20, nfl_HillGa20, nfl_HoffGa20, nfl_HogeGa00, nfl_HrivGa20, nfl_HuffGa00, nfl_HuntGa20, nfl_JeteGa20, nfl_JohnGa00, nfl_JoneGa20, mlb_gaettga01, nfl_KeitGa00, nfl_KerkGa20, nfl_KirnGa20, nfl_KnafGa00, nfl_KosiGa00, nfl_KowaGa20, nfl_KronGa20, nfl_KubiGa00, mlb_wardga01, nfl_LaneGa00, nfl_LarsGa00, mlb_ryersga01, mlb_thomaga01, nfl_LeexGa00, mlb_boydga01, mlb_waslega01, mlb_weissga01, mlb_woodsga01, nfl_LewiGa01, nfl_LewiGa00, nfl_LewiGa21, mlb_eavega01, mlb_rajsiga01, nfl_LoweGa20, mlb_hargiga01, mlb_nolanga01, mlb_suthega01, nfl_MaraGa00, mlb_allenga01, nfl_McDeGa00, mlb_krollga01, mlb_geigega01, mlb_thurmga01, mlb_wilsoga03, nfl_MossGa20, nfl_MoteGa20, nfl_MullGa20, mlb_coopega01, mlb_matthga02, mlb_matthga01, mlb_blaylga01, nfl_NowaGa20, nfl_PadjGa20, nfl_ParrGa00, mlb_lucasga01, bbl_GaryPayton_2013, nfl_PettGa20, nfl_PlumGa20, mlb_bearega01, mlb_ignasga01, nfl_ReasGa20, nfl_RichGa20, mlb_chrisga01, mlb_dottega01, mlb_holmaga01, mlb_wheelga01, mlb_lavelga01, nfl_RobeGa20, mlb_mielkga01, nfl_RussGa00, mlb_sanchga02, mlb_buckega02, nfl_ShirGa00, nfl_SmitGa20, nfl_SpanGa20, nfl_SpanGa21, mlb_roeniga01, mlb_wilsoga02, nfl_StilGa00, mlb_disarga01, mlb_scottga01, nfl_ThomGa21, nfl_TuckGa00, nfl_WalkGa20, nfl_WalkGa00, mlb_alexaga01, mlb_frankwa01, mlb_majewga01, mlb_neibaga01, mlb_serumga01, nfl_WeavGa20, nfl_WellGa00, nfl_WilkGa00, mlb_tayloga01, nfl_WillGa22, bbl_GaryWilliams_2014, nfl_WimmGa20, nfl_WiseGa20, nfl_WoodGa00, nfl_WoolGa20, nfl_ZimmGa00, mlb_glovega01, nfl_GaryKe20, mlb_olsenke01, mlb_jacobja01, mlb_gabrile02, mlb_brownmi01, mlb_foltymi01, mlb_fioremi01, mlb_pottemi01, nfl_GaryOl00, nfl_GaryRu20, nfl_GarySh00, mlb_colemri01, mlb_groombu01, mlb_bellga01, nfl_GaryWi20) OR `s_records_images1`.`ImageURL` LIKE '%gary%' OR `s_records_images1`.`PageURl` LIKE '%gary%' OR `s_records_images1`.`Width` LIKE '%gary%' OR `s_records_images1`.`Height` LIKE '%gary%' OR `s_records_images1`.`Alt` LIKE '%gary%' OR `s_records_images1`.`Title` LIKE '%gary%') ORDER BY `ID` ASC LIMIT 5 made by do_action('wp_ajax_get_wdtable'), WP_Hook->do_action, WP_Hook->apply_filters, wdtGetAjaxData, WPDataTable->queryBasedConstruct
I have since added an integer foreign key to s_records_images1 and tested it. It works great!
Hi Ernie
I'm glad to hear that.
Foreign keys are mainly made to work with integer values, which means if you want to connect a column with a column from another table you have to have integer values. If it's string like in your case it will work with the change but other functionalities like search will not.
For it to work with string values you can try replacing in file
wp-content/plugins/wpdatatables/source/class.wpdatatable.php:1860
you will find this
if (!empty($_POST['search']['value'])) {
so replace that entire if block with this
if (!empty($_POST['search']['value'])) { $search = " ("; for ($i = 0; $i < count($aColumns); $i++) { if (isset($_POST['columns'][$i]) && $_POST['columns'][$i]['searchable'] == "true") { if (isset($wdtParameters['data_types'][$_POST['columns'][$i]['name']]) && in_array($wdtParameters['data_types'][$_POST['columns'][$i]['name']], array('date', 'datetime', 'time'))) { continue; } else { if (isset($wdtParameters['data_types'][$_POST['columns'][$i]['name']])) { if (is_null($wdtParameters['foreignKeyRule'][$_POST['columns'][$i]['name']])) { $search .= $this->getLikeExpression($vendor, $leftSysIdentifier . $tableName . $rightSysIdentifier, $leftSysIdentifier . $aColumns[$i] . $rightSysIdentifier, '%' . addslashes($_POST['search']['value']) . '%') . ' OR '; } else { $foreignKeyRule = $wdtParameters['foreignKeyRule'][$_POST['columns'][$i]['name']]; $joinedTable = WPDataTable::loadWpDataTable($foreignKeyRule->tableId); $distinctValues = $joinedTable->getDistinctValuesForColumns($foreignKeyRule); $distinctValues = array_map('strtolower', $distinctValues); $filteredValues = preg_grep('~' . preg_quote(strtolower($_POST['search']['value']), '~') . '~', $distinctValues); $filteredValues = array_map(function($n) {return "'" . addslashes($n) . "'";}, array_keys($filteredValues)); if (!empty($filteredValues)) { $search .= $leftSysIdentifier . $tableName . "{$rightSysIdentifier}.{$leftSysIdentifier}" . $aColumns[$i] . "{$rightSysIdentifier} IN (" . implode(', ', $filteredValues) . ") OR "; } else { $search .= $leftSysIdentifier . $tableName . "{$rightSysIdentifier}.{$leftSysIdentifier}" . $aColumns[$i] . "{$rightSysIdentifier} = '" . addslashes($_POST['search']['value']) . "' OR "; } } } } } } $search = substr_replace($search, "", -3); $search .= ')'; }This needs a lot of testing before it can be included in the plugin. Until then you will have to change this until it is included in the plugin's core version which has passed all tests.
You will have to use s_records_images1 and s_records table.
Thanks for all your help!
You are most welcome, I'm glad we could help
If there is anything else we can assist you with please don't hesitate to open a new ticket.
Have a wonderful day!