raw sql query for Editor

zapata

I often have to write complex SQL query for detailed reports. Below codes are not suitable and not easy for Editor. Does Editor present us RAW query method to get Datatables JSON?

SELECT table1.username AS username, table3.verici as v,table4.srvname as sn,table2.srvid as srvid,
( sum(table1.acctsessiontime) / 3600 ) as sestime, ( sum(table1.acctinputoctets)  / 8589934592 ) as upload,   
( sum(table1.acctoutputoctets)  / 8589934592 )  as download

FROM table1,table2
left join table3 on table3.id=table2.ap
left join table4 on table4.srvid=table2.srvid

where  (table1.username=table2.username and table2.servicetype='wireless' AND  
_accttime BETWEEN '2015-12-08' AND '2015-12-09') OR (table1.username like '%ms.%' AND table1.username like '%mrs.%')
group by table1.username

zapata

no way! no one of them doesnt work!

$RAW_SQL_QUERY="SELECT username FROM users";

Editor::$db 
->sql($RAW_SQL_QUERY)
->process($_POST)->json();

Editor::inst($db)
->sql($RAW_SQL_QUERY)
->process($_POST)->json();

Editor::inst($db,'users')
->sql($RAW_SQL_QUERY)
->process($_POST)->json();

echo $db
->sql($RAW_SQL_QUERY)
->process($_POST)->json();

zapata

GOD THANKS. I solved it. We dont need EDITOR namespace if we use raw sql!

include( "__editor/php/DataTables.php" );

use
DataTables\Database,
DataTables\Database\Query,
DataTables\Database\Result;

$RAW_SQL_QUERY="select username,id from users where username like 'morsltd'";

$r=$db ->sql($RAW_SQL_QUERY)->fetchAll();
$arr=array("data"=>$r,"options"=>'',"files"=>'');//DATATABLE CLIENT SIDE PARSES
echo json_encode($arr);
exit();

allan

Hi,

This is correct. There is a DataTable->sql() method that can be used to execute raw queries using the Database libraries that Editor comes with. Raw queries can't be used for editable tables directly, but they do allow flexibility if you want to create you own queries without your own database connection object.

Allan