manyMany 多对多关联

Testing Is Documentation

tests/Database/Ddd/Relation/ManyManyTest.phpmanyMany 多对多关联 - 图1

多对多的关联是一种常用的关联，比如用户与角色属于多对多的关系。

多对多关联支持类型关联项

关联项说明例子
\Leevel\Database\Ddd\Entity::MANY_MANY多对多关联实体\Tests\Database\Ddd\Entity\Relation\Role::class
\Leevel\Database\Ddd\Entity::MIDDLE_ENTITY关联查询中间实体\Tests\Database\Ddd\Entity\Relation\UserRole::class
\Leevel\Database\Ddd\Entity::SOURCE_KEY关联查询源键字段user_id
\Leevel\Database\Ddd\Entity::TARGET_KEY关联目标键字段id
\Leevel\Database\Ddd\Entity::MIDDLE_SOURCE_KEY关联查询中间实体源键字段id
\Leevel\Database\Ddd\Entity::MIDDLE_TARGET_KEY关联查询中间实体目标键字段id
\Leevel\Database\Ddd\Entity::RELATION_SCOPE关联查询作用域middleField

Uses

  1. <?php
  3. use Leevel\Collection\Collection;
  4. use Leevel\Database\Ddd\Relation\ManyMany;
  5. use Leevel\Database\Ddd\Relation\Relation;
  6. use Leevel\Database\Ddd\Select;
  7. use Tests\Database\DatabaseTestCase as TestCase;
  8. use Tests\Database\Ddd\Entity\Relation\Role;
  9. use Tests\Database\Ddd\Entity\Relation\RoleSoftDeleted;
  10. use Tests\Database\Ddd\Entity\Relation\User;
  11. use Tests\Database\Ddd\Entity\Relation\UserRole;
  12. use Tests\Database\Ddd\Entity\Relation\UserRoleSoftDeleted;

基本使用方法

fixture 定义

Tests\Database\Ddd\Entity\Relation\User

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  5. use Leevel\Database\Ddd\Relation\ManyMany;
  7. class User extends Entity
  8. {
  9.     use GetterSetter;
  11.     const TABLE = 'user';
  13.     const ID = 'id';
  15.     const AUTO = 'id';
  17.     const STRUCT = [
  18.         'id'        => [],
  19.         'name'      => [],
  20.         'create_at' => [],
  21.         'role'      => [
  22.             self::MANY_MANY         => Role::class,
  23.             self::MIDDLE_ENTITY     => UserRole::class,
  24.             self::SOURCE_KEY        => 'id',
  25.             self::TARGET_KEY        => 'id',
  26.             self::MIDDLE_SOURCE_KEY => 'user_id',
  27.             self::MIDDLE_TARGET_KEY => 'role_id',
  28.         ],
  29.         'role_soft_deleted'      => [
  30.             self::MANY_MANY         => RoleSoftDeleted::class,
  31.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  32.             self::SOURCE_KEY        => 'id',
  33.             self::TARGET_KEY        => 'id',
  34.             self::MIDDLE_SOURCE_KEY => 'user_id',
  35.             self::MIDDLE_TARGET_KEY => 'role_id',
  36.         ],
  37.         'role_middle_with_soft_deleted'      => [
  38.             self::MANY_MANY         => RoleSoftDeleted::class,
  39.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  40.             self::SOURCE_KEY        => 'id',
  41.             self::TARGET_KEY        => 'id',
  42.             self::MIDDLE_SOURCE_KEY => 'user_id',
  43.             self::MIDDLE_TARGET_KEY => 'role_id',
  44.             self::RELATION_SCOPE    => 'withSoftDeleted',
  45.         ],
  46.         'role_middle_only_soft_deleted'      => [
  47.             self::MANY_MANY         => RoleSoftDeleted::class,
  48.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  49.             self::SOURCE_KEY        => 'id',
  50.             self::TARGET_KEY        => 'id',
  51.             self::MIDDLE_SOURCE_KEY => 'user_id',
  52.             self::MIDDLE_TARGET_KEY => 'role_id',
  53.             self::RELATION_SCOPE    => 'onlySoftDeleted',
  54.         ],
  55.         'role_relation_scope_not_found'      => [
  56.             self::MANY_MANY         => RoleSoftDeleted::class,
  57.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  58.             self::SOURCE_KEY        => 'id',
  59.             self::TARGET_KEY        => 'id',
  60.             self::MIDDLE_SOURCE_KEY => 'user_id',
  61.             self::MIDDLE_TARGET_KEY => 'role_id',
  62.             self::RELATION_SCOPE    => 'notFound',
  63.         ],
  64.         'role_relation_scope_found_but_private'      => [
  65.             self::MANY_MANY         => RoleSoftDeleted::class,
  66.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  67.             self::SOURCE_KEY        => 'id',
  68.             self::TARGET_KEY        => 'id',
  69.             self::MIDDLE_SOURCE_KEY => 'user_id',
  70.             self::MIDDLE_TARGET_KEY => 'role_id',
  71.             self::RELATION_SCOPE    => 'foundButPrivate',
  72.         ],
  73.         'role_not_defined_middle_entity'      => [
  74.             self::MANY_MANY         => Role::class,
  75.             self::SOURCE_KEY        => 'id',
  76.             self::TARGET_KEY        => 'id',
  77.             self::MIDDLE_SOURCE_KEY => 'user_id',
  78.             self::MIDDLE_TARGET_KEY => 'role_id',
  79.         ],
  80.         'role_not_defined_source_key'      => [
  81.             self::MANY_MANY         => Role::class,
  82.             self::MIDDLE_ENTITY     => UserRole::class,
  83.             self::TARGET_KEY        => 'id',
  84.             self::MIDDLE_SOURCE_KEY => 'user_id',
  85.             self::MIDDLE_TARGET_KEY => 'role_id',
  86.         ],
  87.         'role_not_defined_target_key'      => [
  88.             self::MANY_MANY         => Role::class,
  89.             self::MIDDLE_ENTITY     => UserRole::class,
  90.             self::SOURCE_KEY        => 'id',
  91.             self::MIDDLE_SOURCE_KEY => 'user_id',
  92.             self::MIDDLE_TARGET_KEY => 'role_id',
  93.         ],
  94.         'role_not_defined_middle_source_key'      => [
  95.             self::MANY_MANY         => Role::class,
  96.             self::MIDDLE_ENTITY     => UserRole::class,
  97.             self::SOURCE_KEY        => 'id',
  98.             self::TARGET_KEY        => 'id',
  99.             self::MIDDLE_TARGET_KEY => 'role_id',
  100.         ],
  101.         'role_not_defined_middle_target_key'      => [
  102.             self::MANY_MANY         => Role::class,
  103.             self::MIDDLE_ENTITY     => UserRole::class,
  104.             self::SOURCE_KEY        => 'id',
  105.             self::TARGET_KEY        => 'id',
  106.             self::MIDDLE_SOURCE_KEY => 'user_id',
  107.         ],
  108.         'role_middle_field'      => [
  109.             self::MANY_MANY         => Role::class,
  110.             self::MIDDLE_ENTITY     => UserRole::class,
  111.             self::SOURCE_KEY        => 'id',
  112.             self::TARGET_KEY        => 'id',
  113.             self::MIDDLE_SOURCE_KEY => 'user_id',
  114.             self::MIDDLE_TARGET_KEY => 'role_id',
  115.             self::RELATION_SCOPE    => 'middleField',
  116.         ],
  117.         'role_middle_only_soft_deleted_and_middle_field_and_other_table_condition' => [
  118.             self::MANY_MANY         => RoleSoftDeleted::class,
  119.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  120.             self::SOURCE_KEY        => 'id',
  121.             self::TARGET_KEY        => 'id',
  122.             self::MIDDLE_SOURCE_KEY => 'user_id',
  123.             self::MIDDLE_TARGET_KEY => 'role_id',
  124.             self::RELATION_SCOPE    => 'middleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition',
  125.         ],
  126.     ];
  128.     protected function relationScopeWithSoftDeleted(ManyMany $relation): void
  129.     {
  130.         $relation->middleWithSoftDeleted();
  131.     }
  133.     protected function relationScopeOnlySoftDeleted(ManyMany $relation): void
  134.     {
  135.         $relation->middleOnlySoftDeleted();
  136.     }
  138.     protected function relationScopeMiddleField(ManyMany $relation): void
  139.     {
  140.         $relation->middleField(['create_at', 'middle_id' => 'id']);
  141.     }
  143.     protected function relationScopeMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition(ManyMany $relation): void
  144.     {
  145.         $relation
  146.             ->middleOnlySoftDeleted()
  147.             ->middleField(['create_at', 'middle_id' => 'id'])
  148.             ->setColumns('id,name')
  149.             ->where('id', '>', 3);
  150.     }
  152.     private function relationScopeFoundButPrivate(ManyMany $relation): void
  153.     {
  154.     }
  155. }

