As previously announced via banners and our newsletters, support is no longer available through this platform.

For easier navigation, you can still click on "Submit a Ticket" here, choose the appropriate category, and you'll be redirected to the correct support channel for your plugin.

You can still access your previous tickets and browse public tickets, but please note that responding to tickets is no longer possible.

Paid customers: Please log in to your store account for support.

Pre-purchase questions: Use the support widget in the bottom-right corner of our websites:
https://wpamelia.com
https://wpdatatables.com
https://wpreportbuilder.com

Okay
  Public Ticket #2583747
Sql error
Closed

Comments

  • Roman started the conversation
    October 28, 2020 at 10:13am

    HELLO
    i have sql code


    SELECT 
        SUM(count) as 'count1',
        SUM(sum) as 'sum1',
        CASE WHEN ( SUM(count) = 0 )
           THEN 0
        ELSE  ( SUM(sum) / SUM(count) ) 
           END as 'avg',
        year

        FROM 
            (
            SELECT 
                DISTINCT ( EXTRACT(YEAR FROM t.ipodate) ) as 'year', 
                COUNT(t.ipodate) as 'count',
                 COALESCE ( SUM( ( COALESCE(t.ipoproceeds,0)+COALESCE(t.overallotmentproceeds,0) ) ), 0)  as 'sum',
                 ( SUM( (  COALESCE(t.ipoproceeds,0)+COALESCE(t.overallotmentproceeds,0) ) ) /  COUNT(t.ipodate) )  as 'average'
            FROM `wp_wpdatatable_1` t
            WHERE t.archived = 'No' AND EXTRACT(YEAR FROM t.ipodate) > 0
            GROUP BY  EXTRACT(YEAR FROM t.ipodate)  

            UNION ALL

            SELECT 
                DISTINCT ( EXTRACT(YEAR FROM t.ipodate) ) as 'year', 
                COUNT(t.ipodate) as 'count',
                SUM( COALESCE( t.archivedsize, 0 ) ) as 'sum',
                SUM( COALESCE( t.archivedsize, 0 ) ) / COUNT(t.ipodate) as 'average'
            FROM `wp_wpdatatable_1` t
            WHERE t.archived= 'Yes' AND EXTRACT(YEAR FROM t.ipodate) > 0
            GROUP BY  EXTRACT(YEAR FROM t.ipodate)  
            ORDER BY `year`  DESC  
            )
            main
      GROUP BY YEAR



    it 's executed directly in sql without problem https://prnt.sc/v8jl0n

    but in wpdatatbles i got error https://prnt.sc/v8jled


    how can i use this sql in my wpdatatables plugin and where is problem here?


  •  2,576
    Aleksandar replied
    October 29, 2020 at 12:22pm

    Hello Roman

    Thank you for your purchase.

    If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

    Please note some this when working with the server-side processing feature:

    • Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
    • Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.

    Kind Regards, 

    Aleksandar Vuković
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables