php - symfony2 mysql ranking based on time interval -

php - symfony2 mysql ranking based on time interval -

i'm working on symfony2 project , have write query show ranked users based on number of views of reviews on differnets periods (weekly, monthly , time)

so have construction :

user table (user) review table (review) +---+--------+ +---+--------+------+-----+ |id |username| |id |user_id |title |desc | +------------+ +------------+------+-----+ |5 | mark | |1 | 5 | tt | aaa | |6 | steve | |2 | 7 | tt | aaa | |7 | user | |3 | 6 | tt | aaa | +---+--------+ +---+--------+------+-----+ review log view table (review_view) +----------+--------+---------------------+ |review_id |user_id |viewed_at | +----------+--------+---------------------+ |1 | 1 | 2014-06-05 15:12:00 | |2 | 2 | 2014-06-05 15:12:00 | |3 | 8 | 2014-06-05 16:12:00 | |1 | 1 | 2014-06-05 15:12:00 | |2 | 2 | 2014-06-07 20:12:00 | |3 | 10 | 2014-06-07 15:12:00 | |1 | null | 2014-06-07 15:12:00 | |2 | 2 | 2014-06-09 15:12:00 | |3 | 3 | 2014-06-09 15:12:00 | |1 | 1 | 2014-06-09 15:12:00 | |2 | 20 | 2014-06-09 15:12:00 | |3 | 8 | 2014-06-03 15:12:00 | +---+--------+------+---------------------+

the review_view table log every time user(user_id) view review (review_id) user_id can null users not logged in

the problem have display them showed in next picture

description of picture: - weekly,monthly , time tabs - reddish arrows if ranking has changed lastly week

so thought symfony2 or mysql ?

thanks

you can utilize case , group by values each user lastly 2 time periods. produce select statement can't written in doctrine, might able dql.

another selection write view in mysql , utilize if table in symfony program. views used have terrible performance in mysql, may have improved.

php mysql symfony2 doctrine2