Multiple join in symfony: Error: Expected =, <, <=, <>, >, >=, !=

Question

I'm trying to create and action that returns all Variantset for a given PrivateUser. It's a many-to-many relationship, so there's a connection table in between.

This is the query I'm trying to run

 $variants = $repository->createQuery('SELECT variants 
            FROM AppBundle:PrivUser user 
            JOIN AppBundle:PrivUserVariantset uv
            JOIN AppBundle:Variantset variants 
            WHERE user.iduser=:user_id')
        ->setParameter('user_id', $userid)
        ->getResult();

but it returns:

[Syntax Error] line 0, col 146: Error: Expected =, <, <=, <>, >, >=, !=, got 'variants'

If I try the very same query with a single join, getting for instance the number of Variantsets from the PrivUserVariantset table, it does work. It looks like a syntax error, but I don't manage to find it from the examples I have gathered.

Is this the right way to do this query?

Dan Mironis · Accepted Answer · 2015-08-11 10:31:09Z

1

I think better way to do this would be:

$variantsRepository = $this->getDoctrine()->getRepository('AppBundle:Variantset');
$qb = $variantsRepository->createQueryBuilder('variants'); //'variants' is just alias, it can be whatever you want
$qb->join('variants.privUsers', 'user')
   ->where('user.id = :user_id')->setParameter('user_id', $userid)
   ->getQuery()
   ->getResult();

Edit:

/** @Entity **/
class Variantset
{
    // ...

    /**
     * @ORM\ManyToMany(targetEntity="PrivUser")
     * @ORM\JoinTable(name="Variantset_privUser",
     *      joinColumns={@ORM\JoinColumn(name="iduser", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="idvariantset", referencedColumnName="id")}
     * )
     **/
    private $privUsers;

    // ...

    public function __construct() {
        $this->privUsers = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

edited Aug 11, 2015 at 10:31

answered Aug 10, 2015 at 17:41

Dan Mironis

7316 silver badges14 bronze badges

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

12 Comments

MarcSitges Over a year ago

Could you tell me what variants.privUser is actually referring to? Do I need to have a direct relation between privUser and VariantSet tables? They are connected through PrivUserVariantset, which contains the ManyToOne keys (foreign keys). Sorry if these are stupid questions, it's my first time with Doctrine and I still don't see all the SQL logic behind it.

Dan Mironis Over a year ago

@MarcSitges It's ok, we all have been there. So in line $qb->join('variants.privUser', 'user') - privUser is the property in your Variantset.php Entity, and yes, the property should have proper relation between Variantset and PrivUser entities, like you said, ManyToMany relation. here is documentation for how setting up relations between entities properly in doctrine. Also I see that you're using AppBundle:PrivUserVariantset in your query and this is neccessary, because you don't need to have that entity.

Dan Mironis Over a year ago

Also you do have AppBundle/Entity/Variantset.php and AppBundle/Entity/PrivUser.php setted up, right?

MarcSitges Over a year ago

Thanks for the answer. Did you mean AppBundle:PrivUserVariantset is unnecessary? It was automatically created by Doctrine when I imported the database schema. I'd prefer not have an ArrayCollection in PrivUser entity containing all its linked Variantsets (like shown in the examples you sent), since the amount of data in the Variantset is huge and this would generate enormous objects. Would be enough defining the relations in the annotation of both PrivUser and Variantset entities?

MarcSitges Over a year ago

I can't, haven't got enough reputation yet. I think you can also vote my question to increase mine. Thanks

|

MarcSitges · Accepted Answer · 2015-08-11 10:51:59Z

Finally I got it to work by changing the columns detailed in the JoinTable annotation: variantid should go on the first joinColumns whereas priv_userid must go on the inverseJoin

Variantset Class:

/**
     * @var ArrayCollection
     *
     * @ORM\ManyToMany(targetEntity="PrivUser")
     * @ORM\JoinTable(name="priv_user_variantset",
     *      joinColumns={@ORM\JoinColumn(name="variantset_varid", referencedColumnName="varid")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="priv_user_priv_userid", referencedColumnName="priv_userid")}
     * )
     **/
    private $privusers;

    // ...

    public function __construct() {
        $this->privUsers = new \Doctrine\Common\Collections\ArrayCollection();
    }

Controller:

$variantsRepository = $this->getDoctrine()->getRepository('AppBundle:Variantset');
        $em = $variantsRepository->createQueryBuilder('variants'); //'variants' is just alias, it can be whatever you want
        $variants = $em->join('variants.privusers', 'user')
           ->where('user.privUserid = :user_id')->setParameter('user_id', $userid)
           ->getQuery()->getResult();

I have only two entities, Varianset and PrivUser, whereas the DB has three tables, one for each entity plus priv_user_variantset, which connects both via ManyToMany relationship.

It works now for the purpose of my API, but if you have any comment, they'll be very welcomed.

Collectives™ on Stack Overflow

Multiple join in symfony: Error: Expected =, <, <=, <>, >, >=, !=

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related