CODEWITHSUNDEEP
phpsymfonydoctrine-orm
DevDonkey
DevDonkey

Reputation: 4880

Symfony2/Doctrine joined query

I need to create a simple query that produces a result set of a database entry plus the username of the person that posted it.

I've tried to setup the associations properly but I'm not sure if that's right either. I'm finding the whole idea of using these small string identifiers quite confusing. Surely there must be a simpler way of doing a join?

My two entities:

class Users
{
  // ...

  /**
    * @ORM\Column(type="string")
    * @ORM\OneToMany(targetEntity="Titles", mappedBy="addedBy")
    */
    protected $username;

   // ..
}

and

class Titles
{
    // ....

    /**
      * @ORM\Column(type="string")
      * @ORM\ManyToOne(targetEntity="Users", inversedBy="username")
     */
     protected $addedBy;

    // ....
}

with the following in the controller:

$titles = $em->createQueryBuilder()
             ->select('t.*', 'u.*')
             ->from('dvdLoggerdvdBundle:Titles', 't')
             ->leftJoin('t.addedBy', 'u')
             ->addOrderBy('t.title', 'DESC')
             ->getQuery()
             ->getResult();

I'm getting the following error:

[Semantical Error] line 0, col 69 near 'u ORDER BY t.title': Error: Class 
dvdLogger\dvdBundle\Entity\Titles has no association named addedBy `

Update 1

I made all the changes suggested by Tom and did lots of reading!

It appears that in order to overcome the lazy loading feature I need to carry out a leftJoin. I have rewritten my query as follows:

public function getAllTitles()
{
    // view all records in db
    $titles = $this->createQueryBuilder('t')
        ->select('t, u')
        ->leftJoin('t.addedBy', 'u')
        ->addOrderBy('t.title', 'DESC');

    return $titles->getQuery()->getResult();

}

I am getting a result set, but the addedBy is returning NULL when I dump the result set. As far as I'm aware shouldn't this pull the associated field in from the other table?

Upvotes: 0

Views: 103

Answers (1)

Tom Tom
Tom Tom

Reputation: 3698

Best practice is to reference the entity by its id, you are trying to reference it using the username. The inversed field should also be a specific field not an existing one that holds data. And keep it mind this field is optional and defines the associations as bidirectional, for the specified use case you don't actually need it as you are joining from the Titles entity. I would advice reading the doc here http://symfony.com/doc/current/book/doctrine.html#entity-relationships-associations as well as here http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html

Bidirectional association (w/ inversed field)

First get rid of that line:

  @ORM\Column(type="string")

In your $addedBy annotations and change inverseBy="username" to inversedBy="titles" (note the typo)

You optionaly could add

@ORM\JoinColumn(name="user_id", referencedColumnName="id")

Then in your Users Entity add

  /**
    *
    * @ORM\OneToMany(targetEntity="Titles", mappedBy="addedBy")
    */
    protected $titles;

And get rid of 

* @ORM\OneToMany(targetEntity="Titles", mappedBy="addedBy")

In your $username annotations

Last make sure you update the database schema

Then your query should return the expected result.

Unidirectional association (w/out inversed field)

Get rid of 

* @ORM\OneToMany(targetEntity="Titles", mappedBy="addedBy")

In your $username annotations

Then get rid of that line in your $addedBy annotations:

  @ORM\Column(type="string")

As well as inverseBy="username"

You optionaly could add

@ORM\JoinColumn(name="user_id", referencedColumnName="id")

Last make sure you update the database schema

Then your query should return the expected result.

Upvotes: 1

Related Questions