Tests\Database\Ddd\Entity\Relation\UserRole

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  6. class UserRole extends Entity
  7. {
  8.     use GetterSetter;
  10.     const TABLE = 'user_role';
  12.     const ID = 'id';
  14.     const AUTO = 'id';
  16.     const STRUCT = [
  17.         'id'        => [],
  18.         'user_id'   => [],
  19.         'role_id'   => [],
  20.         'create_at' => [],
  21.     ];
  22. }

Tests\Database\Ddd\Entity\Relation\Role

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  6. class Role extends Entity
  7. {
  8.     use GetterSetter;
  10.     const TABLE = 'role';
  12.     const ID = 'id';
  14.     const AUTO = 'id';
  16.     const STRUCT = [
  17.         'id'        => [],
  18.         'name'      => [],
  19.         'create_at' => [],
  20.     ];
  21. }
  1. public function testBaseUse(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role')
  60.             ->insert([
  61.                 'user_id' => 1,
  62.                 'role_id' => 3,
  63.             ])
  64.     );
  66.     $user = User::select()->where('id', 1)->findOne();
  68.     $this->assertSame(1, $user->id);
  69.     $this->assertSame(1, $user['id']);
  70.     $this->assertSame(1, $user->getId());
  71.     $this->assertSame('niu', $user->name);
  72.     $this->assertSame('niu', $user['name']);
  73.     $this->assertSame('niu', $user->getName());
  75.     $role = $user->role;
  77.     $this->assertInstanceof(Collection::class, $role);
  79.     $role1 = $role[0];
  81.     $this->assertInstanceof(Role::class, $role1);
  82.     $this->assertSame(1, $role1->id);
  83.     $this->assertSame(1, $role1['id']);
  84.     $this->assertSame(1, $role1->getId());
  85.     $this->assertSame('管理员', $role1->name);
  86.     $this->assertSame('管理员', $role1['name']);
  87.     $this->assertSame('管理员', $role1->getName());
  89.     $role2 = $role[1];
  91.     $this->assertSame(3, $role2->id);
  92.     $this->assertSame(3, $role2['id']);
  93.     $this->assertSame(3, $role2->getId());
  94.     $this->assertSame('会员', $role2->name);
  95.     $this->assertSame('会员', $role2['name']);
  96.     $this->assertSame('会员', $role2->getName());
  98.     $this->assertCount(2, $role);
  99.     $this->assertSame(1, $role[0]['id']);
  100.     $this->assertSame('管理员', $role[0]['name']);
  101.     $this->assertSame(3, $role[1]['id']);
  102.     $this->assertSame('会员', $role[1]['name']);
  104.     $middle = $role[0]->middle();
  105.     $this->assertInstanceof(UserRole::class, $middle);
  106.     $this->assertSame(1, $middle->userId);
  107.     $this->assertSame(1, $middle->roleId);
  109.     $middle = $role[1]->middle();
  110.     $this->assertInstanceof(UserRole::class, $middle);
  111.     $this->assertSame(1, $middle->userId);
  112.     $this->assertSame(3, $middle->roleId);
  113. }

