how to do a specific SQL query

javismiles

in one of the columns to be returned by datatables, i need to do an SQL that is a bit more complex than a select:
$stmt = $mysqli->prepare("SELECT AVG(points), COUNT(points) FROM easyvotes where idvoted=".$row["resultid"]." and type=2");

how do i do this in combination with the typical datatables php read procedure? thanks a lot

Bindrid2

Easiest way is to just create a view (assuming you are not doing updates)

javismiles

yes I just managed to do it with a view, after some tries, it works, just 2 concerns:

a) will performance suffer for big tables, the view is like a temporary pre-query that brings out all the distinct averages and then datatables selects the right one from those based on an extra where condition, but will performance suffer because of that

b) Im also wondering about memcached and datatables, I use it all the time outside of datatables, anybody combining datatables and memcached for quick cache of db queries?

javismiles

for reference my view is like this:
SELECT AVG(a.points) as p, COUNT(a.points) as c, a.idvoted FROM easyvotes a where a.type=2 group by a.idvoted

and then on the datatables php I use:
$editor->leftJoin( 'avg2', 'mywork.workid', '=', 'idvoted' );

and I select avg2.p

and it all works, just wondering about performance of views and also possible use of memcached which helps me a lot, a lot, a lot with speed on my db work outside of datatables

allan

Closing in favour of your other thread on this topic.