Mentenyia Mentenyia - 1 year ago 63
MySQL Question

Laravel 5.2 trouble with SQL query in eloquent

I am having trouble with eloquent in Laravel 5.2

What I want to achieve is (hopefully) a query which fulfills these steps:


  1. updated_at >= value1

  2. updated_at <= value2

  3. event = value3

  4. groupBy = value4

  5. count entries of each "group" (each grouped By group)



Output: latest updated_at for each group (max(updated_at)) and the count value for each group

What I have so far is:

Table 'logs' overview:

---------------------------
| id | increment |
-----------------------------
| key | varchar(255) |
-----------------------------
| video_id | varchar(255) |
-----------------------------
| event | varchar(255) |
-----------------------------
| created_at | timestamp |
-----------------------------
| updated_at | timestamp |
-----------------------------


Query in Laravel:

$showed = self::where('updated_at','>=',$value1)->where('updated_at','<=',$value2)->where('event',$value3)->groupBy($avlue4)->get();


What I am missing are step 5 and the resulting output. And I have no idea how I could include this to the Laravel eloquent.

Question: Does anyone have a concrete idea of how to achieve this query? If it's not possible, what are the steps to take?

Answer Source

If someone also has troubles with building this kind of eloquent: My solution was to group the query by ID, so the entries in Array would be something like:

[0] => [Object, Object],
[1] => [Object]

My query:

$temp = self::where('updated_at','>=',$month_start)
        ->where('updated_at','<=',$month_end)
        ->where('event','completed')
        ->orWhere('event', 'showed')->get();
    $result = $temp->groupBy('id')->toArray();;