eager 预加载关联

  1. public function testEager(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role')
  60.             ->insert([
  61.                 'user_id' => 1,
  62.                 'role_id' => 3,
  63.             ])
  64.     );
  66.     $user = User::eager(['role'])
  67.         ->where('id', 1)
  68.         ->findOne();
  70.     $this->assertSame(1, $user->id);
  71.     $this->assertSame(1, $user['id']);
  72.     $this->assertSame(1, $user->getId());
  73.     $this->assertSame('niu', $user->name);
  74.     $this->assertSame('niu', $user['name']);
  75.     $this->assertSame('niu', $user->getName());
  77.     $role = $user->role;
  79.     $this->assertInstanceof(Collection::class, $role);
  81.     $role1 = $role[0];
  83.     $this->assertInstanceof(Role::class, $role1);
  84.     $this->assertSame(1, $role1->id);
  85.     $this->assertSame(1, $role1['id']);
  86.     $this->assertSame(1, $role1->getId());
  87.     $this->assertSame('管理员', $role1->name);
  88.     $this->assertSame('管理员', $role1['name']);
  89.     $this->assertSame('管理员', $role1->getName());
  91.     $role2 = $role[1];
  93.     $this->assertInstanceof(Role::class, $role2);
  94.     $this->assertSame(3, $role2->id);
  95.     $this->assertSame(3, $role2['id']);
  96.     $this->assertSame(3, $role2->getId());
  97.     $this->assertSame('会员', $role2->name);
  98.     $this->assertSame('会员', $role2['name']);
  99.     $this->assertSame('会员', $role2->getName());
  101.     $this->assertCount(2, $role);
  102.     $this->assertSame(1, $role[0]['id']);
  103.     $this->assertSame('管理员', $role[0]['name']);
  104.     $this->assertSame(3, $role[1]['id']);
  105.     $this->assertSame('会员', $role[1]['name']);
  107.     $middle = $role[0]->middle();
  108.     $this->assertSame(1, $middle->userId);
  109.     $this->assertSame(1, $middle->roleId);
  111.     $middle = $role[1]->middle();
  112.     $this->assertSame(1, $middle->userId);
  113.     $this->assertSame(3, $middle->roleId);
  114. }

eager 预加载关联支持查询条件过滤

  1. public function testEagerWithCondition(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role')
  60.             ->insert([
  61.                 'user_id' => 1,
  62.                 'role_id' => 3,
  63.             ])
  64.     );
  66.     $user = User::eager(['role' => function (Relation $select) {
  67.         $select->where('id', '>', 99999);
  68.     }])
  69.         ->where('id', 1)
  70.         ->findOne();
  72.     $this->assertSame(1, $user->id);
  73.     $this->assertSame(1, $user['id']);
  74.     $this->assertSame(1, $user->getId());
  75.     $this->assertSame('niu', $user->name);
  76.     $this->assertSame('niu', $user['name']);
  77.     $this->assertSame('niu', $user->getName());
  79.     $role = $user->role;
  81.     $this->assertInstanceof(Collection::class, $role);
  82.     $this->assertCount(0, $role);
  83. }

relation 读取关联

  1. public function testRelationAsMethod(): void
  2. {
  3.     $connect = $this->createDatabaseConnect();
  5.     $this->assertSame(
  6.         1,
  7.         $connect
  8.             ->table('user')
  9.             ->insert([
  10.                 'name' => 'niu',
  11.             ])
  12.     );
  14.     $this->assertSame(
  15.         1,
  16.         $connect
  17.             ->table('role')
  18.             ->insert([
  19.                 'name' => '管理员',
  20.             ])
  21.     );
  23.     $this->assertSame(
  24.         2,
  25.         $connect
  26.             ->table('role')
  27.             ->insert([
  28.                 'name' => '版主',
  29.             ])
  30.     );
  32.     $this->assertSame(
  33.         3,
  34.         $connect
  35.             ->table('role')
  36.             ->insert([
  37.                 'name' => '会员',
  38.             ])
  39.     );
  41.     $this->assertSame(
  42.         1,
  43.         $connect
  44.             ->table('user_role')
  45.             ->insert([
  46.                 'user_id' => 1,
  47.                 'role_id' => 1,
  48.             ])
  49.     );
  51.     $this->assertSame(
  52.         2,
  53.         $connect
  54.             ->table('user_role')
  55.             ->insert([
  56.                 'user_id' => 1,
  57.                 'role_id' => 3,
  58.             ])
  59.     );
  61.     $roleRelation = User::make()->relation('role');
  63.     $this->assertInstanceof(ManyMany::class, $roleRelation);
  64.     $this->assertSame('id', $roleRelation->getSourceKey());
  65.     $this->assertSame('id', $roleRelation->getTargetKey());
  66.     $this->assertSame('user_id', $roleRelation->getMiddleSourceKey());
  67.     $this->assertSame('role_id', $roleRelation->getMiddleTargetKey());
  68.     $this->assertInstanceof(User::class, $roleRelation->getSourceEntity());
  69.     $this->assertInstanceof(Role::class, $roleRelation->getTargetEntity());
  70.     $this->assertInstanceof(UserRole::class, $roleRelation->getMiddleEntity());
  71.     $this->assertInstanceof(Select::class, $roleRelation->getSelect());
  72. }

relation 关联模型数据不存在返回空集合

  1. public function testRelationDataWasNotFound(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $user = User::select()->where('id', 1)->findOne();
  48.     $this->assertSame(1, $user->id);
  49.     $this->assertSame(1, $user['id']);
  50.     $this->assertSame(1, $user->getId());
  51.     $this->assertSame('niu', $user->name);
  52.     $this->assertSame('niu', $user['name']);
  53.     $this->assertSame('niu', $user->getName());
  55.     $role = $user->role;
  57.     $this->assertInstanceof(Collection::class, $role);
  58.     $this->assertCount(0, $role);
  59. }

关联软删除

fixture 定义

