如何使用后备表建模软枚举

huangapple go评论71阅读模式
英文:

How to model a soft enum using a backing table

问题

I have an existing pair of tables, unfortunately I can't change anything about them since they come from a different application and at least for the foreseeable future that application still has to work on the database as well.

I have two tables:
partner:

id status salutation
1 1 2
2 2 2
3 1 1
4 1001 0

and code:

id code set label
21 1 1 "regular"
22 2 1 "vip"
23 1001 1 "repeated"
24 0 2 ""
25 1 2 "Madam"
26 2 2 "Sir"

Where a code.set of 1 corresponds to an entry for partner.status
and a code.set of 2 signals an entry for partner.salutation

I'm trying to model the relations but could not find out how to limit a specific relation to only a subset of the other table:
Entity/Partner.php

<?php

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: PartnerRepository::class)]
class Partner {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: "status", referencedColumnName: "code")]
    private ?Code $status = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: "salutation", referencedColumnName: "code")]
    private ?Code $salutation = null;
}

This now fetches the wrong labels for a status of 1 because of course the same value is used for both status = "regular" and salutation = "Madam"

Ideally I'd like to write something like

    #[ORM\ManyToOne(filter: "set = 1")]
    #[ORM\JoinColumn(name: "status", referencedColumnName: "code")]
    private ?Code $status = null;

    #[ORM\ManyToOne(filter: "set = 2")]
    #[ORM\JoinColumn(name: "salutation", referencedColumnName: "code")]
    private ?Code $salutation = null;

or maybe add a #[Filter] attribute, but I could not figure out the right syntax/ place to apply the Filter.

英文:

I have an existing pair of tables, unfortunately I can't change anything about them since they come from a different application and at least for the forseeable future that application still has to work on the database as well.

I have two tables:
partner:

id status salutation
1 1 2
2 2 2
3 1 1
4 1001 0

and code:

id code set label
21 1 1 "regular"
22 2 1 "vip"
23 1001 1 "repeated"
24 0 2 ""
25 1 2 "Madam"
26 2 2 "Sir"

Where a code.set of 1 corresponds to an entry for partner.status
and a code.set of 2 signals an entry for partner.salutation

I'm trying to model the relations but could not find out how to limit a specific relation to only a subset of the other table:
Entity/Partner.php

&lt;?php

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: PartnerRepository::class)]
class Partner {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: &quot;status&quot;, referencedColumnName: &quot;code&quot;)]
    private ?Code $status = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: &quot;salutation&quot;, referencedColumnName: &quot;code&quot;)]
    private ?Code $salutation = null;
}

This now fetches the wrong labels for a status of 1 because of course the same value is used for both status = &quot;regular&quot; and salutation = &quot;Madam&quot;

Ideally I'd like to write something like

    #[ORM\ManyToOne(filter: &quot;set = 1&quot;)]
    #[ORM\JoinColumn(name: &quot;status&quot;, referencedColumnName: &quot;code&quot;)]
    private ?Code $status = null;

    #[ORM\ManyToOne(filter: &quot;set = 2&quot;)]
    #[ORM\JoinColumn(name: &quot;salutation&quot;, referencedColumnName: &quot;code&quot;)]
    private ?Code $salutation = null;

or maybe add a #[Filter] attribute, but I could not figure out the right syntax/ place to apply the Filter.

答案1

得分: 1

以下是您要翻译的代码部分:

use Doctrine\ORM\Mapping as ORM;
use App\Repository\CodeRepository;

#[ORM\Entity(repositoryClass: PartnerRepository::class)]
class Partner {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: "status", referencedColumnName: "code")]
    private ?StatusCode $status = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: "salutation", referencedColumnName: "code")]
    private ?SalutationCode $salutation = null;
}

#[ORM\Entity(repositoryClass: CodeRepository::class)]
#[ORM\InheritanceType('SINGLE_TABLE')]
#[ORM\DiscriminatorColumn(name: 'set', type: 'bigint')]
#[ORM\DiscriminatorMap([1 => StatusCode::class, 2 => SalutationCode::class])]
class Code {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\OneToMany(targetEntity: Partner::class)]
    private ?Partner $code = null;

    #[ORM\Column]
    private ?string $label = null;
}
#[ORM\Entity]
class StatusCode extends Code {}
#[ORM\Entity]
class SalutationCode extends Code {}

请注意,我已经修复了HTML编码和引号问题,以便正确显示代码。

英文:

While not quite the solution I was looking for I'll probably end up using inheritance for the different code sets:

&lt;?php

use Doctrine\ORM\Mapping as ORM;
use App\Repository\CodeRepository;

#[ORM\Entity(repositoryClass: PartnerRepository::class)]
class Partner {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: &quot;status&quot;, referencedColumnName: &quot;code&quot;)]
    private ?StatusCode $status = null;

    #[ORM\ManyToOne]
    #[ORM\JoinColumn(name: &quot;salutation&quot;, referencedColumnName: &quot;code&quot;)]
    private ?SalutationCode $salutation = null;
}

#[ORM\Entity(repositoryClass: CodeRepository::class)]
#[ORM\InheritanceType(&#39;SINGLE_TABLE&#39;)]
#[ORM\DiscriminatorColumn(name: &#39;set&#39;, type: &#39;bigint&#39;)]
#[ORM\DiscriminatorMap([1 =&gt; StatusCode::class, 2 =&gt; SalutationCode::class])]
class Code {
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\OneToMany(targetEntity: Partner::class)]
    private ?Partner $code = null;

    #[ORM\Column]
    private ?string $label = null;
}
#[ORM\Entity]
class StatusCode extends Code {}
#[ORM\Entity]
class SalutationCode extends Code {}

huangapple
  • 本文由 发表于 2023年7月17日 18:17:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/76703483.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定