Mapping the same property to multiple entities in doctrine

[work77]

Since Doctrine needs to be "aware" of all my mappings before I can use them in a DQL join statement, what is the correct way to map the same property to multiple entities if I need to?
And what would the DQL look like since, technically the product_id is mapped to two different objects, Feature and Foo? I simply want to run the following sql, but can't sort my way through the doctrine puzzle.

"SELECT * FROM Product p
INNER JOIN Feature f1 p.id=f1.product_id
INNER JOIN Foo f2 ON f1.product_id=p.foo_id"

Code: Select all

/** @Entity **/
class Feature
{
    /**
     * @ManyToOne(targetEntity="Product", inversedBy="features")
     * @JoinColumn(name="product_id", referencedColumnName="id")
     * @OneToOne(targetEntity="Feature")
     * @JoinColumn(referencedColumnName="foo_id")
     **/
    private $product;
    // ...
}


/** @Entity **/
class Foo
{
    /**
     * @Column(name="foo_id")
     **/
    private $foo_id;
    // ...
}


[dreipunktnull]

I'm not sure what you are trying to achieve but you can't declare two relations on one property. Is Foo related to Product or to Feature? If it is related to product the DQL would look like

Code: Select all

SELECT p, f1, f2 FROM Product p JOIN p.feature f1 JOIN p.foo f2

This would fetch all Products and their related Features and Foos. No need to worry about ids here. This will be managed by Doctrine.

[work77]

I'm not sure what you are trying to achieve but you can't declare two relations on one property. Is Foo related to Product or to Feature? If it is related to product the DQL would look like

Code: Select all

SELECT p, f1, f2 FROM Product p JOIN p.feature f1 JOIN p.foo f2

This would fetch all Products and their related Features and Foos. No need to worry about ids here. This will be managed by Doctrine.

TFR. Maybe a better way to ask is simply - If I want to be able to run EACH of these queries somewhere in my app, then what would p.blah's mapping and DQL look like? Notice p.blah is related to TWO separate entities.

SELECT * FROM Product p JOIN foo ON p.blah=foo.blah
SELECT * FROM Product p JOIN bar ON p.blah=bar.blah

[dreipunktnull]

Ok. For three entities which are related

Product 1:n Feature
Product 1:n Bar
Feature 1:1 Foo
Bar 1:1 Foo

the DQL would most probably look like

Code: Select all

SELECT p, f, ff FROM Product p JOIN feature f JOIN f.foo ff
SELECT p, b, bf FROM Product p JOIN bar b JOIN b.foo bf


and the mapping:

Code: Select all

/** @Entity **/
class Product
{
    /**
     * @OneToMany(targetEntity="Feature", mappedBy="product")
     **/
    private $features;
    // ...
}

/** @Entity **/
class Feature
{
    /**
     * @ManyToOne(targetEntity="Product", inversedBy="features")
     **/
    private $product;

    /**
     * @OneToOne(targetEntity="Foo", mappedBy="feature")
     **/
    private $foo;
    // ...
}

/** @Entity **/
class Bar
{
    /**
     * @ManyToOne(targetEntity="Product", inversedBy="bars")
     **/
    private $product;

    /**
     * @OneToOne(targetEntity="Foo", mappedBy="bar")
     **/
    private $foo;
    // ...
}

/** @Entity **/
class Foo
{
    /**
     * @OneToOne(targetEntity="Feature", inversedBy="foo")
     **/
    private $feature;

    /**
     * @OneToOne(targetEntity="Bar", inversedBy="foo")
     **/
    private $bar;
    // ...
}


HTH

[work77]

the DQL would most probably look like

Code: Select all

SELECT p, f, ff FROM Product p JOIN feature f JOIN f.foo ff
SELECT p, b, bf FROM Product p JOIN bar b JOIN b.foo bf


Is the reason you put 'JOIN' in your DQL twice because both Feature and Bar have been mapped to it, and doing something like the following would be ambiguous?
"SELECT p, myobj FROM Product p JOIN p.id myobj"
I'm thinking it might be good practice then to always have two "JOIN"s per join, since you can't tell just by looking at an Entity's property what other Entity's properties might be mapped to it, but that seems kind of ugly.

I'm completely confused by the entity defs.
Would it be too greedy to ask you to do the same, but assume this relation:
Product n:1 Feature
Product n:1 Bar

I've thoroughly read the docs here, but I see nothing like the scenerio you've drawn up for me: "http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/association-mapping.html#association-mapping-owning-inverse"

Thanks again.

[dreipunktnull]

You spotted a... well let's call it typo in the query which should look like this (assuming Foo is related to Feature and Bar which is not the case according to your new post):

Code: Select all

SELECT p, f, ff FROM Product p JOIN p.feature f JOIN f.foo ff
SELECT p, b, bf FROM Product p JOIN p.bar b JOIN b.foo bf


IMHO the most common problem people have when using Doctrine (or DQL) for the first time - and that of course includes me as well - is that you need to stop thinking in tables but in (related) objects. All things id are handled by Doctrine so you join two objects and needn't care about foreign keys.

The relations would be defined like this:

Code: Select all

/** @Entity **/
class Product
{
    /**
     * @ManyToOne(targetEntity="Feature", inversedBy="product")
     **/
    private $feature;
    /**
     * @ManyToOne(targetEntity="Bar", inversedBy="product")
     **/
    private $bar;
    // ...
}

/** @Entity **/
class Feature
{
    /**
     * @OneToMany(targetEntity="Product", mappedBy="feature")
     **/
    private $products;
    // ...
}

/** @Entity **/
class Bar
{
    /**
     * @OneToMany(targetEntity="Product", mappedBy="bar")
     **/
    private $products;
    // ...
}


Is that what you are trying to achieve? If not feel free to post your entity relations in full.

[work77]

...stop thinking in tables but in (related) objects. All things id are handled by Doctrine so you join two objects and needn't care about foreign keys.

I understand and have heard others giving the same advice. But the objects are linked to tables and tables to sql. The only way I can begin to wrap my tiny brain around doctrine's potential is to first match it to what I know - sql.
In the past, to join two columns, I've never had to concern myself with what TYPE of relationship it is. I just joined them like so.

"SELECT * FROM Astroid a JOIN Furniture f ON a.fruit=f.shoe"

So now first I have to learn how to even determine the relationship type between two columns. In the query above, when each is a foreign key of some other remote tables (e.g. Food, Footwear), would that always be considered a 1:1?

[dreipunktnull]

would that always be considered a 1:1?

You can't tell really. Again, even if it is hard, forget about SQL when working with Doctrine. At the end it is Object-Relational-Mapping so you should try to phrase the relations of your objects like 'a foo can have many bars' (o2m, which in turn means that multiple bars are related to one foo (and only to this particular one)) or 'multiple foos are related to multiple bars' (m2m). The Doctrine docs are quite well written IMHO and should help you here a lot.