Tests\Database\Ddd\Entity\Relation\User

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  5. use Leevel\Database\Ddd\Relation\ManyMany;
  7. class User extends Entity
  8. {
  9.     use GetterSetter;
  11.     const TABLE = 'user';
  13.     const ID = 'id';
  15.     const AUTO = 'id';
  17.     const STRUCT = [
  18.         'id'        => [],
  19.         'name'      => [],
  20.         'create_at' => [],
  21.         'role'      => [
  22.             self::MANY_MANY         => Role::class,
  23.             self::MIDDLE_ENTITY     => UserRole::class,
  24.             self::SOURCE_KEY        => 'id',
  25.             self::TARGET_KEY        => 'id',
  26.             self::MIDDLE_SOURCE_KEY => 'user_id',
  27.             self::MIDDLE_TARGET_KEY => 'role_id',
  28.         ],
  29.         'role_soft_deleted'      => [
  30.             self::MANY_MANY         => RoleSoftDeleted::class,
  31.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  32.             self::SOURCE_KEY        => 'id',
  33.             self::TARGET_KEY        => 'id',
  34.             self::MIDDLE_SOURCE_KEY => 'user_id',
  35.             self::MIDDLE_TARGET_KEY => 'role_id',
  36.         ],
  37.         'role_middle_with_soft_deleted'      => [
  38.             self::MANY_MANY         => RoleSoftDeleted::class,
  39.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  40.             self::SOURCE_KEY        => 'id',
  41.             self::TARGET_KEY        => 'id',
  42.             self::MIDDLE_SOURCE_KEY => 'user_id',
  43.             self::MIDDLE_TARGET_KEY => 'role_id',
  44.             self::RELATION_SCOPE    => 'withSoftDeleted',
  45.         ],
  46.         'role_middle_only_soft_deleted'      => [
  47.             self::MANY_MANY         => RoleSoftDeleted::class,
  48.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  49.             self::SOURCE_KEY        => 'id',
  50.             self::TARGET_KEY        => 'id',
  51.             self::MIDDLE_SOURCE_KEY => 'user_id',
  52.             self::MIDDLE_TARGET_KEY => 'role_id',
  53.             self::RELATION_SCOPE    => 'onlySoftDeleted',
  54.         ],
  55.         'role_relation_scope_not_found'      => [
  56.             self::MANY_MANY         => RoleSoftDeleted::class,
  57.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  58.             self::SOURCE_KEY        => 'id',
  59.             self::TARGET_KEY        => 'id',
  60.             self::MIDDLE_SOURCE_KEY => 'user_id',
  61.             self::MIDDLE_TARGET_KEY => 'role_id',
  62.             self::RELATION_SCOPE    => 'notFound',
  63.         ],
  64.         'role_relation_scope_found_but_private'      => [
  65.             self::MANY_MANY         => RoleSoftDeleted::class,
  66.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  67.             self::SOURCE_KEY        => 'id',
  68.             self::TARGET_KEY        => 'id',
  69.             self::MIDDLE_SOURCE_KEY => 'user_id',
  70.             self::MIDDLE_TARGET_KEY => 'role_id',
  71.             self::RELATION_SCOPE    => 'foundButPrivate',
  72.         ],
  73.         'role_not_defined_middle_entity'      => [
  74.             self::MANY_MANY         => Role::class,
  75.             self::SOURCE_KEY        => 'id',
  76.             self::TARGET_KEY        => 'id',
  77.             self::MIDDLE_SOURCE_KEY => 'user_id',
  78.             self::MIDDLE_TARGET_KEY => 'role_id',
  79.         ],
  80.         'role_not_defined_source_key'      => [
  81.             self::MANY_MANY         => Role::class,
  82.             self::MIDDLE_ENTITY     => UserRole::class,
  83.             self::TARGET_KEY        => 'id',
  84.             self::MIDDLE_SOURCE_KEY => 'user_id',
  85.             self::MIDDLE_TARGET_KEY => 'role_id',
  86.         ],
  87.         'role_not_defined_target_key'      => [
  88.             self::MANY_MANY         => Role::class,
  89.             self::MIDDLE_ENTITY     => UserRole::class,
  90.             self::SOURCE_KEY        => 'id',
  91.             self::MIDDLE_SOURCE_KEY => 'user_id',
  92.             self::MIDDLE_TARGET_KEY => 'role_id',
  93.         ],
  94.         'role_not_defined_middle_source_key'      => [
  95.             self::MANY_MANY         => Role::class,
  96.             self::MIDDLE_ENTITY     => UserRole::class,
  97.             self::SOURCE_KEY        => 'id',
  98.             self::TARGET_KEY        => 'id',
  99.             self::MIDDLE_TARGET_KEY => 'role_id',
  100.         ],
  101.         'role_not_defined_middle_target_key'      => [
  102.             self::MANY_MANY         => Role::class,
  103.             self::MIDDLE_ENTITY     => UserRole::class,
  104.             self::SOURCE_KEY        => 'id',
  105.             self::TARGET_KEY        => 'id',
  106.             self::MIDDLE_SOURCE_KEY => 'user_id',
  107.         ],
  108.         'role_middle_field'      => [
  109.             self::MANY_MANY         => Role::class,
  110.             self::MIDDLE_ENTITY     => UserRole::class,
  111.             self::SOURCE_KEY        => 'id',
  112.             self::TARGET_KEY        => 'id',
  113.             self::MIDDLE_SOURCE_KEY => 'user_id',
  114.             self::MIDDLE_TARGET_KEY => 'role_id',
  115.             self::RELATION_SCOPE    => 'middleField',
  116.         ],
  117.         'role_middle_only_soft_deleted_and_middle_field_and_other_table_condition' => [
  118.             self::MANY_MANY         => RoleSoftDeleted::class,
  119.             self::MIDDLE_ENTITY     => UserRoleSoftDeleted::class,
  120.             self::SOURCE_KEY        => 'id',
  121.             self::TARGET_KEY        => 'id',
  122.             self::MIDDLE_SOURCE_KEY => 'user_id',
  123.             self::MIDDLE_TARGET_KEY => 'role_id',
  124.             self::RELATION_SCOPE    => 'middleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition',
  125.         ],
  126.     ];
  128.     protected function relationScopeWithSoftDeleted(ManyMany $relation): void
  129.     {
  130.         $relation->middleWithSoftDeleted();
  131.     }
  133.     protected function relationScopeOnlySoftDeleted(ManyMany $relation): void
  134.     {
  135.         $relation->middleOnlySoftDeleted();
  136.     }
  138.     protected function relationScopeMiddleField(ManyMany $relation): void
  139.     {
  140.         $relation->middleField(['create_at', 'middle_id' => 'id']);
  141.     }
  143.     protected function relationScopeMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition(ManyMany $relation): void
  144.     {
  145.         $relation
  146.             ->middleOnlySoftDeleted()
  147.             ->middleField(['create_at', 'middle_id' => 'id'])
  148.             ->setColumns('id,name')
  149.             ->where('id', '>', 3);
  150.     }
  152.     private function relationScopeFoundButPrivate(ManyMany $relation): void
  153.     {
  154.     }
  155. }

