Symfony2 / Doctrine Relationship Mapping

Question

i'm building my first application with the syfmony2 framework. Now i stick a little bit on how to get data from my Database.

First here a my tables (only important fields)

# customer

customer_id | INT | AI | PK

# article

article_id | INT | AI | PK

number | VARCHAR

# customer_article (Both fields build the PK)

customer_id | INT | PK

article_id | INT | PK

Now i want to perform a simply query like this:

SELECT 
   ca.article_id, a.number 
FROM 
   customer_article AS ca
LEFT JOIN 
   article AS a ON a.article_id = ca.article_id
WHERE 
   ca.customer_id = {customer_id}

As far as i understand the documentation the best way is to make a Relationship Mapping in my entity php files.

Customer.php

Article.php

CustomerArticle.php

But i'm confused what to do and in which direction. (http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html)

In My opinion this must be the right relationship

Customer to CustomerArticle is a One-To-Many (Unidirectional) Relationship

CustomerArticle to Customer is a Many-To-One (Unidirectional) Relationship

Article to CustomerArticle is a One-To-Many (Unidirectional) Relationship

CustomerArticle to Article is a Many-To-One (Unidirectional) Relationship

Is it right at this point?

And now is it neccessary to make this (4) Relationships Mapping in every Entity file? Or do i only need to do it one way e.g. Customer to CustomerArticle (One-To-Many - Unidirectional) and Article to CustomerArticle (One-To-Many - Unidirectional)

unfortunately the "One-To-May, Unidirectional" is the only one with no proper example on the doctrine reference-page.

They write: "From Doctrine’s point of view, it is simply mapped as a unidirectional many-to-many whereby a unique constraint on one of the join columns enforces the one-to-many cardinality"

Does this mean i only have to do the mapping like on the "unidirectional many-to-many" section of the page?

Sorry about the many questions.

I read a lot in the documentations but i can't answer these questions for myself.

Thanks for any help.

Update 26.04.2013

Unfortunately i can't get it work even with the help from Lighthart and reading the Doctrine documentation.

I think it's helpfull to give you more details about my tables and code. Maybe someone can give me the essential answer so i finaly understand the concept behind it.

My Tables with some example Entries

Table: customer (PK: customer_id)
+-------------+--------+----------+-------------------+------+-----------+
| customer_id | number | password | email             | salt | is_active |
+-------------+--------+----------+-------------------+------+-----------+
| 1           | CU0001 | 43t9wef  | test@example.com  | test | 1         |
| 2           | CU0002 | 32rk329  | test2@example.com | test | 1         |
+-------------+--------+----------+-------------------+------+-----------+

Table: article (PK: article_id)
+-------------+---------+
| article_id  | number  |
+-------------+---------+
| 1           | TEST-01 |
| 2           | TEST-02 |
| 3           | TEST-03 |
| 4           | TEST-04 |
| 5           | TEST-05 |
+-------------+---------+

What i have done since my first Posting?

• Delete the CustomerArticle.php
• Delete the customer_article table
• Add a ManyToMany Relationship to the Customer (And Article) Entity

This is how the entity files look like at this moment (without methods)

Customer.php

<?php
namespace Test\ExampleBundle\Entity;

use Symfony\Component\Security\Core\User\UserInterface;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="customer")
 */
class Customer implements UserInterface
{
/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $customer_id;

/**
 * @ORM\ManyToMany(targetEntity="Article", inversedBy="customers")
 * @ORM\JoinTable(name="customer_article",
 *  joinColumns={ @ORM\JoinColumn(name="customer_id", referencedColumnName="customer_id" ) }
 * )
 */
private $articles;

/**
 * @ORM\Column(type="string", length=255, nullable=false, unique=true)
 */
protected $number;

/**
 * @ORM\Column(type="string", length=255, nullable=false)
 */
protected $password;

/**
 * @ORM\Column(type="string", length=255, nullable=true)
 */
protected $email;

/**
 * @ORM\Column(type="string", length=255)
 */
protected  $salt;

/**
 * @ORM\Column(name="is_active", type="boolean")
 *
 */
protected  $isActive;

// ... methods
}

Article.php

<?php
namespace Test\ExampleBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="article")
 */
class Article
{
/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $article_id;

/**
 * @ORM\ManyToMany(targetEntity="Customer", mappedBy="articles")
 */
private $customers;

/**
 * @ORM\Column(type="string", length=255, nullable=false, unique=true)
 */
protected $number;

// ... methods
}

When i try to execute:

php app/console doctrine:schema:update --force

I get the following Message

[Doctrine\ORM\ORMException]
Column name `id` referenced for relation from    Test\ExampleBundleBundle\Entity\Customer towards Test\ExampleBundleBundle\Entity\Article does not exist.

I don't know why doctrine wants to use a column name "id". I read something about this error and add the "JoinColumns"-Code but it didn't help.

What is my goal?

I import data from some csv-files into the tables: "customer", "article" and also into the currently not exist table "customer_article".

In my application every customer has a few articles referenced to him. So the Table will looks like this

customer_article
+-------------+------------+
| customer_id | article_id |
+-------------+------------+
| 1           | 1          |
| 1           | 2          |
| 1           | 5          |
| 2           | 2          |
| 2           | 5          |
+-------------+------------+

It's not a Shop or something like this. It's quite difficult to explain so first it's only neccessary to know that every customer has a undefined quantity of associated articles.

Now i must be able to get all articles assosiated with one customer using tthe customer_id. (Look at my example sql statement in the starting post above)

I hope you can help me.

Thanks a lot to everyone.

Answer 1

This really looks like a bidirectional many to many with customer and article. The entities you would build are Customer.php and Article.php, and you would specify a many-to-many in those files via annotation, or in doctrine.orm.yml depending on your preference.

Doctrine will make the intermediate table for you (customer_article) and manage it.

You would use DQL:

$query = $em->createQuery("SELECT a FROM Article a JOIN a.customer c");
$users = $query->getResult();

Then you would process articles as entities, and call article.getCustomer() for a list of customers associated with an article.

This is a fundamentally different paradigm than SQL and will take some getting used to.

Answer 2

Your mapping is bad. You specify jointable but don't specify the inverse join column. Try:

 /**
  * @ManyToMany(targetEntity="Article")
  * @JoinTable(name="customer_article",
  *      joinColumns={@JoinColumn(name="Customer_id", referencedColumnName="cusomter_id")},
  *      inverseJoinColumns={@JoinColumn(name="Article_id", referencedColumnName="article_id")}
  *      )
  */

As an aside, mapping your primary keys to something other than 'id' is somewhat unusual.