我有3张桌子:
广告用户
广告组
Ad_usersxad_groups
Ad_usersxad_groups是其他两个的联结表。现在,我正在做一个流明/ Laravel项目,我们正在使用雄辩的模型。我的ad_users表和ad_groups表具有以下模型:
ad_user.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Ad_user extends Model
{
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'common_name',
'location',
'description',
'postalcode',
'physical_delivery_office_name',
'telephone_number',
'initials',
'street_address'
];
/**
* Hides pivot in return queries.
*
* @var array
*/
protected $hidden = [
'pivot'
];
/**
* Many-To-Many relationship.
*/
public function ad_groups()
{
return $this->belongsToMany('App\Ad_group', 'Ad_usersxad_groups');
}
}
ad_group.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Ad_group extends Model
{
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name'
];
/**
* Hides pivot from return queries.
*
* @var array
*/
protected $hidden = [
'pivot'
];
/**
* Many-To-Many relationshipl.
*/
public function ad_users()
{
return $this->belongsToMany('App\Ad_user', 'Ad_usersxad_groups');
}
public function extensiontables()
{
return $this->belongsToMany('App\extensiontables_registry', 'extensiontables_registryxad_groups');
}
}
ad_groups和ad_user内部的数据基本上来自我们公司的LDAP服务器。因此,当用户登录时,中间件会建立与ldap的连接并检索用户数据。然后,数据将同步到我们的本地数据库,这意味着它已更新或已创建。仅出于完整性考虑,以下是用户访问登录路径时正在执行的代码:
AuthController.php
public function login(Request $request)
{
$ldap = new LDAP;
$jwt = new JWTAuth;
$response = new Response;
$this->validate($request, [
'username' => 'string|required',
'password' => 'string|required'
]);
if(!$ldap->authenticateUser($request->username, $request->password))
{
return response()->json([
'error' => 'User could not be authenticated'
], 401);
}
/**
* Synchronizes the user's data from ldap with the local db.
* Used for better performance and mitigating network overhead.
*/
$ldap->syncData($request->username);
$response->header('Authorization', 'Bearer '.$jwt->generateJWT($request->username));
return $response;
}
Here, the important part is the $ldap->syncData($request->username);
.
It calls to LDAP.php, where this code is executed:
LDAP.php
public function syncData($username)
{
try
{
if(!$this->connect())
{
$this->disconnect();
return false;
}
$userData = $this->getUser($username, [
'cn',
'l',
'description',
'postalcode',
'physicaldeliveryofficename',
'telephonenumber',
'initials',
'memberof',
'streetaddress'
]);
$user = Ad_user::updateOrCreate(
['common_name' => $userData['cn']],
[
'common_name' => $userData['cn'],
'location' => $userData['l'],
'description' => $userData['description'],
'postalcode' => $userData['postalcode'],
'physical_delivery_office_name' => $userData['physicaldeliveryofficename'],
'telephone_number' => $userData['telephonenumber'],
'initials' => $userData['initials'],
'street_address' => $userData['streetaddress']
]
);
// Remove everything but the user roles
foreach($userData['memberof'] as $key=>$role)
{
preg_match("/^CN=.+?,/", $role, $role);
$userData['memberof'][$key] = substr($role[0], 3, -1);
}
// Loop through every role because updateOrCreate cant handle arrays
foreach($userData['memberof'] as $value)
{
$roles[] = Ad_group::updateOrCreate(
['name' => $value],
['name' => $value]
)->id;
}
// Syncs current roles received from ldap with the local db
$user->ad_groups()->sync($roles);
}
catch(\Exception $e)
{
$error = array("exception_code" => $e->getCode(), "error_message" => $e->getMessage());
Log::error($error);
return false;
}
finally
{
$this->disconnect();
}
}
I discovered that whenever a user logs into the system, records are inserted into the junction table. EVERY time, meaning theres a whole lot of redundant data being created. I guess the important part of the code causing this problem is this:
$user->ad_groups()->sync($roles)
After 7 logins by the same user for example, the junction table looks like this:
select * from ad_usersxad_groups;
+------------+-------------+------------+------------+
| Ad_user_id | Ad_group_id | created_at | updated_at |
+------------+-------------+------------+------------+
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
+------------+-------------+------------+------------+
This is neither desired nor acceptable, but since I'm pretty new to lumen/laravel and also the php LDAP API in use here, I have really little to no idea what might cause this behavior Oo At least I'm pretty sure that MySQL/MariaDB does not effect this behavior, since the FOREIGN KEYs have no CASCADEs attached to them.
我想知道我在模型的定义中是否做错了什么,但是我不知道xD真的会感谢您的一些帮助^^
编辑:日志已显示此角色数组:
[2020-01-31 08:57:27] local.INFO: array (
0 => 1,
1 => 2,
2 => 3,
3 => 4,
4 => 5,
5 => 6,
6 => 7,
7 => 8,
8 => 9,
9 => 10,
10 => 11,
11 => 12,
)
仅供参考,以下是数据库的外键,当然包括与此问题相关的外键。我很确定自己配置正确,但是也许我只是盲目,所以它们是:
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
| ad_usersxad_groups | Ad_user_id | fk_ad_groupxad_user | ad_users | id |
| ad_usersxad_groups | Ad_group_id | fk_ad_userxad_group | ad_groups | id |
| extensiontables_registryxad_groups | ad_group_id | fk_ad_groupxextensiontables_registry | ad_groups | id |
| extensiontables_registryxad_groups | extensiontables_registry_id | fk_extensiontables_registryxad_group | extensiontables_registry | id |
| extensiontable_itc | coretable_id | fk_extensiontable_itc_coretable | coretable | id |
| inaccessibletable | coretable_id | fk_inaccessibletable_coretable | coretable | id |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
从我看来,转储的数组和代码看起来都不错。
我不是Laravel专家,但我建议在定义您的关系时尝试更加具体,以了解这是否可以解决您的问题。
在Ad_user
模型中,更改:
public function ad_groups()
{
return $this->belongsToMany('App\Ad_group', 'Ad_usersxad_groups');
}
至:
public function ad_groups()
{
return $this->belongsToMany('App\Ad_group', 'Ad_usersxad_groups', 'Ad_user_id', 'Ad_group_id');
}
在Ad_group
模型中,更改:
public function ad_users()
{
return $this->belongsToMany('App\Ad_user', 'Ad_usersxad_groups');
}
至:
public function ad_users()
{
return $this->belongsToMany('App\Ad_user', 'Ad_usersxad_groups', 'Ad_group_id', 'Ad_user_id');
}
如果这不能解决您的问题,请转储
$roles
数组并将其发布在此处。未能解决问题,但仍然感谢您的建议!:D我会立即转储$ roles数组,只给我几秒钟
值得一试,是的。我将删除或更新我的答案。
将日志转储到这里,我必须承认我对xD非常困惑
现在更新了答案,不确定是否会有所帮助,但是再次值得一试。