Tests\Database\Ddd\Entity\Relation\UserRoleSoftDeleted

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  6. class UserRoleSoftDeleted extends Entity
  7. {
  8.     use GetterSetter;
  10.     const TABLE = 'user_role_soft_deleted';
  12.     const ID = 'id';
  14.     const AUTO = 'id';
  16.     const STRUCT = [
  17.         'id'        => [],
  18.         'user_id'   => [],
  19.         'role_id'   => [],
  20.         'create_at' => [],
  21.         'delete_at' => [
  22.             self::CREATE_FILL => 0,
  23.         ],
  24.     ];
  26.     const DELETE_AT = 'delete_at';
  27. }

Tests\Database\Ddd\Entity\Relation\RoleSoftDeleted

  1. namespace Tests\Database\Ddd\Entity\Relation;
  3. use Leevel\Database\Ddd\Entity;
  4. use Leevel\Database\Ddd\GetterSetter;
  6. class RoleSoftDeleted extends Entity
  7. {
  8.     use GetterSetter;
  10.     const TABLE = 'role_soft_deleted';
  12.     const ID = 'id';
  14.     const AUTO = 'id';
  16.     const STRUCT = [
  17.         'id'        => [],
  18.         'name'      => [],
  19.         'create_at' => [],
  20.         'delete_at' => [
  21.             self::CREATE_FILL => 0,
  22.         ],
  23.     ];
  25.     const DELETE_AT = 'delete_at';
  26. }
  1. public function testSoftDeleted(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role_soft_deleted')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role_soft_deleted')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role_soft_deleted')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role_soft_deleted')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role_soft_deleted')
  60.             ->insert([
  61.                 'user_id' => 1,
  62.                 'role_id' => 3,
  63.             ])
  64.     );
  66.     $user = User::select()->where('id', 1)->findOne();
  68.     $sql = <<<'eot'
  69.         SQL: [64] SELECT `user`.* FROM `user` WHERE `user`.`id` = :user_id LIMIT 1 | Params:  1 | Key: Name: [8] :user_id | paramno=0 | name=[8] ":user_id" | is_param=1 | param_type=1 (SELECT `user`.* FROM `user` WHERE `user`.`id` = 1 LIMIT 1)
  70.         eot;
  71.     $this->assertSame(
  72.         $sql,
  73.         User::select()->getLastSql(),
  74.     );
  76.     $this->assertSame(1, $user->id);
  77.     $this->assertSame(1, $user['id']);
  78.     $this->assertSame(1, $user->getId());
  79.     $this->assertSame('niu', $user->name);
  80.     $this->assertSame('niu', $user['name']);
  81.     $this->assertSame('niu', $user->getName());
  83.     $role = $user->roleSoftDeleted;
  85.     $sql = <<<'eot'
  86.         SQL: [490] SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` = :user_role_soft_deleted_delete_at WHERE `role_soft_deleted`.`delete_at` = :role_soft_deleted_delete_at AND `user_role_soft_deleted`.`user_id` IN (:user_role_soft_deleted_user_id_in0) | Params:  3 | Key: Name: [33] :user_role_soft_deleted_delete_at | paramno=0 | name=[33] ":user_role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [28] :role_soft_deleted_delete_at | paramno=1 | name=[28] ":role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [35] :user_role_soft_deleted_user_id_in0 | paramno=2 | name=[35] ":user_role_soft_deleted_user_id_in0" | is_param=1 | param_type=1 (SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` = 0 WHERE `role_soft_deleted`.`delete_at` = 0 AND `user_role_soft_deleted`.`user_id` IN (1))
  87.         eot;
  88.     $this->assertSame(
  89.         $sql,
  90.         User::select()->getLastSql(),
  91.     );
  93.     $this->assertInstanceof(Collection::class, $role);
  95.     $role1 = $role[0];
  97.     $this->assertInstanceof(RoleSoftDeleted::class, $role1);
  98.     $this->assertSame(1, $role1->id);
  99.     $this->assertSame(1, $role1['id']);
  100.     $this->assertSame(1, $role1->getId());
  101.     $this->assertSame('管理员', $role1->name);
  102.     $this->assertSame('管理员', $role1['name']);
  103.     $this->assertSame('管理员', $role1->getName());
  105.     $role2 = $role[1];
  107.     $this->assertInstanceof(RoleSoftDeleted::class, $role2);
  108.     $this->assertSame(3, $role2->id);
  109.     $this->assertSame(3, $role2['id']);
  110.     $this->assertSame(3, $role2->getId());
  111.     $this->assertSame('会员', $role2->name);
  112.     $this->assertSame('会员', $role2['name']);
  113.     $this->assertSame('会员', $role2->getName());
  115.     $this->assertCount(2, $role);
  116.     $this->assertSame(1, $role[0]['id']);
  117.     $this->assertSame('管理员', $role[0]['name']);
  118.     $this->assertSame(3, $role[1]['id']);
  119.     $this->assertSame('会员', $role[1]['name']);
  121.     $middle = $role[0]->middle();
  122.     $this->assertInstanceof(UserRoleSoftDeleted::class, $middle);
  123.     $this->assertSame(1, $middle->userId);
  124.     $this->assertSame(1, $middle->roleId);
  126.     $middle = $role[1]->middle();
  127.     $this->assertInstanceof(UserRoleSoftDeleted::class, $middle);
  128.     $this->assertSame(1, $middle->userId);
  129.     $this->assertSame(3, $middle->roleId);
  130. }

