Laravel充分利用了数据库

Question

I'm having a tracker in Laravel 4 and I was wondering, how can I get the 'most' from my table.

So want I want is to have the day that had the most visitors.

My table structure looks like this:

So, my controller looks like this:

public function index()
    {    
        $begin = date('Y-m-01');
        $end = date('Y-m-t');

        $visits = Tracker::selectRaw('date, count(ip)')->groupBy('date')->whereRaw("date between '$begin' and '$end'")->get();

        //get total visits per month
        $get_visits = Visitor::whereRaw("date between '$begin' and '$end'")->count();

        // get average visits
        // transform dates to DateTime objects (we need the number of days between $begin and $end)
        $begin = new \DateTime($begin);
        $end = new \DateTime('now');
        $diff = $end->diff($begin); // creates a DateInterval object
        $days = (int)$diff->format('%a'); // %a  -->  days
        $average_visits = $get_visits / $days;


        return View::make('admin.home.index')->with('stats', $visits)
                                             ->with('get_visits', $get_visits)
                                             ->with('average_visits', $average_visits);

    }

What I want as an output:

We had the most visitors on 18/06/2015 (542 visitors)

For example.

Thanks!

Answer 1

You can SELECT the date ORDER BY the amount of visitors DESCENDING and LIMIT by 1. This is basic mysql. How to use this in laravel, is in the documentation.

Order by desc:

->orderBy('visits', 'desc')

Limit:

->take(1)

My example query is based on a system that has a column which counts the visits(which your structure doesn't have). So let me help you with your structure.

You want to have it by date so you basically GROUP BY date:

->groupBy('date')

From this query you want to have the amount of rows that belong to that date. So you use COUNT:

->count()

This is all based on that you know stuff about queries. This is all according to the query builder.