getting extra data through views

javismiles

I'm trying to get the result of a AVG, count mysql operation being returned; im trying to get it through views,
so I create a view with:

SELECT AVG(a.points) as p, COUNT(a.points) as c, a.idvoted FROM thevotes a, thework b where a.type=2

created as avg1 view

and then in the datatables php I try to get the field p from table avg1: avg1.p

and do a $editor->leftJoin( 'avg1', 'avg1.idvoted', '=', 'thework.workid' );

and no errors, but it comes out as empty, and i dont understand why

any tips?

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

I've posted back in your other thread about memcached, but with regard to views, I think the only way to be certain would be to stress test your application by loading in a million records and checking the performance characteristics.

Allan