middleWithSoftDeleted 中间实体包含软删除数据的数据库查询集合对象

通过关联作用域来设置中间实体包含软删除数据的数据库查询集合对象。

fixture 定义

  1. # Tests\Database\Ddd\Entity\Relation\User::relationScopeWithSoftDeleted
  2. protected function relationScopeWithSoftDeleted(ManyMany $relation): void
  3. {
  4.     $relation->middleWithSoftDeleted();
  5. }
  1. public function testWithMiddleSoftDeletedAndMiddleEntityHasSoftDeleted(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role_soft_deleted')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role_soft_deleted')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role_soft_deleted')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role_soft_deleted')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role_soft_deleted')
  60.             ->insert([
  61.                 'user_id'   => 1,
  62.                 'role_id'   => 3,
  63.                 'delete_at' => time(),
  64.             ])
  65.     );
  67.     $user = User::select()->where('id', 1)->findOne();
  69.     $sql = <<<'eot'
  70.         SQL: [64] SELECT `user`.* FROM `user` WHERE `user`.`id` = :user_id LIMIT 1 | Params:  1 | Key: Name: [8] :user_id | paramno=0 | name=[8] ":user_id" | is_param=1 | param_type=1 (SELECT `user`.* FROM `user` WHERE `user`.`id` = 1 LIMIT 1)
  71.         eot;
  72.     $this->assertSame(
  73.         $sql,
  74.         User::select()->getLastSql(),
  75.     );
  77.     $this->assertSame(1, $user->id);
  78.     $this->assertSame(1, $user['id']);
  79.     $this->assertSame(1, $user->getId());
  80.     $this->assertSame('niu', $user->name);
  81.     $this->assertSame('niu', $user['name']);
  82.     $this->assertSame('niu', $user->getName());
  84.     $role = $user->roleMiddleWithSoftDeleted;
  86.     $sql = <<<'eot'
  87.         SQL: [413] SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` WHERE `role_soft_deleted`.`delete_at` = :role_soft_deleted_delete_at AND `user_role_soft_deleted`.`user_id` IN (:user_role_soft_deleted_user_id_in0) | Params:  2 | Key: Name: [28] :role_soft_deleted_delete_at | paramno=0 | name=[28] ":role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [35] :user_role_soft_deleted_user_id_in0 | paramno=1 | name=[35] ":user_role_soft_deleted_user_id_in0" | is_param=1 | param_type=1 (SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` WHERE `role_soft_deleted`.`delete_at` = 0 AND `user_role_soft_deleted`.`user_id` IN (1))
  88.         eot;
  89.     $this->assertSame(
  90.         $sql,
  91.         User::select()->getLastSql(),
  92.     );
  94.     $this->assertInstanceof(Collection::class, $role);
  96.     $role1 = $role[0];
  98.     $this->assertInstanceof(RoleSoftDeleted::class, $role1);
  99.     $this->assertSame(1, $role1->id);
  100.     $this->assertSame(1, $role1['id']);
  101.     $this->assertSame(1, $role1->getId());
  102.     $this->assertSame('管理员', $role1->name);
  103.     $this->assertSame('管理员', $role1['name']);
  104.     $this->assertSame('管理员', $role1->getName());
  106.     $role2 = $role[1];
  108.     $this->assertInstanceof(RoleSoftDeleted::class, $role2);
  109.     $this->assertSame(3, $role2->id);
  110.     $this->assertSame(3, $role2['id']);
  111.     $this->assertSame(3, $role2->getId());
  112.     $this->assertSame('会员', $role2->name);
  113.     $this->assertSame('会员', $role2['name']);
  114.     $this->assertSame('会员', $role2->getName());
  116.     $this->assertCount(2, $role);
  117.     $this->assertSame(1, $role[0]['id']);
  118.     $this->assertSame('管理员', $role[0]['name']);
  119.     $this->assertSame(3, $role[1]['id']);
  120.     $this->assertSame('会员', $role[1]['name']);
  122.     $middle = $role[0]->middle();
  123.     $this->assertInstanceof(UserRoleSoftDeleted::class, $middle);
  124.     $this->assertSame(1, $middle->userId);
  125.     $this->assertSame(1, $middle->roleId);
  127.     $middle = $role[1]->middle();
  128.     $this->assertInstanceof(UserRoleSoftDeleted::class, $middle);
  129.     $this->assertSame(1, $middle->userId);
  130.     $this->assertSame(3, $middle->roleId);
  131. }

middleOnlySoftDeleted 中间实体仅仅包含软删除数据的数据库查询集合对象

通过关联作用域来设置中间实体仅仅包含软删除数据的数据库查询集合对象。

