通过普通表建立关系正确的方式 [英] Establishing relationships through a common table the right way

问题描述

我在数据库内有三张表： interest ，订阅和 / code>。模式如下所示：

I have three tables inside of a database: interest, subscription and subscriber. The schema looks like this:

< img src =https://i.stack.imgur.com/31Hag.jpgalt =订阅数据库模式>

订阅表对兴趣和订阅者都有一对多的关系。我想要如何工作，我不知道我想要它如何工作，它的实际工作正在排队，但它应该是这样的：一个订阅者可以拥有多个兴趣，每个兴趣可以有多个订阅者。这将让我看看订阅者的兴趣和哪些兴趣已被订阅。那么，订阅只涉及一个订阅者和一个兴趣。我认为这是如何设置的，但是我刚刚做了一些测试来查询订阅表，我回到了这里：

The subscription table has a one to many relationship on both interest and subscriber. How I would like it to work, and I'm not sure if how I want it to work and how it's actually working are lining up, but it should be like this: a Subscriber can have multiple Interests, and each Interest can have multiple Subscribers. This will allow me to see which interests a subscriber has, and which interests have been subscribed to. Then, the Subscription relates only to one subscriber and one interest. I think that's how it's set up, but I just did some tests querying the subscription table and I got this back:

C:\xampp\htdocs\www\public_html\playground\subscribr>php list_subscriptions.php
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Promotions
[e-mail]
--Newsletter
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Promotions
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Promotions
[e-mail]
--Newsletter 

我想我预期结果更像这样：

I suppose I expected a result more like this:

C:\xampp\htdocs\www\public_html\playground\subscribr>php list_subscriptions.php
[e-mail]
--Magazine
--Newsletter
--Promotions
--Email
[e-mail]
--Magazine
--Promotions
[e-mail]
--Newsletter
--Email
[e-mail]
--Magazine
--Promotions
--Email
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Promotions
[e-mail]
--Newsletter

如果我通过订阅者查询并获取他们的兴趣，我会得到这个结果（通过订阅表）。我正在使用ORM，所以要执行的代码如下所示：

I do get this result if I query by subscriber, and grab their interests (through the subscription table). I'm using ORM, so the code to do that looks like this:

$subscriberRepo = $entityManager->getRepository( 'Subscribr\Entity\Subscriber' );
$subscribers = $subscriberRepo->findAll();

foreach ($subscribers as $subscriber) {
    echo sprintf( "-%s\n", $subscriber->getEmail() );
    foreach ($subscriber->getInterests() as $interest) {
        echo sprintf( "--%s\n", $interest->getInterest()->getName() );
    }
}

所以我可以得到订阅者的订阅和兴趣被订阅，使用这个模式，但是有没有保留订阅表的一点？还是整个事情需要重做，做我想要做的事情？我喜欢在用户和兴趣之间建立间接关系，我希望在订阅表中添加额外的列，例如从订阅表中删除 is_subscribed ，并在订阅中添加列 subscription_status ，以这种方式，这个解决方案感觉最干净。然而，它也几乎像一个额外的字段的连接表。想法？

So I can get a subscriber's subscriptions, and interests that are subscribed to, using this schema, but then is there a point in keeping the subscription table around? Or does the whole thing need to be reworked to do what I'd like it to do? I like the having an indirect relationship between subscriber and interests, incase I wanted to add extra columns to the subscription table like removing is_subscribed from the subscriber table and adding a column in subscription called subscription_status, in that way this solution feels the cleanest. However, it also almost feels like a join table with extra fields. Thoughts?

推荐答案

根据您的解释，您可以正确地了解需要映射的内容。简单的方法有两种：

Based on your explanation a could understand properly what you need to map. In a simplified way you have two options:

选项1

订阅者 1 - > n 订阅

订阅 n <1 兴趣

Subscriber 1 -> n Subscription
Subscription n <- 1 Interest

这意味着间接：订阅者 n - > n 兴趣

看不到？让代码看起来更好：

It means indirectly: Subscriber n -> n Interest
Don't see? Lets code to see better:

foreach ($subscribers as $subscriber) {
   echo sprintf( "-%s\n", $subscriber->getEmail() );
   foreach ($subscriber->getSubscriptions() as $subscription) {
      echo sprintf( "--%s\n", $subscription->getInterest()->getName() );
   }
}

编辑

上面的代码将与您的映射中的以下更改一起使用。此代码说明：迭代订阅者，其中每个订阅者都有订阅列表，每个订阅都有兴趣。

The above code will work with the below changes in your mapping. This code explanation: Iterate subscribers where each subscriber has a list of subscriptions and each subscription referes on interest.

Subscriber.php

 /**
  * @var array
  * 
  * @ORM\OneToMany(targetEntity="Subscription", mappedBy="subscriber",    cascade={"persist", "remove"}, orphanRemoval=TRUE)
  */
  private $subscriptions;

您应该删除 private $兴趣; 订户。它不会直接访问兴趣。

You should remove private $interests; from Subscriber. It won't access Interest directly.

Subscription.php

/**
 * @var Interest
 *
 * @ORM\ManyToOne(targetEntity="Interest", inversedBy="subscribers")
 * @ORM\JoinColumn(name="interest_id", referencedColumnName="id", nullable=FALSE)
 */
private $interest;

注意每个订阅将访问一个兴趣。

这个第一个选项是你最好的。

This first option is the best in your case.

选项2

您可以删除订阅类并使用其表作为参考表。并直接映射：订阅者 n - > n 兴趣。

You could remove Subscription class and use its table as a reference table. And map directly: Subscriber n -> n Interest.

class Subscriber {

     /**
      * @var array
      * 
      * @ORM\ManyToMany(targetEntity="Interest", cascade={"persist", "remove"}, orphanRemoval=TRUE)
      * @ORM\JoinTable(name="subscription",
      *      joinColumns={@ORM\JoinColumn(name="subscriber_id", referencedColumnName="id")},
      *      inverseJoinColumns={@ORM\JoinColumn(name="interest_id", referencedColumnName="id")}
      *      )
      */
      private $interests;
}

此选项中的问题不能使用订阅。 interest_date 'cause subscription table现在只是一个关系表。关于这个问题。

The issue in this option you cannot use subscription.interest_date 'cause subscription table is now only a relation table. More explanation about that in this question.

这篇关于通过普通表建立关系正确的方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！