Need help to write a frequent query in Laravel 3

Question

I need a little help writing frequent query in Laravel 3.

I have a table called "votes" which stores user votes in "posts".

so, "votes" table has a "user_id", "post_id" and "timestamp".

Now I need to write a query to show "trending" posts (in a certain timeframe). My logic is to first select records that were added in the desired timeframe (say last 30 days for example), then group the records by post_id and then order them by count.

I can write query for each criteria separately.. but I cant figure out how to incorporate all these criteria in a single query.

Basically... I just want someone to write a frequent query for this SQL

SELECT post_id, COUNT(*)
FROM votes
GROUP BY post_id
ORDER BY COUNT(*) DESC

Any kind of help will be immensely appreciated.

Thank You. :)

Answer 1

By frequent query builder, I assume you are referring to Fluent Query Builder.

Using Fluent Query Builder you would have something like this:

Laravel 3:

$results = DB::table('votes')->get(array('post_id','count(*)'))->group_by('post_id')->order_by('count(*)','desc')->get();

Laravel 4:

$results = DB::table('votes')->select('post_id','count(*)')->groupBy('post_id')->orderBy('count(*)','desc')->get();

Hope this helps! Good Luck!