Join with composite keys #5409

New Issue

Closed

opened 2026-01-22 15:07:02 +01:00 by admin · 2 comments

admin commented 2026-01-22 15:07:02 +01:00

Owner

Copy Link

Originally created by @dautushenka on GitHub (Feb 6, 2017).

Originally assigned to: @Ocramius on GitHub.

I have EAV pattern with 4 entities:
Item.php

class Item
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var ItemAttributeValue[]|Collection
     *
     * @ORM\OneToMany(targetEntity="ItemAttributeValue", mappedBy="item", cascade={"persist", "remove"}, orphanRemoval=true)
     */
    private $attributeValues;

Attribute.php

class Attribute
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var AttributeValue[]|Collection
     *
     * @ORM\OneToMany(targetEntity="AttributeValue", mappedBy="attribute", fetch="EAGER", orphanRemoval=true, cascade={"persist", "remove"})
     */
    private $values;

AttributeValue.php

class AttributeValue
{
    /**
     * @var Attribute
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\ManyToOne(targetEntity="Attribute", inversedBy="values")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="attribute_id", referencedColumnName="id", nullable=false)
     * })
     */
    private $attribute;

    /**
     * @var string
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\Column(name="key", type="string", length=10, nullable=false)
     */
    private $key;

ItemAttributeValue.php

class ItemAttributeValue
{
    /**
     * @var AttributeValue
     *
     * @ORM\ManyToOne(targetEntity="AttributeValue")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="attribute_value", referencedColumnName="key"),
     *   @ORM\JoinColumn(name="attribute_id", referencedColumnName="attribute_id")
     * })
     */
    private $attributeValue;

    /**
     * @var Item
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\ManyToOne(targetEntity="Item", inversedBy="attributeValues")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="item_id", referencedColumnName="id")
     * })
     */
    private $item;