fixture 定义

  1. # Tests\Database\Ddd\Entity\Relation\User::relationScopeOnlySoftDeleted
  2. protected function relationScopeOnlySoftDeleted(ManyMany $relation): void
  3. {
  4.     $relation->middleOnlySoftDeleted();
  5. }
  1. public function testOnlyMiddleSoftDeletedAndMiddleEntityHasSoftDeleted(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role_soft_deleted')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role_soft_deleted')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role_soft_deleted')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role_soft_deleted')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role_soft_deleted')
  60.             ->insert([
  61.                 'user_id'   => 1,
  62.                 'role_id'   => 3,
  63.                 'delete_at' => time(),
  64.             ])
  65.     );
  67.     $user = User::select()->where('id', 1)->findOne();
  69.     $sql = <<<'eot'
  70.         SQL: [64] SELECT `user`.* FROM `user` WHERE `user`.`id` = :user_id LIMIT 1 | Params:  1 | Key: Name: [8] :user_id | paramno=0 | name=[8] ":user_id" | is_param=1 | param_type=1 (SELECT `user`.* FROM `user` WHERE `user`.`id` = 1 LIMIT 1)
  71.         eot;
  72.     $this->assertSame(
  73.         $sql,
  74.         User::select()->getLastSql(),
  75.     );
  77.     $this->assertSame(1, $user->id);
  78.     $this->assertSame(1, $user['id']);
  79.     $this->assertSame(1, $user->getId());
  80.     $this->assertSame('niu', $user->name);
  81.     $this->assertSame('niu', $user['name']);
  82.     $this->assertSame('niu', $user->getName());
  84.     $role = $user->roleMiddleOnlySoftDeleted;
  86.     $sql = <<<'eot'
  87.         SQL: [490] SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` > :user_role_soft_deleted_delete_at WHERE `role_soft_deleted`.`delete_at` = :role_soft_deleted_delete_at AND `user_role_soft_deleted`.`user_id` IN (:user_role_soft_deleted_user_id_in0) | Params:  3 | Key: Name: [33] :user_role_soft_deleted_delete_at | paramno=0 | name=[33] ":user_role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [28] :role_soft_deleted_delete_at | paramno=1 | name=[28] ":role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [35] :user_role_soft_deleted_user_id_in0 | paramno=2 | name=[35] ":user_role_soft_deleted_user_id_in0" | is_param=1 | param_type=1 (SELECT `role_soft_deleted`.*,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` > 0 WHERE `role_soft_deleted`.`delete_at` = 0 AND `user_role_soft_deleted`.`user_id` IN (1))
  88.         eot;
  89.     $this->assertSame(
  90.         $sql,
  91.         User::select()->getLastSql(),
  92.     );
  94.     $this->assertInstanceof(Collection::class, $role);
  96.     $role1 = $role[0];
  98.     $this->assertInstanceof(RoleSoftDeleted::class, $role1);
  99.     $this->assertSame(3, $role1->id);
  100.     $this->assertSame(3, $role1['id']);
  101.     $this->assertSame(3, $role1->getId());
  102.     $this->assertSame('会员', $role1->name);
  103.     $this->assertSame('会员', $role1['name']);
  104.     $this->assertSame('会员', $role1->getName());
  106.     $role2 = $role[1];
  108.     $this->assertNull($role2);
  110.     $this->assertCount(1, $role);
  111.     $this->assertSame(3, $role[0]['id']);
  112.     $this->assertSame('会员', $role[0]['name']);
  113.     $this->assertNull($role[1]['id'] ?? null);
  114.     $this->assertNull($role[1]['name'] ?? null);
  116.     $middle = $role[0]->middle();
  117.     $this->assertInstanceof(UserRoleSoftDeleted::class, $middle);
  118.     $this->assertSame(1, $middle->userId);
  119.     $this->assertSame(3, $middle->roleId);
  120. }

middleOnlySoftDeleted.middleField.where 组合条件查询例子

通过关联作用域来设置组合查询条件。

