如何在laravel5.6中同时使用group-by和order-by

s5a0g9ez  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(310)

我用的是拉维5.6
我用的是口才。我想用 GROUP BY 以及 ORDER BY 总之，我有一些经验告诉我，即使在普洱，也不像看上去那么容易 mysql 查询；）。我也读过类似的问题，但我还是很困惑！
我有两个模型（表）叫 Currency （数据库表： currencies )以及 CurrencyExchangeRate （数据库表： currency_exchange_rates )有了这些信息：

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');
        });

和

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');
});

这些是我的模型：

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);
    }
}

和

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);
    }
}

正如您所看到的，每种货币都可以有许多货币汇率，我想显示用户在过去10天内的货币汇率！一天内一种货币有不同的汇率，我要一天内最后输入的汇率！例如：

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种价格 currency_id = 1 在 date = 2018-04-05 ，在我的报告中，我需要最新的一个（创建于=3 | sell=15 | buy=20）
因此，如果我想有一个更真实的例子，我的表将如下所示：

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

我想要一个这样的数组：

$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 
                ]
            ]
        ];

我用这个代码得到了每种货币的汇率 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();

但是 ORDER BY 是不工作，我会收到只是第一流。
更新
我想得到最近10天每天的最新价格，例如，如果管理员输入了4个价格 USD 在 2018-04-05 ，我想买最新的(我刚进去 sell 比率以简化我的示例）

USD = [
     2018-04-01 => 10 (the latest rate has been entered for USD on 2018-04-01 is 10),
     2018-04-02 => 13 (the latest rate has been entered for USD on 2018-04-02 is 13),
     2018-04-03 => 15 (the latest rate has been entered for USD on 2018-04-03 is 15),
     2018-04-04 => 18 (the latest rate has been entered for USD on 2018-04-04 is 18),
     2018-04-05 => 12 (the latest rate has been entered for USD on 2018-04-05 is 12),
     ...
],

EUR  = [
     2018-04-01 => 10 (the latest rate has been entered for EUR on 2018-04-01 is 10),
     2018-04-02 => 13 (the latest rate has been entered for EUR on 2018-04-02 is 13),
     2018-04-03 => 15 (the latest rate has been entered for EUR on 2018-04-03 is 15),
     2018-04-04 => 18 (the latest rate has been entered for EUR on 2018-04-04 is 18),
     2018-04-05 => 12 (the latest rate has been entered for EUR on 2018-04-05 is 12),
     ...
],
mysqlgroup-bylaraveleloquentlaravel-5

2条答案

按热度按时间
gkn4icbw

gkn4icbw1#

试试这个

Currency::findOrFail($currency_id)
    ->with(
    [
        'exchangeRate' => function ($q) use ($days) {
            $q->orderBy('created_at', 'DESC')
                ->first();
        }
    ]
)->get();

我想这是唯一的办法

赞(0）分享  回复(0）举报  2021-06-18
ctzwtxfj

ctzwtxfj2#

使用子查询联接：

$days = 10 ;
$currencies = Currency::with(
    [
        'exchangeRate' => function ($q) use ($days) {
            $latest = CurrencyExchangeRate::select('currency_id', 'date')
                ->selectRaw('MAX(created_at) created_at')
                ->groupBy('currency_id', 'date');
            $q->joinSub($latest, 'latest', function($join) {
                $join->on('currency_exchange_rates.currency_id', 'latest.currency_id')
                    ->on('currency_exchange_rates.date', 'latest.date')
                    ->on('currency_exchange_rates.created_at', 'latest.created_at');
            })->where('currency_exchange_rates.created_at', ">", strtotime('-' . $days . ' days', time()));
        }
    ]
)->get();

执行以下查询：

select *
from `currency_exchange_rates`
inner join (
  select `currency_id`, `date`, MAX(created_at) created_at
  from `currency_exchange_rates`
  group by `currency_id`, `date`
) as `latest`
on `currency_exchange_rates`.`currency_id` = `latest`.`currency_id`
  and `currency_exchange_rates`.`date` = `latest`.`date`
  and `currency_exchange_rates`.`created_at` = `latest`.`created_at`
where `currency_exchange_rates`.`currency_id` in (?, ...)
  and `currency_exchange_rates`.`created_at` > ?
赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com