CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
user1483208
user1483208

Reputation: 385

Join 3 tables in Symfony2

I have problem with some query in Symfony2, I have 3 entites (they are simplified a bit):

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

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255)
     */
    protected $name;

    /** @ORM\OneToMany(targetEntity="GroupMember", mappedBy="family") */
    protected $groupMembers;


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

    /**
     * @ORM\Column(name="group_member_type", type="integer")
     */
    protected $groupMemberType;

    /**
     * @ORM\ManyToOne(targetEntity="Group", inversedBy="groupMembers")
     * @ORM\JoinColumn(name="group_id", referencedColumnName="id", nullable=false)
     */
    protected $group;

/**
 * @ORM\ManyToOne(targetEntity="Test\UserBundle\Entity\User", inversedBy="memberships")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
 */
protected $user;

USER -> standard FOSuserbundle user

I want to make query that returns All groups where current user is a group member. Could someone tell me how to do that?

Here is my try:

SELECT g FROM (group g INNER JOIN groupmember gm ON g.id=gm.group_id) INNER JOIN fos_user u ON gm.user_id = u.id WHERE u.id = 6

but it returns 

Unknown column 'g' in 'field list'

Upvotes: 1

Views: 2274

Answers (2)

sjagr
sjagr

Reputation: 16512

Using the Query Builder, assuming you've fetched the user and stored it in $userObject and your bundle name is BundleName:

$groups = $this->getDoctrine()->getManager()->createQueryBuilder()
    ->select('g')
    ->from('BundleName:Group', 'g')
    ->innerJoin('g.groupMembers', 'gm')
    ->innerJoin('gm.user', 'u')
    ->where('u = :user')
    ->setParameter('user', $userObject)
    ->getQuery()
    ->getResult();

You also need to make sure that your relational mappings are correct.

/** @ORM\OneToMany(targetEntity="GroupMember", mappedBy="family") */
protected $groupMembers;

should be

/** @ORM\OneToMany(targetEntity="GroupMember", mappedBy="group") */
protected $groupMembers;

Upvotes: 4

user1483208
user1483208

Reputation: 385

This is right query:

SELECT g.* FROM (group g INNER JOIN groupmember gm ON g.id=gm.group_id) INNER JOIN fos_user u ON gm.user_id = u.id WHERE u.id = 6

Upvotes: 0

Related Questions