DataTables Server-side MySql Query

EslamENE

Hi i'd like to thank you about the great DataTable tool
i'd like to use Server-Side on my app but i usually use a long and dynamic query's such as Exp.

SELECT sales_forecast.ID
, sales_forecast.Item_Code
, sales_forecast.Representative_NO
, sales_forecast.Year
, items.Item_Name
, items.Unit_Value
, items.Unit
, items.Company
, sum(sales_forecast.M_01)  AS SUM_M_01
, sum(sales_forecast.M_02)  AS SUM_M_02
, sum(sales_forecast.M_03)  AS SUM_M_03
, sum(sales_forecast.M_04)  AS SUM_M_04
, sum(sales_forecast.M_05)  AS SUM_M_05
, sum(sales_forecast.M_06)  AS SUM_M_06
, sum(sales_forecast.M_07)  AS SUM_M_07
, sum(sales_forecast.M_08)  AS SUM_M_08
, sum(sales_forecast.M_09)  AS SUM_M_09
, sum(sales_forecast.M_10)  AS SUM_M_10
, sum(sales_forecast.M_11)  AS SUM_M_11
, sum(sales_forecast.M_12)  AS SUM_M_12
, sum(sales_forecast.M_01 * sales_forecast.Q_V_01)  AS SUM_M_V_01
, sum(sales_forecast.M_02 * sales_forecast.Q_V_01)  AS SUM_M_V_02
, sum(sales_forecast.M_03 * sales_forecast.Q_V_01)  AS SUM_M_V_03
, sum(sales_forecast.M_04 * sales_forecast.Q_V_02)  AS SUM_M_V_04
, sum(sales_forecast.M_05 * sales_forecast.Q_V_02)  AS SUM_M_V_05
, sum(sales_forecast.M_06 * sales_forecast.Q_V_02)  AS SUM_M_V_06
, sum(sales_forecast.M_07 * sales_forecast.Q_V_03)  AS SUM_M_V_07
, sum(sales_forecast.M_08 * sales_forecast.Q_V_03)  AS SUM_M_V_08
, sum(sales_forecast.M_09 * sales_forecast.Q_V_03)  AS SUM_M_V_09
, sum(sales_forecast.M_10 * sales_forecast.Q_V_04)  AS SUM_M_V_10
, sum(sales_forecast.M_11 * sales_forecast.Q_V_04)  AS SUM_M_V_11
, sum(sales_forecast.M_12 * sales_forecast.Q_V_04)  AS SUM_M_V_12
, sum(sales_forecast.M_01 + sales_forecast.M_02 + sales_forecast.M_03) AS SUM_Q_01
, sum(sales_forecast.M_04 + sales_forecast.M_05 + sales_forecast.M_06) AS SUM_Q_02
, sum(sales_forecast.M_07 + sales_forecast.M_08 + sales_forecast.M_09) AS SUM_Q_03
, sum(sales_forecast.M_10 + sales_forecast.M_11 + sales_forecast.M_12) AS SUM_Q_04
, sum((sales_forecast.M_01 + sales_forecast.M_02 + sales_forecast.M_03) * sales_forecast.Q_V_01) AS SUM_Q_V_01
, sum((sales_forecast.M_04 + sales_forecast.M_05 + sales_forecast.M_06) * sales_forecast.Q_V_02) AS SUM_Q_V_02
, sum((sales_forecast.M_07 + sales_forecast.M_08 + sales_forecast.M_09) * sales_forecast.Q_V_03) AS SUM_Q_V_03
, sum((sales_forecast.M_10 + sales_forecast.M_11 + sales_forecast.M_12) * sales_forecast.Q_V_04) AS SUM_Q_V_04
, sum(sales_forecast.M_01 + sales_forecast.M_02 + sales_forecast.M_03 + sales_forecast.M_04 + sales_forecast.M_05 + sales_forecast.M_06 + sales_forecast.M_07 + sales_forecast.M_08 + sales_forecast.M_09 + sales_forecast.M_10 + sales_forecast.M_11 + sales_forecast.M_12) AS Total_Year_Qty
FROM
    sales_forecast
INNER JOIN items
    ON sales_forecast.Item_Code = items.Item_Code
$WHERE
GROUP BY
    sales_forecast.Item_Code;

how can i use Datatable server-side to handle such a query ??

pansengtat

Hi @EslamENE,

I too am also wondering how I can convert an existing MySQL query containing SUM(), GROUP BY and other aggregate functions into the current syntax used in DataTables and Editor.

I happened to stumble upon this PHP wrapper library by @numberone called IgnitedDataTables, and you can see the thread at http://datatables.net/forums/discussion/5133/ignited-datatables-php-library/p1 for more info. Apparently, I am figuring out how I can convert SUM() functions into the format used in DataTables with the plug-in(s) I mentioned.

Is this what you were looking for?