laravel - 如何从 Laravel 7 中的数据透视表中查询数据
问题描述
更新:我更改了阅读一些建议线程的代码。现在我的控制器中的功能是:
public function search (Document $document, Request $request) {
$from = $request -> input('from');
$to = $request -> input('to');
$results = Document::whereHas('products', function ($q) {
$request = new Request;
$product = $request -> input('product');
$q->where('name', $product);
})
->whereBetween('created_at', [$from, $to])
->get();
dd($results);
return view('cms.search', compact('results'));
}
它不会抛出任何错误,而是返回一个空数组。:(
我从未在 Laravel 中编写过复杂的搜索查询,并且在访问数据时遇到了一些困难。问题是我必须在名为“documents”的数据库表中创建一个搜索,它根据给定的日期范围和产品名称返回文档列表。
这是搜索的标记:
<form action="{{route('cms.search')}}" method="GET">
<div class="form-group col-12 col-md-4">
<label for="date">Intervallo di date:</label>
<div class="input-group date">
<div class="input-group input-daterange">
<input name="from" type="search" class="form-control" value="Scegli data" data-provide="datepicker">
<div class="input-group-addon">a</div>
<input name="to" type="text" class="form-control" value="Scegli data" data-provide="datepicker">
</div>
</div>
</div>
<div class="form-group col-12 col-md-4">
<label for="products">Prodotto:</label>
<input type="text" id="searchbox" name="product" class="form-control" placeholder="Cerca prodotto" />
</div>
<button type="submit" style="margin-top: 23px; margin-left: 16px;" class="btn btn_search btn-primary">Invia</button>
</form>
这是我的控制器中的搜索功能:
public function search (Document $document, Request $request) {
$from = $request -> input('from');
$to = $request -> input('to');
$product = $request -> input('product');
$results = Document::with('products')
->whereBetween('created_at', [$from, $to])
->where($document->$product['name'], $product)
->get();
return view('cms.search', compact('results'));
}
提交表单时出现的错误:SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (SQL: select * from documents
where created_at
between 02/01/2021 and 02/05/2021 and ` ` = Bolognando 和documents
.deleted_at
为空)
我想问题在于查找产品名称,因为虽然“created_at”是“文档”表中的一列,但与“产品”表的连接是通过多对多关系和数据透视表完成的。所以我很确定我试图在查询中访问文档名称的方式是错误的,但我不知道该怎么做。基本上,我试图从 products 表中查询列“name”,该列通过枢轴连接到“documents”表。我希望这是有道理的...
产品表:
public function up()
{
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('description')->nullable();
$table->string('um');
$table->decimal('price', 8, 2)->nullable();
$table->integer('availability')->nullable();
$table->boolean('hidden')->default(false);
$table->integer('created_by');
$table->integer('updated_by')->nullable();
$table->integer('deleted_by')->nullable();
$table->timestamps();
$table->softDeletes('deleted_at', 0);
});
}
数据透视表:
public function up()
{
Schema::create('document_product', function (Blueprint $table) {
$table->id();
$table->integer('document_id');
$table->integer('product_id');
$table->decimal('product_price', 8, 2)->nullable();
$table->integer('quantity');
});
}