Mentenyia Mentenyia - 2 months ago 19
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

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();;
Comments