Symfony 2.2 Doctrine2多对多关系插入查询

I'm using Doctrine2 in Symfony 2.2. I have the following entities:

我在Symfony 2.2中使用Doctrine2。我有以下实体:

"User"
"OrganizationSupplier"

and m:n relation between those entities. Here is how it is in schema:

和m:n这些实体之间的关系。这是架构中的方式:

class User {
.....
    /**
     * @ORM\ManyToMany(targetEntity="OrganizationSupplier", inversedBy="users")
     * @ORM\JoinTable(name="User_Supplier",
     *      inverseJoinColumns={@ORM\JoinColumn(name="organization_supplier_id", referencedColumnName="id")},
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}
     *      )
     */
    private $allowed_suppliers;
.....
}

and

class OrganizationSupplier
{
...
    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="allowed_suppliers")
     */
    private $users;
...
}

Here is m:n table created by Doctrine:

这是由Doctrine创建的m:n表:

CREATE TABLE `User_Supplier` (
  `user_id` int(11) NOT NULL,
  `organization_supplier_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`organization_supplier_id`),
  KEY `IDX_31AFA1DDA76ED395` (`user_id`),
  KEY `IDX_31AFA1DD1A81C9F7` (`organization_supplier_id`),
  CONSTRAINT `FK_31AFA1DD1A81C9F7` FOREIGN KEY (`organization_supplier_id`) REFERENCES `organizationsupplier` (`id`),
  CONSTRAINT `FK_31AFA1DDA76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is the data for user id=638 from this m:n table:

以下是来自此m:n表的用户id = 638的数据:

user_id organization_supplier_id
638 618
638 620
638 622
638 624
638 626
638 628
638 630
638 632
638 634
638 636

Everything is fine up to this point.

到目前为止,一切都很好。

Now the problem: I have a form in admin that modifies this m:n table by adding/removing records from it. From business logic perspective I need to assign/unassign suppliers to certain users from the list of suppliers assigned to organization (OrgaizationSupplier entity - see above)

现在问题是:我在管理员中有一个表单通过添加/删除记录来修改这个m:n表。从业务逻辑的角度来看,我需要从分配给组织的供应商列表中为某些用户分配/取消分配供应商(OrgaizationSupplier实体 - 见上文)

Here is the code for this field of the form:

以下是表单中此字段的代码:

->add('allowed_suppliers', 'entity', array('class'=>'STMainBundle:OrganizationSupplier', 'multiple'=>true, 'expanded'=>true,
        'query_builder' => function(EntityRepository $er) use ($user) {
            return $er->createQueryBuilder('os')
                ->where('os.organization = :organization')
                ->setParameter(':organization', $user->getOrganization());

If I submit it with a checkbox checked, I'm getting an SQL exception, it tries to launch a query with swapped columns:

如果我在选中复选框的情况下提交它,我会收到SQL异常,它会尝试使用交换列启动查询:

INSERT INTO User_Supplier (organization_supplier_id, user_id) VALUES (?, ?)' with params [638, 618]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sollod_new`.`user_supplier`, CONSTRAINT `FK_31AFA1DDA76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))".

so it should be vice versa, [618, 638] and not as it tries [638,618].

所以反之亦然[618,638]而不是它[638,618]。

I'm a newbie in symfony, so I'm trying to find the place where it builds this query on postack, to control this parameters order.

我是symfony的新手,所以我试图找到它在postack上构建这个查询的地方,以控制这个参数顺序。

Does anyone know where I can find it?

有谁知道我能在哪里找到它?

UPDATE: Here is ladybug dump of Request, User entity and Form. This is done right after $form->bind($request);

更新:这是请求,用户实体和表单的瓢虫转储。这是在$ form-> bind($ request)之后立即完成的;

Thanks a lot

非常感谢

UPDATE: My bad, I forgot that schema is cached as well, the schema that I gave above was already the correct and fixed one:

更新:我的不好,我忘记了架构也被缓存了,我上面给出的架构已经是正确且固定的架构:

 *      inverseJoinColumns={@ORM\JoinColumn(name="organization_supplier_id", referencedColumnName="id")},
 *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}

Initially these columns were swapped. I only had to clear dev/prod cache to make it work. Sorry about that

最初这些列是交换的。我只需要清除dev / prod缓存就可以了。对于那个很抱歉

1 个解决方案

#1

It was a matter of cache. Initially the data model was wrong, I fixed it to be:

这是一个缓存的问题。最初数据模型错了,我把它修复为:

     *      inverseJoinColumns={@ORM\JoinColumn(name="organization_supplier_id", referencedColumnName="id")},
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}

and forgot to clear cache

并忘了清除缓存

#1