问题

I'm not terribly good with complex database queries and this melted my brain.

我对复杂的数据库查询不太擅长，这让我感到头疼。

I want to get Swimmers by specific certificate ID, but the certificate ID match should be determined by the order column.

我想通过特定的证书 ID 来获取游泳者，但证书 ID 的匹配应该由顺序列来确定。

Swimmers have multiple certificates and certificates can be added between existing certificates. Say the system starts off with Level 1 and Level 2. Eventually we have level 1.1, 1.2, and 1.3 so we have an order column to determine the next and previous certificate that should be earned.

游泳者拥有多个证书，而证书可以添加在现有的证书之间。假设系统从 Level 1 和 Level 2 开始。最终，我们有了 Level 1.1、1.2 和 1.3，因此我们有一个顺序列来确定下一个和前一个应该获得的证书。

The relationship looks like this:

关系看起来像这样：

Swimmer ( id ) -> SwimmerCertificate ( swimmer_id, certificate_id ) -> Certificate ( id, order, name )

游泳者 (id) -> 游泳者证书 (swimmer_id, certificate_id) -> 证书 (id, order, name)

Swimmer One = Swimmer->certificates = [ certificate_id = 1,2,3,4 ]

游泳者 One = 游泳者->证书 = [ 证书 ID = 1,2,3,4 ]

Swimmer Two = Swimmer->certificates = [ certificate_id = 1,2,4 ]

游泳者 Two = 游泳者->证书 = [ 证书 ID = 1,2,4 ]

$certificates = [
1 => [ ‘order’ => 1],
2 => [ ‘order’ => 2],
3 => [ ‘order’ => 4], // Higher order
4 => [ ‘order’ => 3]
];

$证书 = [
1 => [ ‘顺序’ => 1],
2 => [ ‘顺序’ => 2],
3 => [ ‘顺序’ => 4], // 更高的顺序
4 => [ ‘顺序’ => 3]
];

The swimmers maximum certificate is determined by max order.

游泳者的最高证书由最大的顺序决定。

So if I wanted swimmers by certificate ID 4 in this case I would not get this swimmer One I would get swimmer Two

所以，如果我想要根据证书 ID 4 获取游泳者，在这种情况下，我将不会获取到游泳者 One，我会获取到游泳者 Two

If I wanted swimmers by certificate ID 3 then I would get Swimmer One

如果我想要根据证书 ID 3 获取游泳者，那么我将获取到游泳者 One

I'd love to know how this should be approached.

我想知道应该如何处理这个问题。

Ultimately, I opted to add a current certificate to the Swimmers table maintained by a model observer.

最终，我选择在由模型观察者维护的游泳者表中添加一个当前证书。

Swimmer One = Swimmer->certificates = [ certificate_id = 1,2,3,4 ]
Swimmer Two = Swimmer->certificates = [ certificate_id = 1,2,4 ]

$certificates = [
    1 => [ ‘order’ => 1],
    2 => [ ‘order’ => 2],
    3 => [ ‘order’ => 4], // Higher order
    4 => [ ‘order’ => 3]
];

答案1

得分: 1

以下是代码部分的中文翻译：

基本查询：

SELECT swimmers.*, MAX(order) as highest_certificate from swimmers JOIN swimmercertificate ON swimmers.id = swimmercertificate.swimmer_id JOIN certificate ON certificate.id = swimmercertificate.certificate_id GROUP BY swimmers.id

然后，您可以在该结果上执行以下查询：

SELECT * FROM (SELECT swimmers.*, MAX(order) as highest_certificate from swimmers JOIN swimmercertificate ON swimmers.id = swimmercertificate.swimmer_id JOIN certificate ON certificate.id = swimmercertificate.certificate_id GROUP BY swimmers.id) swimmers_with_max JOIN certificates ON certificates.order = swimmers_with_max.highest_certificate

SELECT swimmers.*, MAX(order) as highest_certificate from swimmers JOIN swimmercertificate ON swimmers.id = swimmercertificate.swimmer_id JOIN certificate ON certificate.id = swimmercertificate.certificate_id GROUP BY swimmers.id

SELECT * FROM (SELECT swimmers.*, MAX(order) as highest_certificate from swimmers JOIN swimmercertificate ON swimmers.id = swimmercertificate.swimmer_id JOIN certificate ON certificate.id = swimmercertificate.certificate_id GROUP BY swimmers.id) swimmers_with_max JOIN certificates ON certificates.order = swimmers_with_max.highest_certificate