SQL to Laravel Query Builder

I'm having a hard time converting raw SQL to an SQL string built with the Laravel Query Builder.

Here you can read about my problem, and see the answer I got. The answer I received needs to be converted from raw SQL to a SQL string built with the Laravel Query Builder using a model (Time) instead of \DB::

---

I had it working with raw SQL. Where 4298584 is the $id variable. Unfortunately, it returns an array like this:

// php
$times = \DB::select( \DB::raw(
    "SELECT t.* FROM times t
        JOIN (SELECT ath_id, stroke_id, MIN(time)
              AS time
              FROM swimrankings.times
              GROUP BY ath_id, stroke_id
        ) b
        ON t.ath_id = b.ath_id
        AND t.stroke_id = b.stroke_id
        AND t.time = b.time
        WHERE t.ath_id = 4298584
        ORDER BY t.stroke_id ASC, t.date DESC"
) );

// returns:
array:40 [▼
  0 => {#217 ▼
    +"id": 72073658
    +"ath_id": 4298584
    +"brand_id": 1
    +"time": 1104
    +"date": "28 Jun 2015"
  }
]

Instead of an array that contains all the models like this:

// php:
$times = Time::where('ath_id', '=', $id)
             ->groupby('brand_id')
             ->get();

// returns:
Collection {#219 ▼
  #items: array:39 [▼
    0 => Time {#220 ▼
      #fillable: array:7 [▶]
      #attributes: array:9 [▼
        "id" => 72073658
        "ath_id" => 4298584
        "brand_id" => 1
        "time" => 1104
        "date" => "28 Jun 2015"
      ]
      #original: array:9 [▼
        "id" => 72073658
        "ath_id" => 4298584
        "brand_id" => 1
        "time" => 1104
        "date" => "28 Jun 2015"
      ]
    }
  ]
}

---

My model name for the times table is named Time. And I wish to put the results from the databse in a variable named $times.

---

I need to convert the following SQL, using the Time::class:

SELECT t.* FROM times t
JOIN (SELECT ath_id, stroke_id, MIN(time) AS time FROM swimrankings.times 
      GROUP BY ath_id, stroke_id) b
  ON t.ath_id = b.ath_id AND t.stroke_id = b.stroke_id AND t.time = b.time
WHERE t.ath_id = 4298584
ORDER BY t.stroke_id ASC, t.date DESC"

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2sdG1F3
via IFTTT