Hello Zeba,

Thanks for showing interest in wpDataTables.

You are correct, it is exactly like this.

Due to the nature of how the SQL/PostgreSQL foreign keys work, the tables that you wish to connect via foreign key relations have to be on the same Database connection.

If they are not on the same Database connection, the foreign key will not work, sadly.

That is the limitation that is currently existing - Our developers hope to find a way to improve it in the future, but we can't say an ETA on it.

You could follow our changeLog page - here we always state any changes/improvements at each Update cycle.

Let me know if you have any further questions. Thanks.

Hello Zeba,

My apologies, I can now see what you meant.

I have to check this with our senior 2nd level Team to make sure that we give you the most up to date information.

As soon as they confirm it, I will immediately come back to you. Thank you for your patience and for clarifying the issue.

Hi, Zeba

We are all cooperating together on this, 

the developers advised us that they are able to successfully create foreign key relations on both local PostgreSQL and an external live PostgreSQL database.

-

Could you possibly help us replicate this, by sending us temporary Access credentials to your database;

and if you can point out more details such as which tables you are trying to connect, and which columns,

so that we can try to remote in, if that is OK?

In case you send any credentials, make sure to mark reply as Private. Thank you.

Hi, Zeba

Thank you for the credentials, i can definitely log into everything now - as well as WP Admin; 

and i see the tables + columns that you pointed out in the database.

We will share all the details with the developers and they will help us replicate the foreign key relations as you described, with the respective tables and columns;

i will advise you on the results as soon as we test everything out.

Thank you.

Hi, Zeba

Thank you very much for your patience.

The developers tested everything.

Basically, for some reason, they are not sure - on your PostgreSql server, using the GROUP BY has issues.

If they try Query like this, it would fail :

SELECT ("wdt_id") AS "wdt_id", ("birthdate") AS "birthdate" FROM 
(SELECT wpdatatable_6."wdt_id",
        wpdatatable_6."name",
        wpdatatable_6."birthdate"
FROM wpdatatable_6
)
 tbl GROUP BY ("wdt_id", "birthdate") ORDER BY "birthdate"

But if they remove GROUP BY, then it works, but it won't group the rows.

- Could you please contact your server support, to see if they perhaps disabled or forbid the GROUP BY?

Looking forward to your reply. Thank you

Hi, Zeba

I will pass this to the devs, and i am waiting for their answer;

as soon as they respond i will pass the details, thank you for that.

And of course, let us know what the server support says. Thank you

Hi, Adriano

Thank you for the latest information.

I will pass it to the devs, as well as pointing out that they are free to test fully as you suggested.

I will keep you posted on their progress.

Thanks.

Hi, Adriano

Thank you for your patience.

- We got update from our developer.

For some reason the brackets after the Group By are not valid on your server, so the dev has excluded that , and now it works.

In order for this to be implemented in the plugin core, we will firstly test more PostGreSQL databases.

Since this is a string column, the foreign key date is returned as it is saved in the database. 1234-05-05

In order to show as it is in wpDataTables, you will have to insert a hook, into the functions.php file of your current theme :

function wpdatatables_filter_cell_by_column_name( $formattedValue, $tableId , $columnHeader)
{ 
//here you can set table id that you need
 // $tableId is int 
// here you will insert table id for which you need this hook
 if ($tableId == 1) {
 //here you will insert orig header for columns where you need to change content 
// $columnHeader is string 
if ($columnHeader == "funcquatro" ) { 
$dateFormat = get_option('wdtDateFormat'); 
// here is logic for converting date format from 
// database to format set in wpdatatables
 $formattedValue = WDTTools::wdtConvertStringToUnixTimestamp($formattedValue, $dateFormat); 
$formattedValue = date($dateFormat, $formattedValue); 
    }
 } 
return $formattedValue;
}
add_filter('wpdatatables_filter_cell_output','wpdatatables_filter_cell_by_column_name', 10, 3);

For the time being, if you update a theme, you will have to repeat this, and change the theme functions file again;

unless if you use a child theme, in that case it will not be overwritten.

-

Let me know how it goes. Thank you.

Hi, Zeba 

As i started reading the message, i was very happy, but then, at the end, when i realised some other changes got lost through the update, of course, that is a shame.

I was not aware that the devs did other changes, apart from the hooks advised in the child theme's PhP file...

I will check it with them - and we will do our best to advise as quick as possible. As soon as they respond i will inform you right away.

Thank you.

Hi, Zeba 

Oh, sorry - somehow i did not see the last message you sent after the previous one.

I am definitely happy now, to see that you found the other updated changes in the /wpdatatables/source/class.wpdatatable.php file.

That is awesome!

Yes, i will check with developers, if they have plans to modify these details in the plugin for the future, or if you should proceed to repeat this indefinitely.

As soon as they confirm it , i will advise on it.

Thank you.

Hi, Zeba

Apologies, i confirmed this a lot quicker than i thought. I found correspondence with the developers, they confirmed that they have to do more testing on this, on various databases and server types; 

but once they complete all the testing, they will work on implementing a permanent fix.

I can't say an ETA on it , but if you wish you can follow our ChangeLog page. At each update, they add patch notes for any change/bug fix/new feature;

you can also find the changeLog on the plugin dashboard in Wp-Admin;

And once they complete all the work for this - they will certainly add it to the patch notes.-

Until then, you will have to do these modifications at each update to overwrite the code.

Hi, Zeba 

I just wanted to follow up on this issue.

We are happy to say that the developers added this in the latest update version 4.4 ,

"

Fixed issue with foreign keys for PostgreSQL database

"

Just wanted to let you know, so from now on you don't have to modify anything when you update the plugin.

Hey, Zeba 

Thank you again for the wonderful review, and all the kind words,

i can not express how much it means to us.

We will of course, do our best to advise as much as possible on the other ticket - and please don't hesitate to open new tickets for any new question or concern.

Thanks again, have a great day