How to use GROUP BY and ORDER BY together in LARVEL 5.6

I'm using LARAVEL 5.6

I'm using Eloquent. I want to use GROUP BY and ORDER BY together, I've some experiences that tell me it's not as easy as it looks like, even in puer mysql query ;). I've read similar questions too, But still I'm confused!

I have to Models (tables) called Currency (database table: currencies) and CurrencyExchangeRate (database table: currency_exchange_rates) with this informations:

Schema::create('currencies', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title', 200)->nullable();
            $table->string('symbol', 20)->nullable();
            $table->text('icon')->nullable();
            $table->tinyInteger('visible')->defualt(0);
            $table->tinyInteger('order')->nullable();
            $table->bigInteger('created_at');
            $table->bigInteger('updated_at');
        });

And

Schema::create('currency_exchange_rates', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('currency_id')->unsigned();
    $table->foreign('currency_id')->references('id')->on('currencies')->onDelete('cascade');
    $table->bigInteger("sell")->nullable();
    $table->bigInteger("buy")->nullable();
    $table->string('date', 20);
    $table->bigInteger('created_at');
    $table->bigInteger('updated_at');
});

And these are my Models:

class Currency extends Model
{
    /**
     * The storage format of the model's date columns.
     *
     * @var string
     */
    public $dateFormat = 'U';
    protected $fillable = ['title', 'symbol', 'icon', 'visible', 'order'];

    public function exchangeRate()
    {
        return $this->hasMany('App\CurrencyExchangeRate');
    }

    public function getIconAttribute($value)
    {
        return json_decode($value);
    }
}

And

class Currency extends Model
{
    /**
     * The storage format of the model's date columns.
     *
     * @var string
     */
    public $dateFormat = 'U';
    protected $fillable = ['title', 'symbol', 'icon', 'visible', 'order'];

    public function exchangeRate()
    {
        return $this->hasMany('App\CurrencyExchangeRate');
    }

    public function getIconAttribute($value)
    {
        return json_decode($value);
    }
}

As you see every Currency can have many currency exchange rates, I want to show user's currency exchange rates for a Currency in the last 10 days! there are different rates for a currency in one day, I want the last rate entered in a day! for example:

id | currency_id | sell | buy |     date   |  created_at | updated_at
---+-------------+------+-----+------------+-------------+------------
 1 |   1         |  10  |  12 | 2018-04-05 |    1        |    1
 2 |   1         |  11  |  13 | 2018-04-05 |    2        |    2
 3 |   1         |  15  |  20 | 2018-04-05 |    3        |    3

As you see there are 4 rates for currency_id = 1 in date = 2018-04-05, which in my report I need the latest one (created_at = 3 | sell = 15 | buy = 20)

So if I want to have a more real example my table will be like:

id | currency_id | sell | buy |     date   |  created_at | updated_at
---+-------------+------+-----+------------+-------------+------------
 1 |   1         |  10  |  12 | 2018-04-05 |    1        |    1
 2 |   1         |  11  |  13 | 2018-04-05 |    2        |    2
 3 |   1         |  15  |  20 | 2018-04-05 |    3        |    3
 4 |   1         |  20  |  22 | 2018-04-06 |    4        |    4
 5 |   1         |  21  |  23 | 2018-04-06 |    5        |    5

 6 |   2         |  40  |  50 | 2018-04-05 |    1        |    1
 7 |   2         |  60  |  70 | 2018-04-05 |    2        |    2
 8 |   2         |  80  |  90 | 2018-04-06 |    4        |    4
 9 |   2         |  95  |  85 | 2018-04-06 |    5        |    5

I want to have an array like this:

$currencies = [
            'currency_id ' => 1,
            'title' => 'Dollar',
            'currency_exchange_rates' => [
                '2018-04-05' => [
                    'sell' => 15,
                    'buy' => 20,
                    'created_at' => 3, // the latest rate entered in 2018-04-06 for 'currency_id ' => 1 
                ] ,
                '2018-04-06' => [
                    'sell' => 21,
                    'buy' => 23,
                    'created_at' => 5, // the latest rate entered in 2018-04-06  for 'currency_id ' => 1 
                ]
            ] ,
            'currency_id ' => 2,
            'title' => 'Euro',
            'currency_exchange_rates' => [
                '2018-04-05' => [
                    'sell' => 60,
                    'buy' => 70,
                    'created_at' => 2, // the latest rate entered in 2018-04-05 for 'currency_id ' => 2
                ] ,
                '2018-04-06' => [
                    'sell' => 95 ,
                    'buy' => 85,
                    'created_at' => 5, // the latest rate entered in 2018-04-06 for 'currency_id ' => 2 
                ]
            ]
        ];

I've used this code to get currency exchange rates for every currency by it's id:

$days = 10 ;
$currencies = Currency::findOrFail($currency_id)
    ->with(
    [
        'exchangeRate' => function ($q) use ($days) {
            $q->orderBy('created_at', 'DESC')
                ->where('created_at', ">", strtotime('-' . $days . ' days', time()))
                ->groupBy('date')
                ->get();
        }
    ]
)->get();

But ORDER BY is not working and I will receive just first rates.

via Chebli Mohamed