Mapping the same property to multiple entities in doctrine

New topics about Symfony 2 should go here

Moderators: tiagojsag, dcobalt

Mapping the same property to multiple entities in doctrine

Postby work77 » Sun Jan 20, 2013 10:10 am

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;
    // ...
}
work77
Junior Member
 
Posts: 28
Joined: Mon Aug 13, 2012 7:04 am

Re: Mapping the same property to multiple entities in doctri

Postby bjo3rn » Sun Jan 20, 2013 4:08 pm

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.
User avatar
bjo3rn
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: Mapping the same property to multiple entities in doctri

Postby work77 » Mon Jan 21, 2013 11:11 am

dreipunktnull wrote: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
work77
Junior Member
 
Posts: 28
Joined: Mon Aug 13, 2012 7:04 am

Re: Mapping the same property to multiple entities in doctri

Postby bjo3rn » Mon Jan 21, 2013 12:54 pm

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
User avatar
bjo3rn
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: Mapping the same property to multiple entities in doctri

Postby work77 » Tue Jan 22, 2013 8:08 am

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.
work77
Junior Member
 
Posts: 28
Joined: Mon Aug 13, 2012 7:04 am

Re: Mapping the same property to multiple entities in doctri

Postby bjo3rn » Tue Jan 22, 2013 10:38 am

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.
User avatar
bjo3rn
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: Mapping the same property to multiple entities in doctri

Postby work77 » Wed Jan 23, 2013 1:55 am

dreipunktnull wrote:...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?
work77
Junior Member
 
Posts: 28
Joined: Mon Aug 13, 2012 7:04 am

Re: Mapping the same property to multiple entities in doctri

Postby bjo3rn » Wed Jan 23, 2013 12:08 pm

work77 wrote: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.
User avatar
bjo3rn
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany


Return to General Symfony 2 discussion

Who is online

Users browsing this forum: No registered users and 4 guests