如何使用拉拉维尔关系在拉拉维尔的 3 个表之间建立关系?

Question

我有三个表：

SET NAMES utf8;
SET time_zone = '+00:00';
DROP TABLE IF EXISTS `api_credentials`;
CREATE TABLE `api_credentials` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`api_provider_id` bigint(20) unsigned NOT NULL,
`access` text COLLATE utf8_unicode_ci,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `api_credentials_user_id_foreign` (`user_id`),
KEY `api_credentials_api_provider_id_index` (`api_provider_id`),
CONSTRAINT `api_credentials_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `api_credentials_api_provider_id_foreign` FOREIGN KEY (`api_provider_id`) REFERENCES `api_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `api_providers`;
CREATE TABLE `api_providers` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` char(255) COLLATE utf8_unicode_ci NOT NULL,
`table_prefix` char(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 2019-12-05 13:38:06

这是我的模型： 1. API 凭据

<?php
namespace AppModels;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsPivot;
class ApiCredential extends Pivot
{
}

2. ApiProvider

<?php
namespace AppModels;
use IlluminateDatabaseEloquentModel;
class ApiProvider extends Model
{
protected $fillable = [
'name',
'table_prefix'
];
public function users()
{
return $this->belongsToMany('AppModelsUser')->using('AppModelsApiCredential');
}
}

3. 用户模型

namespace AppModels;
use IlluminateNotificationsNotifiable;
use IlluminateContractsAuthMustVerifyEmail;
use IlluminateFoundationAuthUser as Authenticatable;
use AppModelsApiProvider;
use AppModelsApiCredential;
class User extends Authenticatable
{
use Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name',
'email',
'password',
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [
'password',
'remember_token',
];
public function apiProvider()
{  
return $this->belongsToMany('AppModelsApiProvider','api_providers','user_id','api_provider_id');
} 
public function hasCredentials(String $apiProvider)
{
return $this->apiProvider()->where('name', $apiProvider)->get();
} 
}

现在我想要的：我想根据user和api_provider从api_credentials表中获取记录。假设我api_providers有三条记录(名称是adscane，cpalead，offertorro(。

我正在运行此功能以获取api_credentialcpalead。

User::find(1)->hasCredentials('cpalead');

但它并没有给出预期的结果。

有人可以帮我解决这个问题吗？

Answer 1

我相信

# User model
public function apiProvider()
{  
return $this->belongsToMany('AppModelsApiProvider','api_providers','user_id','api_provider_id');
}

应该是

# User model
public function apiProvider()
{  
return $this->belongsToMany('AppModelsApiProvider','api_credentials','user_id','api_provider_id');
}

编辑访问api_credentials：

2. 使用hasMany关系

# User model
public function apiCredentials()
{
return $this->hasMany('AppModelsApiCredential')
}

# ApiProvider model
public function apiCredentials()
{
return $this->hasMany('AppModelsApiCredential');
}

# ApiCredential model
public function user()
{
return $this->belongsTo('AppModelsUser');
}
public function apiProvider()
{
return $this->belongsTo('AppModelsApiProvider');
}

然后，我们将hasCredentials()函数重新定义为如下工作：

# User model
public function hasCredentials(String $apiProvider)
{
$this->apiCredentials()->whereHas('apiProvider', function ($query) use ($apiProvider) {
$query->where('name', '=', $apiProvider);
})
->get();
}

使用数据将如下所示：

$user = User::find($id);
foreach($user->hasCredentials('api_provider_name') as $apiCredential)
{
// $apiCredential->name
// $apiCredential->table_prefix
}

Answer 2

这样用法

namespace AppModel;

use IlluminateDatabaseEloquentModel;
class ApiCredential extends Model
{
public function user()
{
return $this->belongsTo(User::class);
}
public function apiProvider()
{
return $this->belongsTo(ApiProvider::class);
}
}

和

namespace AppModel;

use IlluminateDatabaseEloquentModel;
class ApiProvider extends Model
{
public function ApiCredentials()
{
return $this->hasMany(ApiCredential::class);
}
}

和

namespace AppModel;
use IlluminateDatabaseEloquentRelationsBelongsTo;
use IlluminateDatabaseEloquentRelationsHasMany;
use IlluminateFoundationAuthUser as Authenticatable;
use IlluminateNotificationsNotifiable;
use LaravelPassportHasApiTokens;
use SpatieActivitylogTraitsLogsActivity;
class User extends Authenticatable
{
use Notifiable;
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name',
'email',
'password',
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [
'password',
'remember_token',
];
public function ApiCredentials()
{
return $this->hasMany(ApiCredential::class);
}
}

现在像这样查询

User::with('apiCredentials.apiProvider',)
->where('id',1)
->whereHas('apiCredentials.apiProvider', function ($query)  {
$query->where('name','cpalead' );
})->get();