如何从行中获取self-value，以便在self-雄辩模型中的另一个选择查询中使用

Question

我是laravel的新手，几天前刚开始，我已经搜索过了，但没有找到我的问题。例如我有报价和价格表格。

优惠：

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | Product 1 |
|  2 | Product 2 |
|  3 | Product 3 |
|  4 | Product 4 |
+----+-----------+

报价_价格：

+----+------------+-------+---------------------+
| id |  offer_id  | price |     created_at      |
+----+------------+-------+---------------------+
|  1 |          1 | 65.90 | 2020-12-17 16:00:00 |
|  2 |          1 | 64.99 | 2020-12-17 17:00:00 |
|  3 |          1 | 58.90 | 2020-12-17 18:00:00 |
|  4 |          1 | 60.99 | 2020-12-17 19:00:00 |
+----+------------+-------+---------------------+

为了获得当前的报价(60.99(，我创建了下面的函数，以从一个关系雄辩的模型中获得它：

型号/Offer.php

public function price() {
return parent::hasOne(OfferPrices::class)
->where("date", "<=", CarbonCarbon::now()->toDateTimeString())
->orderBy("date", "DESC")
->limit(1);
}

现在我想得到最新的以前的价格，但比现在的价格高，就像下面的查询一样：

SELECT *
FROM offers_prices
WHERE offers_prices.offer_id = 1
AND
offers_prices.price > (
SELECT offers_prices.price
FROM offers_prices
WHERE offers_prices.offer_id = 1
AND offers_prices.date <= NOW()
ORDER BY offers_prices.date DESC
LIMIT 1
)
AND offers_prices.date <= NOW()
ORDER BY offers_prices.date DESC
LIMIT 1;

这意味着最近的先前价格和高于当前价格不能是58.90，因为它低于当前(60.99(。必须是64.99。我该怎么做？我尝试在自我说服模型中创建另一个函数：

型号/Offer.php

public function prev_high_price() {
return parent::hasOne(OfferPrices::class)
->where("date", "<=", CarbonCarbon::now()->toDateTimeString())
->where("price", ">", $this->price()->price)
->orderBy("date", "DESC")
->limit(1);
}

但它不起作用。。

Answer 1

您将hasOne关系用于看起来更像hasMany的数据库关系。您的Offers可以有许多Prices。您的表命名也不符合惯例，因为您没有使用透视表，而是将offers_prices表命名为原样。

与问题无关，但与您的情况相关，建议不要将货币值存储为浮动或双倍。将它们作为整数存储在最小分母中

为了回答您的问题，给定以下两个迁移：

提供表迁移

class CreateOffersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('offers', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->string('name');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('offers');
}
}

价格表迁移

class CreatePricesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('prices', function (Blueprint $table) {
$table->id();
$table->timestamps();
$table->foreignId('offer_id');
$table->integer('price');
$table->foreign('offer_id')->references('id')->on('offers');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('prices');
}
}

在Offer模型中定义以下内容：

class Offer extends Model
{
use HasFactory;
public function prices()
{
return $this->hasMany(Price::class);
}
public function lastPrice()
{
return $this->prices()->orderBy('id', 'desc')->limit(1);
}
public function lastHighPrice()
{
return $this->prices()
->where('id', '<', $this->lastPrice()->first()->id)
->orderBy('price', 'desc')
->limit(1);
}
}

运行迁移和数据库种子程序(如果有，请手动添加数据(。在您的终端中，当您当前的工作目录是您的Laravel项目时，启动tinker:

php artisan tinker

从内部修补程序，您现在可以处理您的数据和模型。

// get a collection of all prices for the Offer with id 1
Offer::find(1)->prices
// get a collection with the last price for the offer with id 1
Offer::find(1)->lastPrice
// get a collection with the previous highest price for the offer with id 1
Offer::find(1)->lastHighPrice