Querying Entity with self referencing relationship - doctrine-user

The old Google Groups will be going away soon.

« Groups Home

doctrine-user

Home

Discussions

+ new post

About this group

Join this group

Querying Entity with self referencing relationship

Options

13 messages - Collapse all

sh.dime

View profile

More options Feb 7, 2:12 am

From: "sh.dime" <sharanov.dmi...@gmail.com>

Date: Mon, 6 Feb 2012 23:12:00 -0800 (PST)

Subject: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

Let's assume I have an entity with self referencing relationship.
For example:
/**
* @ORM\Table(name="categories")
* @ORM\Entity
*
*/
class Category
{
/**
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue
*/
protected $id;

/**
*
* @ORM\Column(name="title", type="string", nullable=false)
*/
protected $title;

/**
*
* @ORM\ManyToOne(targetEntity="Category")
* @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
*/
protected $parent;

// ....

}

Let's take a look for a sample query for retrieving Entity from DB

$query = $em->createQuery("
SELECT c
FROM Category c
WHERE c.id = 793
");

So, invoking "getResult" sends two queries to the Database (MySQL).:
$result = $query->getResult();

1. Querying the Category entity from DB
SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

2. Second query select for a parent
SELECT t0.id AS id1 ... FROM categories t0 WHERE t0.id = 789

I assumed that second query will be run only when accessing
"Category::parent". But it's not so.

If I invoke "getArrayResult", I see that there is only one query.
SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

But in that case array will be returned, so, to operate with object,
using getResult method will be preferred.
How to prevent sending two queries when I invoke getResult on Entity
with self referencing relationship.

sh.dime

View profile

More options Feb 8, 8:40 am

From: "sh.dime" <sharanov.dmi...@gmail.com>

Date: Wed, 8 Feb 2012 05:40:09 -0800 (PST)

Local: Wed, Feb 8 2012 8:40 am

Subject: Re: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

if join table

$query = $em->createQuery("
SELECT c, parent
FROM Category c
LEFT JOIN c.parent parent
WHERE c.id = 793
");
$result = $query->getResult();

Then doctrine sends one query. Something like this:
SELECT c1.*, c2.* FROM Categories c1 LEFT JOIN Categories c2 ON
c1.parent_id = c2.id

but I still look for a solution how to get Entity from DB with self
referencing relationship withou additional query for a "parent".
May be anybody have any sugestions?

On 7 фев, 10:12, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

- Show quoted text -

Ezequiel Rabinovich

View profile

More options Feb 8, 9:35 am

From: Ezequiel Rabinovich <erabinov...@gmail.com>

Date: Wed, 8 Feb 2012 11:35:52 -0300

Local: Wed, Feb 8 2012 9:35 am

Subject: Re: [doctrine-user] Re: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

Hey, I think it's the default, but have you tried setting fetch="LAZY"
in the ManyToOne annotation?
By the way, have you looked into nested sets? If the data is not
changing often they can be a pretty good alternative to self
referencing columns (I havent used them since doctrine 1, but I think
doctrine 2 did have support for them)

Cheers

Ezequiel Rabinovich.

Sent from my iPhone

On 08/02/2012, at 10:40, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

- Show quoted text -

> if join table

> $query = $em->createQuery("
> SELECT c, parent
> FROM Category c
> LEFT JOIN c.parent parent
> WHERE c.id = 793
> ");
> $result = $query->getResult();

> Then doctrine sends one query. Something like this:
> SELECT c1.*, c2.* FROM Categories c1 LEFT JOIN Categories c2 ON
> c1.parent_id = c2.id

> but I still look for a solution how to get Entity from DB with self
> referencing relationship withou additional query for a "parent".
> May be anybody have any sugestions?

> On 7 фев, 10:12, "sh.dime" <sharanov.dmi...@gmail.com> wrote:
>> Let's assume I have an entity with self referencing relationship.
>> For example:
>> /**
>> * @ORM\Table(name="categories")
>> * @ORM\Entity
>> *
>> */
>> class Category
>> {
>> /**
>> *
>> * @ORM\Column(name="id", type="integer", nullable=false)
>> * @ORM\Id
>> * @ORM\GeneratedValue
>> */
>> protected $id;

>> /**
>> *
>> * @ORM\Column(name="title", type="string", nullable=false)
>> */
>> protected $title;

>> /**
>> *
>> * @ORM\ManyToOne(targetEntity="Category")
>> * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
>> */
>> protected $parent;

>> // ....

>> }

>> Let's take a look for a sample query for retrieving Entity from DB

>> $query = $em->createQuery("
>> SELECT c
>> FROM Category c
>> WHERE c.id = 793
>> ");

>> So, invoking "getResult" sends two queries to the Database (MySQL).:
>> $result = $query->getResult();

>> 1. Querying the Category entity from DB
>> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

>> 2. Second query select for a parent
>> SELECT t0.id AS id1 ... FROM categories t0 WHERE t0.id = 789

>> I assumed that second query will be run only when accessing
>> "Category::parent". But it's not so.

>> If I invoke "getArrayResult", I see that there is only one query.
>> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

>> But in that case array will be returned, so, to operate with object,
>> using getResult method will be preferred.
>> How to prevent sending two queries when I invoke getResult on Entity
>> with self referencing relationship.

> --
> You received this message because you are subscribed to the Google Groups "doctrine-user" group.
> To post to this group, send email to doctrine-user@googlegroups.com.
> To unsubscribe from this group, send email to doctrine-user+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Discussion subject changed to "Get sinble entity from collection by identity" by Tridem Lists

Tridem Lists

View profile

More options Feb 8, 10:42 am

From: "Tridem Lists" <li...@tridemapps.com>

Date: Wed, 8 Feb 2012 16:42:44 +0100

Local: Wed, Feb 8 2012 10:42 am

Subject: Get sinble entity from collection by identity

Print | Individual message | Show original | Report this message | Find messages by this author

Is it possible? Let's say I have a collection of users with ids 1, 2, 3 -
Can I do something like:

$myEntity->getUsers()->get(1)?

Or do I have to use the find method each time?
Wouldn't this cause unwanted queries when my users have already been loaded?

Ezequiel Rabinovich

View profile

More options Feb 8, 10:47 am

From: Ezequiel Rabinovich <erabinov...@gmail.com>

Date: Wed, 8 Feb 2012 12:47:09 -0300

Local: Wed, Feb 8 2012 10:47 am

Subject: Re: [doctrine-user] Get sinble entity from collection by identity

Print | Individual message | Show original | Report this message | Find messages by this author

You can use the indexBy parameter in the annotation to define a field from
the related object that's going to be used as the collection key.

Cheers.

- Show quoted text -

Jasper N. Brouwer

View profile

More options Feb 8, 10:56 am

From: "Jasper N. Brouwer" <jas...@nerdsweide.nl>

Date: Wed, 8 Feb 2012 16:56:01 +0100

Local: Wed, Feb 8 2012 10:56 am

Subject: Re: [doctrine-user] Get sinble entity from collection by identity

Print | Individual message | Show original | Report this message | Find messages by this author

Note that you should only use the indexBy parameter on columns that are unique.
Placing it on non-unique columns will result in an incomplete collection.

--
Jasper N. Brouwer

On 08-02-2012, at 16:47, Ezequiel Rabinovich wrote:

- Show quoted text -

Discussion subject changed to "Querying Entity with self referencing relationship" by sh.dime

sh.dime

View profile

More options Feb 9, 2:48 am

From: "sh.dime" <sharanov.dmi...@gmail.com>

Date: Wed, 8 Feb 2012 23:48:56 -0800 (PST)

Local: Thurs, Feb 9 2012 2:48 am

Subject: Re: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

Thank for your reply, Ezequiel.
I figured out that it happens when single table inheritance is set. So
I find it behaviour very strange.

Here's a sample I've experimented with:

/**
*
* @ORM\Table(name="test_category")
* @ORM\Entity
* @ORM\InheritanceType("SINGLE_TABLE")
* @ORM\DiscriminatorColumn(name="type", type="integer")
* @ORM\DiscriminatorMap({"1" = "TestCat1"})
*/
class TestCategory
{
/**
* @var bigint $id
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;

/**
* @var string $name
*
* @ORM\Column(name="title", type="string", length=255,
nullable=true)
*/
private $title;

/**
*
* @ORM\ManyToOne(targetEntity="TestCategory", fetch="LAZY")
* @ORM\JoinColumn(name="parent", referencedColumnName="id")
*/
protected $parent;

// ...

}

/**
*
* @ORM\Entity
*
*/
class TestCat1 extends TestCategory {

}

When I perform a query:

$query = $em->createQuery("
SELECT tr
FROM TestCat1 tr
WHERE tr.id = 6
");
$qr = $query->getResult();

then Doctrine sends several queries to the database for retrieving all
parent tree of an entity.

If I remove inheritance annotation attributes.

/**
*
* @ORM\Table(name="test_category")
* @ORM\Entity
*/
class TestCategory
{
// ...

}

And after executing

$query = $em->createQuery("
SELECT tr
FROM TestCategory tr
WHERE tr.id = 6
");
$qr = $query->getResult();

Doctrine sends only one query to the DB, as expected.

So, How do I get rid of the additional queries in the case when single
table inheritance mapping is used?

On 8 фев, 17:35, Ezequiel Rabinovich <erabinov...@gmail.com> wrote:

- Show quoted text -

> Hey, I think it's the default, but have you tried setting fetch="LAZY"
> in the ManyToOne annotation?
> By the way, have you looked into nested sets? If the data is not
> changing often they can be a pretty good alternative to self
> referencing columns (I havent used them since doctrine 1, but I think
> doctrine 2 did have support for them)

> Cheers

> Ezequiel Rabinovich.

> Sent from my iPhone

> On 08/02/2012, at 10:40, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

> > if join table

> > $query = $em->createQuery("
> > SELECT c, parent
> > FROM Category c
> > LEFT JOIN c.parent parent
> > WHERE c.id = 793
> > ");
> > $result = $query->getResult();

> > Then doctrine sends one query. Something like this:
> > SELECT c1.*, c2.* FROM Categories c1 LEFT JOIN Categories c2 ON
> > c1.parent_id = c2.id

> > but I still look for a solution how to get Entity from DB with self
> > referencing relationship withou additional query for a "parent".
> > May be anybody have any sugestions?

> > On 7 фев, 10:12, "sh.dime" <sharanov.dmi...@gmail.com> wrote:
> >> Let's assume I have an entity with self referencing relationship.
> >> For example:
> >> /**
> >> * @ORM\Table(name="categories")
> >> * @ORM\Entity
> >> *
> >> */
> >> class Category
> >> {
> >> /**
> >> *
> >> * @ORM\Column(name="id", type="integer", nullable=false)
> >> * @ORM\Id
> >> * @ORM\GeneratedValue
> >> */
> >> protected $id;

> >> /**
> >> *
> >> * @ORM\Column(name="title", type="string", nullable=false)
> >> */
> >> protected $title;

> >> /**
> >> *
> >> * @ORM\ManyToOne(targetEntity="Category")
> >> * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
> >> */
> >> protected $parent;

> >> // ....

> >> }

> >> Let's take a look for a sample query for retrieving Entity from DB

> >> $query = $em->createQuery("
> >> SELECT c
> >> FROM Category c
> >> WHERE c.id = 793
> >> ");

> >> So, invoking "getResult" sends two queries to the Database (MySQL).:
> >> $result = $query->getResult();

> >> 1. Querying the Category entity from DB
> >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

> >> 2. Second query select for a parent
> >> SELECT t0.id AS id1 ... FROM categories t0 WHERE t0.id = 789

> >> I assumed that second query will be run only when accessing
> >> "Category::parent". But it's not so.

> >> If I invoke "getArrayResult", I see that there is only one query.
> >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

> >> But in that case array will be returned, so, to operate with object,
> >> using getResult method will be preferred.
> >> How to prevent sending two queries when I invoke getResult on Entity
> >> with self referencing relationship.

> > --
> > You received this message because you are subscribed to the Google Groups "doctrine-user" group.
> > To post to this group, send email to doctrine-user@googlegroups.com.
> > To unsubscribe from this group, send email to doctrine-user+unsubscribe@googlegroups.com.
> > For more options, visit this group athttp://groups.google.com/group/doctrine-user?hl=en.

Menno Holtkamp

View profile

More options Feb 9, 5:22 am

From: Menno Holtkamp <menno.holtk...@gmail.com>

Date: Thu, 9 Feb 2012 08:22:53 -0200

Local: Thurs, Feb 9 2012 5:22 am

Subject: Re: [doctrine-user] Re: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

I don't think your testcase really represents your initial question. In
case you define the inheritance on ORM level, the Entities are not the same
anymore, so this is no longer a self-reference...

Still, your initial question is an interesting one, will also run some
tests.
On Feb 9, 2012 5:48 AM, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

- Show quoted text -

> Thank for your reply, Ezequiel.
> I figured out that it happens when single table inheritance is set. So
> I find it behaviour very strange.

> Here's a sample I've experimented with:

> /**
> *
> * @ORM\Table(name="test_category")
> * @ORM\Entity
> * @ORM\InheritanceType("SINGLE_TABLE")
> * @ORM\DiscriminatorColumn(name="type", type="integer")
> * @ORM\DiscriminatorMap({"1" = "TestCat1"})
> */
> class TestCategory
> {
> /**
> * @var bigint $id
> *
> * @ORM\Column(name="id", type="integer", nullable=false)
> * @ORM\Id
> * @ORM\GeneratedValue(strategy="IDENTITY")
> */
> private $id;

> /**
> * @var string $name
> *
> * @ORM\Column(name="title", type="string", length=255,
> nullable=true)
> */
> private $title;

> /**
> *
> * @ORM\ManyToOne(targetEntity="TestCategory", fetch="LAZY")
> * @ORM\JoinColumn(name="parent", referencedColumnName="id")
> */
> protected $parent;

> // ...
> }

> /**
> *
> * @ORM\Entity
> *
> */
> class TestCat1 extends TestCategory {

> }

> When I perform a query:

> $query = $em->createQuery("
> SELECT tr
> FROM TestCat1 tr
> WHERE tr.id = 6
> ");
> $qr = $query->getResult();

> then Doctrine sends several queries to the database for retrieving all
> parent tree of an entity.

> If I remove inheritance annotation attributes.

> /**
> *
> * @ORM\Table(name="test_category")
> * @ORM\Entity
> */
> class TestCategory
> {
> // ...
> }

> And after executing

> $query = $em->createQuery("
> SELECT tr
> FROM TestCategory tr
> WHERE tr.id = 6
> ");
> $qr = $query->getResult();

> Doctrine sends only one query to the DB, as expected.

> So, How do I get rid of the additional queries in the case when single
> table inheritance mapping is used?

> On 8 фев, 17:35, Ezequiel Rabinovich <erabinov...@gmail.com> wrote:
> > Hey, I think it's the default, but have you tried setting fetch="LAZY"
> > in the ManyToOne annotation?
> > By the way, have you looked into nested sets? If the data is not
> > changing often they can be a pretty good alternative to self
> > referencing columns (I havent used them since doctrine 1, but I think
> > doctrine 2 did have support for them)

> > Cheers

> > Ezequiel Rabinovich.

> > Sent from my iPhone

> > On 08/02/2012, at 10:40, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

> > > if join table

> > > $query = $em->createQuery("
> > > SELECT c, parent
> > > FROM Category c
> > > LEFT JOIN c.parent parent
> > > WHERE c.id = 793
> > > ");
> > > $result = $query->getResult();

> > > Then doctrine sends one query. Something like this:
> > > SELECT c1.*, c2.* FROM Categories c1 LEFT JOIN Categories c2 ON
> > > c1.parent_id = c2.id

> > > but I still look for a solution how to get Entity from DB with self
> > > referencing relationship withou additional query for a "parent".
> > > May be anybody have any sugestions?

> > > On 7 фев, 10:12, "sh.dime" <sharanov.dmi...@gmail.com> wrote:
> > >> Let's assume I have an entity with self referencing relationship.
> > >> For example:
> > >> /**
> > >> * @ORM\Table(name="categories")
> > >> * @ORM\Entity
> > >> *
> > >> */
> > >> class Category
> > >> {
> > >> /**
> > >> *
> > >> * @ORM\Column(name="id", type="integer", nullable=false)
> > >> * @ORM\Id
> > >> * @ORM\GeneratedValue
> > >> */
> > >> protected $id;

> > >> /**
> > >> *
> > >> * @ORM\Column(name="title", type="string", nullable=false)
> > >> */
> > >> protected $title;

> > >> /**
> > >> *
> > >> * @ORM\ManyToOne(targetEntity="Category")
> > >> * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
> > >> */
> > >> protected $parent;

> > >> // ....

> > >> }

> > >> Let's take a look for a sample query for retrieving Entity from DB

> > >> $query = $em->createQuery("
> > >> SELECT c
> > >> FROM Category c
> > >> WHERE c.id = 793
> > >> ");

> > >> So, invoking "getResult" sends two queries to the Database (MySQL).:
> > >> $result = $query->getResult();

> > >> 1. Querying the Category entity from DB
> > >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

> > >> 2. Second query select for a parent
> > >> SELECT t0.id AS id1 ... FROM categories t0 WHERE t0.id = 789

> > >> I assumed that second query will be run only when accessing
> > >> "Category::parent". But it's not so.

> > >> If I invoke "getArrayResult", I see that there is only one query.
> > >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

> > >> But in that case array will be returned, so, to operate with object,
> > >> using getResult method will be preferred.
> > >> How to prevent sending two queries when I invoke getResult on Entity
> > >> with self referencing relationship.

> > > --
> > > You received this message because you are subscribed to the Google
> Groups "doctrine-user" group.
> > > To post to this group, send email to doctrine-user@googlegroups.com.
> > > To unsubscribe from this group, send email to
> doctrine-user+unsubscribe@googlegroups.com.
> > > For more options, visit this group athttp://
> groups.google.com/group/doctrine-user?hl=en.

> --
> You received this message because you are subscribed to the Google Groups
> "doctrine-user" group.
> To post to this group, send email to doctrine-user@googlegroups.com.
> To unsubscribe from this group, send email to
> doctrine-user+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/doctrine-user?hl=en.

Menno Holtkamp

View profile

More options Feb 9, 6:10 am

From: Menno Holtkamp <menno.holtk...@gmail.com>

Date: Thu, 9 Feb 2012 09:10:58 -0200

Local: Thurs, Feb 9 2012 6:10 am

Subject: Re: [doctrine-user] Re: Querying Entity with self referencing relationship

Print | Individual message | Show original | Report this message | Find messages by this author

I have the following configuration in place:

- Entity CategoryAbstract, which is extend by two Entities using Class
Table Inheritance (JOINED):
- Entity ProductCategory
- Entity SupplierCategory

At the 'child' Entities, both a 'parentCategory' and a 'subCategories'
property are defined, which have the annotations that realize a
self-reference association.

When using
$entityManager->find('Entity\Category\ProductCategory', $id);

This generates the following SQL:

SELECT t1.id AS id2, t1.description AS description3, t1.name AS name4,
t0.parent_category_id AS parent_category_id5, t1.discriminator
FROM category_product_type t0
INNER JOIN category t1 ON t0.id = t1.id
WHERE t1.id = ?

Which seems correct to me, so maybe you should prevent typing DQL and have
the EntityManager resolve it for you?

Cheers

PS: the reason I chose using Class Table Inheritance is because the
ProductCategory and SupplierCategory have different characteristics /
associations with other Entities

On 9 February 2012 08:22, Menno Holtkamp <menno.holtk...@gmail.com> wrote:

- Show quoted text -

> I don't think your testcase really represents your initial question. In
> case you define the inheritance on ORM level, the Entities are not the same
> anymore, so this is no longer a self-reference...

> Still, your initial question is an interesting one, will also run some
> tests.
> On Feb 9, 2012 5:48 AM, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

>> Thank for your reply, Ezequiel.
>> I figured out that it happens when single table inheritance is set. So
>> I find it behaviour very strange.

>> Here's a sample I've experimented with:

>> /**
>> *
>> * @ORM\Table(name="test_category")
>> * @ORM\Entity
>> * @ORM\InheritanceType("SINGLE_TABLE")
>> * @ORM\DiscriminatorColumn(name="type", type="integer")
>> * @ORM\DiscriminatorMap({"1" = "TestCat1"})
>> */
>> class TestCategory
>> {
>> /**
>> * @var bigint $id
>> *
>> * @ORM\Column(name="id", type="integer", nullable=false)
>> * @ORM\Id
>> * @ORM\GeneratedValue(strategy="IDENTITY")
>> */
>> private $id;

>> /**
>> * @var string $name
>> *
>> * @ORM\Column(name="title", type="string", length=255,
>> nullable=true)
>> */
>> private $title;

>> /**
>> *
>> * @ORM\ManyToOne(targetEntity="TestCategory", fetch="LAZY")
>> * @ORM\JoinColumn(name="parent", referencedColumnName="id")
>> */
>> protected $parent;

>> // ...
>> }

>> /**
>> *
>> * @ORM\Entity
>> *
>> */
>> class TestCat1 extends TestCategory {

>> }

>> When I perform a query:

>> $query = $em->createQuery("
>> SELECT tr
>> FROM TestCat1 tr
>> WHERE tr.id = 6
>> ");
>> $qr = $query->getResult();

>> then Doctrine sends several queries to the database for retrieving all
>> parent tree of an entity.

>> If I remove inheritance annotation attributes.

>> /**
>> *
>> * @ORM\Table(name="test_category")
>> * @ORM\Entity
>> */
>> class TestCategory
>> {
>> // ...
>> }

>> And after executing

>> $query = $em->createQuery("
>> SELECT tr
>> FROM TestCategory tr
>> WHERE tr.id = 6
>> ");
>> $qr = $query->getResult();

>> Doctrine sends only one query to the DB, as expected.

>> So, How do I get rid of the additional queries in the case when single
>> table inheritance mapping is used?

>> On 8 фев, 17:35, Ezequiel Rabinovich <erabinov...@gmail.com> wrote:
>> > Hey, I think it's the default, but have you tried setting fetch="LAZY"
>> > in the ManyToOne annotation?
>> > By the way, have you looked into nested sets? If the data is not
>> > changing often they can be a pretty good alternative to self
>> > referencing columns (I havent used them since doctrine 1, but I think
>> > doctrine 2 did have support for them)

>> > Cheers

>> > Ezequiel Rabinovich.

>> > Sent from my iPhone

>> > On 08/02/2012, at 10:40, "sh.dime" <sharanov.dmi...@gmail.com> wrote:

>> > > if join table

>> > > $query = $em->createQuery("
>> > > SELECT c, parent
>> > > FROM Category c
>> > > LEFT JOIN c.parent parent
>> > > WHERE c.id = 793
>> > > ");
>> > > $result = $query->getResult();

>> > > Then doctrine sends one query. Something like this:
>> > > SELECT c1.*, c2.* FROM Categories c1 LEFT JOIN Categories c2 ON
>> > > c1.parent_id = c2.id

>> > > but I still look for a solution how to get Entity from DB with self
>> > > referencing relationship withou additional query for a "parent".
>> > > May be anybody have any sugestions?

>> > > On 7 фев, 10:12, "sh.dime" <sharanov.dmi...@gmail.com> wrote:
>> > >> Let's assume I have an entity with self referencing relationship.
>> > >> For example:
>> > >> /**
>> > >> * @ORM\Table(name="categories")
>> > >> * @ORM\Entity
>> > >> *
>> > >> */
>> > >> class Category
>> > >> {
>> > >> /**
>> > >> *
>> > >> * @ORM\Column(name="id", type="integer", nullable=false)
>> > >> * @ORM\Id
>> > >> * @ORM\GeneratedValue
>> > >> */
>> > >> protected $id;

>> > >> /**
>> > >> *
>> > >> * @ORM\Column(name="title", type="string", nullable=false)
>> > >> */
>> > >> protected $title;

>> > >> /**
>> > >> *
>> > >> * @ORM\ManyToOne(targetEntity="Category")
>> > >> * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
>> > >> */
>> > >> protected $parent;

>> > >> // ....

>> > >> }

>> > >> Let's take a look for a sample query for retrieving Entity from DB

>> > >> $query = $em->createQuery("
>> > >> SELECT c
>> > >> FROM Category c
>> > >> WHERE c.id = 793
>> > >> ");

>> > >> So, invoking "getResult" sends two queries to the Database (MySQL).:
>> > >> $result = $query->getResult();

>> > >> 1. Querying the Category entity from DB
>> > >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

>> > >> 2. Second query select for a parent
>> > >> SELECT t0.id AS id1 ... FROM categories t0 WHERE t0.id = 789

>> > >> I assumed that second query will be run only when accessing
>> > >> "Category::parent". But it's not so.

>> > >> If I invoke "getArrayResult", I see that there is only one query.
>> > >> SELECT r0_.id AS id0 ... FROM categories r0_ WHERE (r0_.id = 793)

>> > >> But in that case array will be returned, so, to operate with object,
>> > >> using getResult method will be preferred.
>> > >> How to prevent sending two queries when I invoke getResult on Entity
>> > >> with self referencing relationship.

>> > > --
>> > > You received this message because you are subscribed to the Google
>> Groups "doctrine-user" group.
>> > > To post to this group, send email to doctrine-user@googlegroups.com.
>> > > To unsubscribe from this group, send email to
>> doctrine-user+unsubscribe@googlegroups.com.
>> > > For more options, visit this group athttp://
>> groups.google.com/group/doctrine-user?hl=en.

>> --
>> You received this message because you are subscribed to the Google Groups
>> "doctrine-user" group.
>> To post to this group, send email to doctrine-user@googlegroups.com.
>> To unsubscribe from this group, send email to
>> doctrine-user+unsubscribe@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/doctrine-user?hl=en.

Discussion subject changed to "how to update sub-entities (join table)" by Tridem Lists

Tridem Lists

View profile

More options Feb 9, 7:36 am

From: "Tridem Lists" <li...@tridemapps.com>

Date: Thu, 9 Feb 2012 13:36:02 +0100

Local: Thurs, Feb 9 2012 7:36 am

Subject: how to update sub-entities (join table)

Print | Individual message | Show original | Report this message | Find messages by this author

My Profile entity has an arrayCollection of Advertisement entities already existing in my database.

Now I have to update or create a new Advertisement and add it to my Profile entity.

My code so far:

…

$currentAdvertisements = $profile->getAdvertisements();

foreach ($newAdvertisements as $newAdvertisementsId) {

$advertisement = isset($currentAdvertisements [$newAdvertisementsId])

? $currentAdvertisements [$newAdvertisementsId]

: new Entities\Advertisement;

$advertisement->setFoo(‘bar’);

$em->persist($advertisement);

$profile->addAdvertisement($advertisement);

}

$em->persist($profile);

try {

$em->flush($profile);

} catch (Exception $e) {

// …

}

Advertisements are indexed by their id (indexBy annotation) and the addAdvertisement method looks like this:

public function addAdvertisement(Advertisement $advertisement)

{

if (!$this->advertisement->contains($advertisement)) {

$this->advertisements[] = $advertisement;

$advertisement->setProfile($this);

}

What is the correct way of persisting and adding advertisements to make them get updated when flushing the profile entity?

Tridem Lists

View profile

More options Feb 10, 7:12 am

From: "Tridem Lists" <li...@tridemapps.com>

Date: Fri, 10 Feb 2012 13:12:55 +0100

Local: Fri, Feb 10 2012 7:12 am

Subject: AW: how to update sub-entities (join table)

Print | Individual message | Show original | Report this message | Find messages by this author

Actually my code seems to be right.

Two things had to be changed to get it working:

1: Method order:

// Add entity first. This way it is mapped to the profile. If the entity already exists it is not added but updated.

$profile->addAdvertisement($advertisement);

// Then persist.

$em->persist($advertisement);

2: Flush

$em->flush();

For some reason I used $em->flush($profile);

The method description says:

* If an entity is explicitly passed to this method only this entity and

* the cascade-persist semantics + scheduled inserts/removals are synchronized.

Can somebody explain why updating the advertisements is not included then when passing the $profile entity?

Von: Tridem Lists [mailto:li...@tridemapps.com]
Gesendet: Donnerstag, 9. Februar 2012 13:36
An: <mailto:doctrine-user@googlegroups.com> doctrine-user@googlegroups.com
Betreff: how to update sub-entities (join table)

My Profile entity has an arrayCollection of Advertisement entities already existing in my database.

Now I have to update or create a new Advertisement and add it to my Profile entity.

My code so far:

…

$currentAdvertisements = $profile->getAdvertisements();

foreach ($newAdvertisements as $newAdvertisementsId) {

$advertisement = isset($currentAdvertisements [$newAdvertisementsId])

? $currentAdvertisements [$newAdvertisementsId]

: new Entities\Advertisement;

$advertisement->setFoo(‘bar’);

$em->persist($advertisement);

$profile->addAdvertisement($advertisement);

}

$em->persist($profile);

try {

$em->flush($profile);

} catch (Exception $e) {

// …

}

Advertisements are indexed by their id (indexBy annotation) and the addAdvertisement method looks like this:

public function addAdvertisement(Advertisement $advertisement)

{

if (!$this->advertisement->contains($advertisement)) {

$this->advertisements[] = $advertisement;

$advertisement->setProfile($this);

}

What is the correct way of persisting and adding advertisements to make them get updated when flushing the profile entity?

Discussion subject changed to "Get sinble entity from collection by identity" by Tridem Lists

Tridem Lists

View profile

More options Feb 10, 8:54 am

From: "Tridem Lists" <li...@tridemapps.com>

Date: Fri, 10 Feb 2012 14:54:11 +0100

Local: Fri, Feb 10 2012 8:54 am

Subject: AW: [doctrine-user] Get sinble entity from collection by identity

Print | Individual message | Show original | Report this message | Find messages by this author

You mean entity-level uniqueness, right?

See:
http://groups.google.com/group/doctrine-dev/browse_thread/thread/07e3...
ca58e

Is it possible to use indexBy on an OneToOne entity (f.e. user using the id
of the user) instead of an entity column?

-----Ursprüngliche Nachricht-----
Von: doctrine-user@googlegroups.com [mailto:doctrine-user@googlegroups.com]
Im Auftrag von Jasper N. Brouwer
Gesendet: Mittwoch, 8. Februar 2012 16:56
An: doctrine-user@googlegroups.com
Betreff: Re: [doctrine-user] Get sinble entity from collection by identity

Note that you should only use the indexBy parameter on columns that are
unique.
Placing it on non-unique columns will result in an incomplete collection.

--
Jasper N. Brouwer

On 08-02-2012, at 16:47, Ezequiel Rabinovich wrote:

> You can use the indexBy parameter in the annotation to define a field from

the related object that's going to be used as the collection key.

> Cheers.

--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To post to this group, send email to doctrine-user@googlegroups.com.
To unsubscribe from this group, send email to
doctrine-user+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/doctrine-user?hl=en.

Benjamin Eberlei

View profile

More options Feb 10, 9:23 am

From: Benjamin Eberlei <kont...@beberlei.de>

Date: Fri, 10 Feb 2012 15:23:42 +0100

Local: Fri, Feb 10 2012 9:23 am

Subject: Re: [doctrine-user] Get sinble entity from collection by identity

Print | Individual message | Show original | Report this message | Find messages by this author

That is someting different, you can do that with:

/** @Id @OneToOne */ as the primary key. However i really not recommend
this for onetoone, especially not if you make it bidirectional, because
then the "inverse" side has to always fetch the association.

- Show quoted text -

End of messages

« Back to Discussions

« Newer topic

Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy

Account Options