Symfony2 entity field type query_builder innerJoin

Question

I have 3 Entities (Group, GroupCategory and GroupLanguage)

Group Table

id (pk)

id_group_category (fk)

GroupCategory Table

id (pk)

GroupCategoryLanguage Table

id (pk)

id_language (fk)

id_group_category (fk)

I have created a GroupType which takes in GroupCategory as a subform.

$builder->add('id_group_category', 'entity', array(
                   'class' => 'BazaarBundle:GroupCategory',
                   'property' => 'id',
                   'query_builder' => function(EntityRepository $a) {
                    return $a->createQueryBuilder('a')
                    ->innerJoin('BazaarBundle:GroupCategoryLanguage', 'b')
                    ->where('b.id_group_category = a.id')
                    ->orderBy('a.id', 'ASC');
                    }
                  )
                  )
            ->add('Add', 'submit');

I'm trying to innerJoin the language table so that the dropdownlist would be populated with text and not the ids of the category.

I'm quite new to Symfony2 and have already looked up to their documentation and sorry to say it was quite puzzling for me. Am I doing it right because i'm having some errors with the code.

The error message:

[Semantical Error] line 0, col 111 near 'id_group_category': Error: Class Karl\BazaarBundle\Entity\GroupCategoryLanguage has no field or association named id_group_category

GroupCategory.php

class GroupCategory
    {   
    public function __construct()
{
    $this->groupCategoryLanguage = new ArrayCollection();
}

public function __toString(){

    return $this->groupCategoryLanguage->getName();
}

/**
 * @ORM\OneToMany(targetEntity="GroupCategoryLanguage", mappedBy="idGroupCategory")
 * @ORM\JoinColumn(nullable=false,referencedColumnName="id_group_category")
 */
protected $groupCategoryLanguage;
}

GroupCategoryLanguage.php

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

/**
 * @var integer
 *
 * @ORM\Column(name="id_language", type="integer")
 */
private $idLanguage;

/**
 * @var integer
 *
 * @ORM\JoinColumn(name="id_group_category", nullable=false)
 * @ORM\ManyToOne(targetEntity="Karl\BazaarBundle\Entity\GroupCategory", inversedBy="groupCategoryLanguage")
 */
private $idGroupCategory;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=32)
 */
private $name;
}

Jona · Accepted Answer · 2015-01-17 18:24:19Z

1

i think you need to add GroupCategoryLanguage data into your query by adding:

->addSelect('b')

to your query builer object. Exemple below.

Please note i have deleted the where condition because it seems to be a join condition, adn this is not needed bacause Doctrine is suposed to know all about relations. If i'm wrong, don't delete it...

$builder->add('id_group_category', 'entity', array(
            'class'         => 'BazaarBundle:GroupCategory',
            'property'      => 'id',
            'query_builder' => function(EntityRepository $a) {
                return $a->createQueryBuilder('a')
                         ->innerJoin('a.languages', 'b')
                         ->addSelect('b')
                         ->orderBy('a.id', 'ASC');
            }
        ))
        ->add('Add', 'submit');

EDIT:

Regarding our discussions, i update my answer:.

Let's start from the beginning:

You have a relation between GroupCategory and GroupCategoryLanguage and the GroupCategoryLanguage is the owner of this relation (it have to FK). Here you want to get languages from the GroupCategory so it's $owner->getSlave() and you need a bidirectionnal relation.

For that you need to add a field into the slave entity:

So in GroupCategory entity:

    /**
     * @ORM\OneToMany(targetEntity="Karl\BazaarBundle\Entity\GroupCategoryLanguage",referencedColumnName="id_group_category", mappedBy="category")
     * @ORM\JoinColumn(nullable=false)
     */
    private $languages;

And i assume that in GroupCategoryLanguages you have:

/**
 * @ORM\ManyToOne(targetEntity="Karl\BazaarBundle\Entity\GroupCategory", inversedBy="languages")
 * @ORM\JoinColumn(name="id_group_category", nullable=false)
 */
private $category;

I think one of your problems is that you think in terms of tables, am i wrong ? You really need to think in term of objects (entities) and let Doctrine manage the boring things :)

Display language in place of id

You can totally delete the 'property' option and add a __toString method into your GroupCategory entity, which one will be called and the returned value will appear in your form.

I think we are good :)

Cheers

edited Jan 17, 2015 at 18:24

answered Jan 17, 2015 at 16:52

Jona

1,15610 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Karl Wong Over a year ago

I've got an error with the code: [Syntax Error] Error: Expected Literal, got 'BY'" , i've tried removing the orderBy but am still getting errors, is the innerJoin syntax correct? I've seen the docs and it uses the "a" in the join, eg: a.test.

Jona Over a year ago

Oh yes you're right... It should be a.yourmappedfield in place of BazaarBundle:GroupCategoryLanguage can you add your mapping for the GroupCategory entity and BazaarBundle:GroupCategoryLanguage ? In your GroupCategory entity you should have a field $languages (or another name ?) then you need to do a.languages

Karl Wong Over a year ago

I'm not sure what you mean by mapping, i've updated the question. GroupCategory table does not have any field/key that will relate to GroupCategoryLanguage table though. Is this correct?

Karl Wong Over a year ago

I've successfully mapped and generate the field out but it is still displaying the category id, how can i make it to display the inner join table's name field? I tried changing the property but it doesn't seem to be working.

Karl Wong Over a year ago

I noticed that the referencedColumnName should be in the @ORM\JoinColumn. I've updated my question on the __toString method in GroupCategory, i can't seem to link it to GroupCategoryLanguage.

|

Collectives™ on Stack Overflow

Symfony2 entity field type query_builder innerJoin

Group Table

GroupCategory Table

GroupCategoryLanguage Table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Group Table

GroupCategory Table

GroupCategoryLanguage Table

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related