如何在Laravel中加入一对多关系 [英] How to group one to many relationships with join in Laravel
问题描述
我正在使用Laravel 5.3,并尝试使用联接从多个表中返回数据.
I'm using Laravel 5.3 and trying to return data from multiple tables using a join.
我正在使用3种模型/表格,分别是客户,业务和网站,这些模型/表格的相关性如下:
I'm using 3 models / tables, Customer, Business and Website which are related as follows:
在Customer.php中:
In Customer.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Customer extends Model
{
public function businesses()
{
return $this->hasMany('App\Business');
}
}
在Business.php中:
In Business.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Business extends Model
{
public function customer()
{
return $this->belongsTo('App\Customer');
}
public function websites()
{
return $this->hasMany('App\Website');
}
}
在Website.php中:
And in Website.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Website extends Model
{
public function business() {
return $this->belongsTo('App\Business');
}
}
因此,客户
可以具有许多 Business
,而这些企业可以具有许多网站
.现在,我尝试使用join语句返回客户及其相关业务和网站信息的列表.我正在使用以下代码返回此信息:
So a Customer
can have many Businesses
which can have many Websites
.
Now I'm trying to use a join statement to return a list of the customers and their related business and website information. I'm using the following code to return this information:
$customers = \DB::table('customers')
->join('businesses', 'customers.id', '=', 'businesses.customer_id')
->join('websites', 'businesses.id', '=', 'websites.business_id')
->select('customers.x', 'businesses.y', 'websites.z')
->get();
我希望将数据以关联客户数组的形式返回,而将业务和网站数据嵌套在关联数组中,如下所示:
And I'd like the data to be return in an array of associative customer arrays with the business and websites data nested in the associative array like so:
[
0 => {
$customer1Data,
$customer1BusinessData,
$customer1WebsiteData
}
1 => {
$customer2Data,
$customer2BusinessData,
$customer2WebsiteData
}
...
]
如果客户经营一家拥有一个网站的企业,但假设 $ customer1
经营两家,则上述方法可以很好地工作,那么上述联接将以以下格式返回内容:
This works fine if a customer has one business which has one website but suppose $customer1
has two businesses, then the above join returns something in this format:
[
0 => {
$customer1Data,
$customer1BusinessData1,
$customer1WebsiteData
}
1 => {
$customer1Data,
$customer1BusinessData2,
$customer1WebsiteData
}
...
]
有没有一种方法可以修改join语句以这种格式返回该方案:
Is there a way I can modify the join statement to return that scenario in this format:
[
0 => {
$customer1Data,
businesses => {
$customer1BusinessData1,
$customer1BusinessData2
}
...
}
]
有没有一种方法可以通过join语句实现?还是我应该以不同的方式来处理这个问题?任何帮助将不胜感激,非常感谢.
Is there a way I can achieve this with join statements? Or should I be approaching this in a different way? Any help would be greatly appreciated, many thanks.
推荐答案
Before I dive into explaining eloquent model relations, read this: https://laravel.com/docs/5.4/eloquent. This will clear things up.
在模型关系中,您可以指定将这两个表链接在一起的数据库字段名称.这甚至适用于数据透视表.对于基本文档,请阅读以下内容: https://laravel.com/docs/5.4/eloquent-relationships#defining-relationships .
In a model relation you can specify what the database field names are that link those two tables together. This even works with pivot tables. For the basic documentation read this: https://laravel.com/docs/5.4/eloquent-relationships#defining-relationships.
例如在客户模型中:
public function businesses()
{
return $this->hasMany(Business::class, 'id', 'customer_id');
}
public function websites()
{
//Argument order: final class, pivot class, pivot foreign key, foreign key final model, primary key start model (customer)
return $this->hasManyThrough(Website::class, Business::class, 'customer_id', 'id', id);
}
If this last part was a bit hard to understand, read the documentation: https://laravel.com/docs/5.4/eloquent-relationships#has-many-through
对于商业模式:
public function website()
{
return $this->hasMany(Website::class, 'id', 'business_id');
}
现在您可以使用雄辩的语言检索数据了.
And now you can retrieve data by using eloquent.
//This will retrieve all customers with all of their website information.
Customer::with('websites')->get();
//This will retrieve all customers with their business information.
Customer::with('businesses')->get();
//This will retrieve all customers with business and website information
//Retrieves: [
// customer: [
// customerDetails: []
// businesses: [ b1, b2],
// websites: [w1, w2]
// ]
//]
Customer::with('businesses', 'websites')->get();
//This will retrieve all customers with business information and the website information for each business
//Retrieves: [
// customer: [
// customerDetails: []
// businesses: [
// b1: [ websites: [w1] ],
// b2: [ websites: [w2] ]
// ],
// ]
//]
Customer::with('businesses.websites')->get();
我希望这会有所帮助!
这篇关于如何在Laravel中加入一对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!