sql - 如何在数据表中显示 belongsTomany 数据
问题描述
数据表警告:表 id=dataTableBuilder - 异常消息:
SQLSTATE [42000]:语法错误或访问冲突:1066 不是唯一的表/别名:'phyto_product'(SQL:选择计数(*)作为聚合来自(从左连接中选择
row_count
'phyto_product
1phyto_product
' 。phyto_product
=id
。phytos
左phyto_id
连接phyto_product
。phyto_product
= .其中.为空) count_row_table)id
products
product_id
phyto_product
deleted_at
我belongsTomany
在植物和产品之间有如下关系。我正在寻找一种在datatable
以下内容中显示的方法
phyto_number, product_name, weight, charge
我的查询(PhytoProduct 是一个数据透视表)
public function query(PhytoProduct $model)
{
return $model->newQuery()->leftjoin('phyto_product','phyto_product.id', '=','phytos.phyto_id')
->leftjoin('phyto_product','phyto_product.id', '=', 'products.product_id')
->select('phyto_product.*', 'phytos.phyto_number','products.product_name');
}
我的数据表
protected function getColumns()
{
return [
[ 'data' => 'phyto_number', 'name' => 'phytos.phyto_number', 'title' => 'Phyto Number' ],
[ 'data' => 'product_name', 'name' => 'products.product_name', 'title' => 'Product Name' ],
[ 'data' => 'weight', 'name' => 'phyto_product.weight', 'title' => 'Weight' ],
[ 'data' => 'charge', 'name' => 'phyto_product.charge', 'title' => 'charge' ],
];
}
植物模型
<?php
namespace App\Models;
use Eloquent as Model;
use Illuminate\Database\Eloquent\SoftDeletes;
/**
* Class Phyto
* @package App\Models
* @version December 27, 2019, 1:08 am UTC
*
* @property string phyto_number
* @property integer destination_id
* @property string indate
*/
class Phyto extends Model
{
use SoftDeletes;
public $table = 'phytos';
const CREATED_AT = 'created_at';
const UPDATED_AT = 'updated_at';
protected $dates = ['deleted_at'];
public $fillable = [
'phyto_number',
'destination_id',
'indate'
];
/**
* The attributes that should be casted to native types.
*
* @var array
*/
protected $casts = [
'id' => 'integer',
'phyto_number' => 'string',
'destination_id' => 'integer',
'indate' => 'date:d/m/y',
];
/**
* Validation rules
*
* @var array
*/
public static $rules = [
'phyto_number' => 'required',
'indate' => 'required'
];
public function products()
{
return $this->belongsToMany(Product::class)->withPivot(['weight','charge']);
}
public function destinations()
{
return $this->hasMany(Destination::class);
}
}
PhytoProduct Model
<?php
namespace App\Models;
use Eloquent as Model;
use Illuminate\Database\Eloquent\SoftDeletes;
/**
* Class PhytoProduct
* @package App\Models
* @version December 27, 2019, 2:20 am UTC
*
* @property integer phyto_id
* @property integer product_id
* @property number weight
* @property number charge
*/
class PhytoProduct extends Model
{
use SoftDeletes;
public $table = 'phyto_product';
const CREATED_AT = 'created_at';
const UPDATED_AT = 'updated_at';
protected $dates = ['deleted_at'];
public $fillable = [
'phyto_id',
'product_id',
'weight',
'charge'
];
/**
* The attributes that should be casted to native types.
*
* @var array
*/
protected $casts = [
'id' => 'integer',
'phyto_id' => 'integer',
'product_id' => 'integer',
'weight' => 'float',
'charge' => 'float'
];
/**
* Validation rules
*
* @var array
*/
public static $rules = [
'phyto_id' => 'required',
'product_id' => 'required'
];
}
Product Model
<?php
namespace App\Models;
use Eloquent as Model;
use Illuminate\Database\Eloquent\SoftDeletes;
/**
* Class Product
* @package App\Models
* @version December 27, 2019, 1:07 am UTC
*
* @property string product_name
*/
class Product extends Model
{
use SoftDeletes;
public $table = 'products';
const CREATED_AT = 'created_at';
const UPDATED_AT = 'updated_at';
protected $dates = ['deleted_at'];
public $fillable = [
'product_name'
];
/**
* The attributes that should be casted to native types.
*
* @var array
*/
protected $casts = [
'id' => 'integer',
'product_name' => 'string'
];
/**
* Validation rules
*
* @var array
*/
public static $rules = [
'product_name' => 'required'
];
public function phytos()
{
return $this->belongsToMany(Product::class)->withPivot(['weight','charge']);
}
}
解决方案
答案基于这个sql
SELECT phyto_product.weight,
phytos.phyto_number,
products.product_name
FROM phyto_product
LEFT JOIN phytos ON phytos.id = phyto_product.phyto_id
Left join products ON products.id = phyto_product.product_id
然后翻译成这个
public function query(PhytoProduct $model)
{
return $model->newQuery()->leftjoin('phytos','phytos.id', '=', 'phyto_product.phyto_id')
->leftjoin('products','products.id', '=', 'phyto_product.product_id')
->select('phyto_product.*', 'phytos.*','products.*');
}
推荐阅读
- azure - 与默认凭据连接时,azure-lab-services 权限被拒绝
- angular - 根据域在路由之间进行选择 - Angular 6
- excel - 基于最近的“完成年份”的返回值?
- java - 如何在不使用静态上下文中的类名的情况下访问 java 中的 .class 对象
- django - django mongo db not in query error while 检查名称是否存在于列表中
- java - 软键盘隐藏编辑文本
- android-studio - 无法在 MacOS 上的 Android Studio 3.2.1 中查看 logcat 工具窗口
- php - 如果列不包含数组中的值,则插入新记录
- sql - SQL合并两个查询并为微积分插入一个新列
- createjs - 如何让文本对象动态更改文本?