It looks like you're new here. If you want to get involved, click one of these buttons!
I"m try to develop new plugin that display top five thanks received uses on last 7 days..here working code that give me expect result
SELECT COUNT(vf_ThanksLog.DateInserted) AS Thanks,vf_ThanksLog.UserID,vf_User.Name FROM vf_ThanksLog LEFT JOIN vf_User ON vf_ThanksLog.UserID=vf_User.UserID WHERE vf_ThanksLog.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() GROUP BY `UserID` ORDER BY thanks DESC LIMIT 05;
Now i want convert it work on VanillaModel..here my code
SQL ->Select('u.UserID, u.Name,u.Email, u.Photo') ->Select( 'tc.DateInserted','count','ThankCount') ->From('ThanksLog tc') ->Join('User u', 'tc.UserID = u.UserID') ->WHERE('tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW()') ->GroupBy('u.UserID') ->Limit("$limit") ->OrderBy('ThankCount', "Desc") ->Get() ->ResultArray();
It's given me some result,but thier where code not working as expect
select u.UserID as `UserID`, u.Name as `Name`, u.Email as `Email`, u.Photo as `Photo`, count(tc.DateInserted) as `ThankCount` from vf_ThanksLog tc join vf_User u on tc.UserID = u.UserID where tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() is null group by u.UserID order by ThankCount desc limit 5;
so problem is how to remove is null on where code
where tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() is null
please help me..any solution ?