Hello,

I've tried to enter query syntax to import data from PostgreSql. Query is fine for sure, it works under pgAdmin query tool. 

Once I've pasted it into wpdatatables, an error occured. I re-write a query with 'ROW_NUMBER' instead of 'GROUP BY' and then it works.

Wpdatatables plugin does not support 'GROUP BY' query syntax? Please find below exact error info.

BR,

Marek

unknown [expr_type] = const in "GROUP" [0]

Hello Aleksandar,

thanks for the reply. My PostgreSQL query looks as follows:

 SELECT tab."ID",
    tab."dStart",
    tab."dEnd",
    date_part('day'::text, tab."dEnd" - tab."dStart") AS "dDiff"
   FROM ( SELECT "vAdInfo"."ID",
            min("vAdInfo"."DownloadDate") AS "dStart",
            max("vAdInfo"."DownloadDate") AS "dEnd"
           FROM oto."vAdInfo"
          GROUP BY "vAdInfo"."ID") tab;

When I store it on db server with 'CREATE VIEW' and paste just SELECT * FROM result_view, it works fine.

Aleksandar,

Please consider 2 sql scripts. Both runs directly on PostgreSQL database (via PgAdmin4).

Script 1:

SELECT tab."ID",
    tab."dStart",
    tab."dEnd"
FROM ( 
    SELECT "ID",
        min("DownloadDate") AS "dStart",
        max("DownloadDate") AS "dEnd"
    FROM oto."vAdInfo"
    GROUP BY "ID"
    ) tab

Script 2:

SELECT tmin.*, tmax."dEnd"
FROM (
    SELECT "ID", "DownloadDate" as "dStart"
    FROM (
        SELECT "ID", "DownloadDate",
            ROW_NUMBER() OVER(PARTITION BY "ID" ORDER BY "DownloadDate" ASC) AS rn
        FROM oto."vAdInfo") tab
        WHERE tab.rn=1
    ) tmin
JOIN (
    SELECT "ID", "DownloadDate" as "dEnd"
    FROM (
        SELECT "ID", "DownloadDate",
            ROW_NUMBER() OVER(PARTITION BY "ID" ORDER BY "DownloadDate" DESC) AS rn
        FROM oto."vAdInfo"
    ) tab
    WHERE tab.rn=1) tmax ON tmax."ID"=tmin."ID"

Only Script 2 runs through wpDataTables plugin. Don't you think it's strange? ;)

Prefix 'oto' means database schema. All the tables are in the same database but there are multiple schemas thus pure table name ("vAdInfo") it's not sufficient for querying. There is only 1 table named "vAdInfo" in whole database but prefix 'oto' is still mandatory (at least in pgAdmin4).

I regret to inform you that at the moment direct DB access for you it's not possible. Tommorow we'll try to figure out a workaround for this.

BR,

Marek

Hello Aleksandar,

nice to see you again :)

Please don't worry on those queries as it's pretty easy to find a workaround of such problems. I've posted it to help other customers by showing possible limitations of wpdatatables. And to help you as you probably have some QA or other test of your software.

BR

Marek