How to model a soft enum using a backing table

Question

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

<?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.

Answer 1

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

<?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: "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 {}