fixture 定义

  1. # Tests\Database\Ddd\Entity\Relation\User::relationScopeMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition
  2. protected function relationScopeMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition(ManyMany $relation): void
  3. {
  4.     $relation
  5.         ->middleOnlySoftDeleted()
  6.         ->middleField(['create_at', 'middle_id' => 'id'])
  7.         ->setColumns('id,name')
  8.         ->where('id', '>', 3);
  9. }
  1. public function testMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role_soft_deleted')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role_soft_deleted')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         3,
  39.         $connect
  40.             ->table('role_soft_deleted')
  41.             ->insert([
  42.                 'name' => '会员',
  43.             ])
  44.     );
  46.     $this->assertSame(
  47.         1,
  48.         $connect
  49.             ->table('user_role_soft_deleted')
  50.             ->insert([
  51.                 'user_id' => 1,
  52.                 'role_id' => 1,
  53.             ])
  54.     );
  56.     $this->assertSame(
  57.         2,
  58.         $connect
  59.             ->table('user_role_soft_deleted')
  60.             ->insert([
  61.                 'user_id'   => 1,
  62.                 'role_id'   => 3,
  63.                 'delete_at' => time(),
  64.             ])
  65.     );
  67.     $user = User::select()->where('id', 1)->findOne();
  69.     $sql = <<<'eot'
  70.         SQL: [64] SELECT `user`.* FROM `user` WHERE `user`.`id` = :user_id LIMIT 1 | Params:  1 | Key: Name: [8] :user_id | paramno=0 | name=[8] ":user_id" | is_param=1 | param_type=1 (SELECT `user`.* FROM `user` WHERE `user`.`id` = 1 LIMIT 1)
  71.         eot;
  72.     $this->assertSame(
  73.         $sql,
  74.         User::select()->getLastSql(),
  75.     );
  77.     $this->assertSame(1, $user->id);
  78.     $this->assertSame(1, $user['id']);
  79.     $this->assertSame(1, $user->getId());
  80.     $this->assertSame('niu', $user->name);
  81.     $this->assertSame('niu', $user['name']);
  82.     $this->assertSame('niu', $user->getName());
  84.     $role = $user->roleMiddleOnlySoftDeletedAndMiddleFieldAndOtherTableCondition;
  86.     $sql = <<<'eot'
  87.         SQL: [655] SELECT `role_soft_deleted`.`id`,`role_soft_deleted`.`name`,`user_role_soft_deleted`.`create_at`,`user_role_soft_deleted`.`id` AS `middle_id`,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` > :user_role_soft_deleted_delete_at WHERE `role_soft_deleted`.`delete_at` = :role_soft_deleted_delete_at AND `role_soft_deleted`.`id` > :role_soft_deleted_id AND `user_role_soft_deleted`.`user_id` IN (:user_role_soft_deleted_user_id_in0) | Params:  4 | Key: Name: [33] :user_role_soft_deleted_delete_at | paramno=0 | name=[33] ":user_role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [28] :role_soft_deleted_delete_at | paramno=1 | name=[28] ":role_soft_deleted_delete_at" | is_param=1 | param_type=1 | Key: Name: [21] :role_soft_deleted_id | paramno=2 | name=[21] ":role_soft_deleted_id" | is_param=1 | param_type=1 | Key: Name: [35] :user_role_soft_deleted_user_id_in0 | paramno=3 | name=[35] ":user_role_soft_deleted_user_id_in0" | is_param=1 | param_type=1 (SELECT `role_soft_deleted`.`id`,`role_soft_deleted`.`name`,`user_role_soft_deleted`.`create_at`,`user_role_soft_deleted`.`id` AS `middle_id`,`user_role_soft_deleted`.`role_id` AS `middle_role_id`,`user_role_soft_deleted`.`user_id` AS `middle_user_id` FROM `role_soft_deleted` INNER JOIN `user_role_soft_deleted` ON `user_role_soft_deleted`.`role_id` = `role_soft_deleted`.`id` AND `user_role_soft_deleted`.`delete_at` > 0 WHERE `role_soft_deleted`.`delete_at` = 0 AND `role_soft_deleted`.`id` > 3 AND `user_role_soft_deleted`.`user_id` IN (1))
  88.         eot;
  89.     $this->assertSame(
  90.         $sql,
  91.         User::select()->getLastSql(),
  92.     );
  94.     $this->assertInstanceof(Collection::class, $role);
  95.     $this->assertFalse(isset($role[0]));
  96. }

middleField 中间实体查询字段

通过关联作用域来设置中间实体查询字段。

fixture 定义

  1. # Tests\Database\Ddd\Entity\Relation\User::relationScopeMiddleField
  2. protected function relationScopeMiddleField(ManyMany $relation): void
  3. {
  4.     $relation->middleField(['create_at', 'middle_id' => 'id']);
  5. }
  1. public function testMiddleField(): void
  2. {
  3.     $user = User::select()->where('id', 1)->findOne();
  5.     $this->assertInstanceof(User::class, $user);
  6.     $this->assertNull($user->id);
  8.     $connect = $this->createDatabaseConnect();
  10.     $this->assertSame(
  11.         1,
  12.         $connect
  13.             ->table('user')
  14.             ->insert([
  15.                 'name' => 'niu',
  16.             ])
  17.     );
  19.     $this->assertSame(
  20.         1,
  21.         $connect
  22.             ->table('role')
  23.             ->insert([
  24.                 'name' => '管理员',
  25.             ])
  26.     );
  28.     $this->assertSame(
  29.         2,
  30.         $connect
  31.             ->table('role')
  32.             ->insert([
  33.                 'name' => '版主',
  34.             ])
  35.     );
  37.     $this->assertSame(
  38.         1,
  39.         $connect
  40.             ->table('user_role')
  41.             ->insert([
  42.                 'user_id' => 1,
  43.                 'role_id' => 2,
  44.             ])
  45.     );
  47.     $user = User::select()->where('id', 1)->findOne();
  49.     $sql = <<<'eot'
  50.         SQL: [64] SELECT `user`.* FROM `user` WHERE `user`.`id` = :user_id LIMIT 1 | Params:  1 | Key: Name: [8] :user_id | paramno=0 | name=[8] ":user_id" | is_param=1 | param_type=1 (SELECT `user`.* FROM `user` WHERE `user`.`id` = 1 LIMIT 1)
  51.         eot;
  52.     $this->assertSame(
  53.         $sql,
  54.         User::select()->getLastSql(),
  55.     );
  57.     $this->assertSame(1, $user->id);
  58.     $this->assertSame(1, $user['id']);
  59.     $this->assertSame(1, $user->getId());
  60.     $this->assertSame('niu', $user->name);
  61.     $this->assertSame('niu', $user['name']);
  62.     $this->assertSame('niu', $user->getName());
  64.     $role = $user->roleMiddleField;
  66.     $sql = <<<'eot'
  67.         SQL: [285] SELECT `role`.*,`user_role`.`create_at`,`user_role`.`id` AS `middle_id`,`user_role`.`role_id` AS `middle_role_id`,`user_role`.`user_id` AS `middle_user_id` FROM `role` INNER JOIN `user_role` ON `user_role`.`role_id` = `role`.`id` WHERE `user_role`.`user_id` IN (:user_role_user_id_in0) | Params:  1 | Key: Name: [22] :user_role_user_id_in0 | paramno=0 | name=[22] ":user_role_user_id_in0" | is_param=1 | param_type=1 (SELECT `role`.*,`user_role`.`create_at`,`user_role`.`id` AS `middle_id`,`user_role`.`role_id` AS `middle_role_id`,`user_role`.`user_id` AS `middle_user_id` FROM `role` INNER JOIN `user_role` ON `user_role`.`role_id` = `role`.`id` WHERE `user_role`.`user_id` IN (1))
  68.         eot;
  69.     $this->assertSame(
  70.         $sql,
  71.         User::select()->getLastSql(),
  72.     );
  74.     $this->assertInstanceof(Collection::class, $role);
  75.     $this->assertCount(1, $role);
  77.     $role1 = $role[0];
  79.     $this->assertInstanceof(Role::class, $role1);
  80.     $this->assertSame(2, $role1->id);
  81.     $this->assertSame(2, $role1['id']);
  82.     $this->assertSame(2, $role1->getId());
  83.     $this->assertSame('版主', $role1->name);
  84.     $this->assertSame('版主', $role1['name']);
  85.     $this->assertSame('版主', $role1->getName());
  87.     $middle = $role[0]->middle();
  88.     $this->assertInstanceof(UserRole::class, $middle);
  89.     $this->assertSame(1, $middle->userId);
  90.     $this->assertSame(2, $middle->roleId);
  91. }