    /**
     * @var Attribute
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     * @ORM\ManyToOne(targetEntity="Attribute")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="attribute_id", referencedColumnName="id")
     * })
     */
    private $attribute;

As you can see entity "ItemAttributeValue" has composite foreign key "attributeValue", now I am trying to find some items with certain attribute values:

$qb = $this->entityManager->createQueryBuilder();
$qb->select('i')
    ->from(Item::class, 'i')
    ->setMaxResults(20)
    ->join(
        ItemAttributeValue::class,
        'ia',
        Join::WITH,
        "ia.item=i"
    )
    ->where("ia.attributeValue = ?1")
    ->setParameter(1, [
        'attribute_id'      => 1,
        'attribute_value'   => 'value'
    ])
    ;

And I am getting following error:
A single-valued association path expression to an entity with a composite primary key is not supported. Explicitly name the components of the composite primary key in the query.
As result I'd like to get query like that:

SELECT i.* FROM item i 
INNER JOIN item_attribute_value ia ON ia.item_id=i.id
WHERE ia.attribute_id=1 AND ia.attribute_value='value'

I don't want to join "AttributeValue" at all.

Used versions:

• doctrine/orm v2.5.6
• doctrine/dbal v2.5.10
• doctrine/collections v1.4.0
• doctrine/common v2.7.2

Thank you for the answers in advance.

Link: http://stackoverflow.com/questions/42018313/doctrine-join-with-composite-keys

Originally created by @dautushenka on GitHub (Feb 6, 2017). Originally assigned to: @Ocramius on GitHub. I have EAV pattern with 4 entities: **Item.php** class Item { /** * @var integer * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var ItemAttributeValue[]|Collection * * @ORM\OneToMany(targetEntity="ItemAttributeValue", mappedBy="item", cascade={"persist", "remove"}, orphanRemoval=true) */ private $attributeValues; **Attribute.php** class Attribute { /** * @var integer * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var AttributeValue[]|Collection * * @ORM\OneToMany(targetEntity="AttributeValue", mappedBy="attribute", fetch="EAGER", orphanRemoval=true, cascade={"persist", "remove"}) */ private $values; **AttributeValue.php** class AttributeValue { /** * @var Attribute * * @ORM\Id * @ORM\GeneratedValue(strategy="NONE") * @ORM\ManyToOne(targetEntity="Attribute", inversedBy="values") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="attribute_id", referencedColumnName="id", nullable=false) * }) */ private $attribute; /** * @var string * * @ORM\Id * @ORM\GeneratedValue(strategy="NONE") * @ORM\Column(name="key", type="string", length=10, nullable=false) */ private $key; **ItemAttributeValue.php** class ItemAttributeValue { /** * @var AttributeValue * * @ORM\ManyToOne(targetEntity="AttributeValue") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="attribute_value", referencedColumnName="key"), * @ORM\JoinColumn(name="attribute_id", referencedColumnName="attribute_id") * }) */ private $attributeValue; /** * @var Item * * @ORM\Id * @ORM\GeneratedValue(strategy="NONE") * @ORM\ManyToOne(targetEntity="Item", inversedBy="attributeValues") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="item_id", referencedColumnName="id") * }) */ private $item; /** * @var Attribute * * @ORM\Id * @ORM\GeneratedValue(strategy="NONE") * @ORM\ManyToOne(targetEntity="Attribute") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="attribute_id", referencedColumnName="id") * }) */ private $attribute; As you can see entity "ItemAttributeValue" has composite foreign key "attributeValue", now I am trying to find some items with certain attribute values: $qb = $this->entityManager->createQueryBuilder(); $qb->select('i') ->from(Item::class, 'i') ->setMaxResults(20) ->join( ItemAttributeValue::class, 'ia', Join::WITH, "ia.item=i" ) ->where("ia.attributeValue = ?1") ->setParameter(1, [ 'attribute_id' => 1, 'attribute_value' => 'value' ]) ; And I am getting following error: **A single-valued association path expression to an entity with a composite primary key is not supported. Explicitly name the components of the composite primary key in the query.** As result I'd like to get query like that: SELECT i.* FROM item i INNER JOIN item_attribute_value ia ON ia.item_id=i.id WHERE ia.attribute_id=1 AND ia.attribute_value='value' I don't want to join "AttributeValue" at all. Used versions: - doctrine/orm v2.5.6 - doctrine/dbal v2.5.10 - doctrine/collections v1.4.0 - doctrine/common v2.7.2 Thank you for the answers in advance. Link: http://stackoverflow.com/questions/42018313/doctrine-join-with-composite-keys

admin added the Question label 2026-01-22 15:07:02 +01:00

admin closed this issue 2026-01-22 15:07:02 +01:00

admin commented 2026-01-22 15:07:04 +01:00

Author

Owner

Copy Link

@Ocramius commented on GitHub (Feb 7, 2017):

What could probably be attempted is:

    ->where("ia.attributeValue = :attributeValue")
    ->setParameter(
        'attributeValue',
        $entityManager->getReference(
            AttributeValue::class,
            [
                'attribute_id'      => 1,
                'attribute_value'   => 'value'
            ]
        )
    );

Another approach is to combine it with the IDENTITY() function (can't remember if it works with composite PKs).

Another approach is to just join the association (not a big issue, if your indexes are in place).

@Ocramius commented on GitHub (Feb 7, 2017): What could probably be attempted is: ```php ->where("ia.attributeValue = :attributeValue") ->setParameter( 'attributeValue', $entityManager->getReference( AttributeValue::class, [ 'attribute_id' => 1, 'attribute_value' => 'value' ] ) ); ``` Another approach is to combine it with the `IDENTITY()` function (can't remember if it works with composite PKs). Another approach is to just join the association (not a big issue, if your indexes are in place).

admin commented 2026-01-22 15:07:04 +01:00

Author

Owner

Copy Link

@Ocramius commented on GitHub (Feb 7, 2017):

Closing, since this is a question about a well known limitation (exception is there for a reason), and not an issue.

@Ocramius commented on GitHub (Feb 7, 2017): Closing, since this is a question about a well known limitation (exception is there for a reason), and not an issue.

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Question

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5409