CODEWITHSUNDEEP
symfonydoctrine-orm
fkoessler
fkoessler

Reputation: 7276

Query builder join on one to many relationship

I have a LastTrait object that has a many to one relationship with my user object: one user can have many different lastTrait:

YOP\YourOwnPoetBundle\Entity\LastTrait:
  type: entity
  table: null
  fields:
id:
  type: integer
  id: true
  generator:
    strategy: AUTO
traitCategoryID:
  type: integer
verseID:
  type: integer
  manyToOne:
user:
  targetEntity: User
  inversedBy: lastTrait
  joinColumn:
      name: user_id
      referencedColumnName: id

I need to retrieve the verseID knowing the userID and traitCategoryID. So I created a Repository for my LastTrait object:

<?php

namespace YOP\YourOwnPoetBundle\Repository;

use Doctrine\ORM\EntityRepository;

class LastTraitRepository extends EntityRepository {

public function findOneByTraitCategoryID($userID, $traitCategoryID)
{
    return $this->getEntityManager()
    ->createQuery('SELECT l
        FROM YOPYourOwnPoetBundle:LastTrait l
        JOIN l.user u
        WHERE l.traitCategoryID = :categoryID AND u.id = :userID')
    ->setParameters(array(
        'categoryID' => $traitCategoryID,
        'userID' => $userID,
    ))
    ->getResult();
}
}

?>

However, when I call :

$lastTrait = $this->getDoctrine()
        ->getRepository('YOPYourOwnPoetBundle:LastTrait')
        ->findOneByTraitCategoryID($user->getID(), $collector->getTraitCategory()->getID());

I always get NULL, even though I should get a LastTrait object back!

What am I doing wrong here?

Upvotes: 0

Views: 3235

Answers (3)

fkoessler
fkoessler

Reputation: 7276

The issue was that I forgot to register the repository in my entity:

YOP\YourOwnPoetBundle\Entity\LastTrait:
  repositoryClass: YOP\YourOwnPoetBundle\Repository\LastTraitRepository
  type: entity
  table: null
  fields:
id:
  type: integer
  id: true
  generator:
    strategy: AUTO
traitCategoryID:
  type: integer
verseID:
  type: integer
  manyToOne:
user:
  targetEntity: User
  inversedBy: lastTrait
  joinColumn:
      name: user_id
      referencedColumnName: id

After adding the repositoryClass line, my custom Repository class is called and I get the desired result.

However, @dhunter's answer made me realize that I didn't even need a custom Repository class. I can get the desired by simply using the regular findOneBy function from my controller:

$lastTrait = $this->getDoctrine()
  ->getRepository('YOPYourOwnPoetBundle:LastTrait')
  ->findOneBy(
      array('user' => $user->getId(), 'traitCategoryID' => $collector->getTraitCategory()->getID())
  );

Upvotes: 1

MGP
MGP

Reputation: 3031

I think you should be thinking more in entities and less in foreign keys.

Try this in your method.

public function findOneByTraitCategoryID($userID, $traitCategoryID)
{
     return $this->findOneBy(
            array(
            'traitcategoryID' => $traitCategoryID,
            'user' => $userID
            ));
}

You can call ->getVerse()->getId(); after this or return the id directly on the method (too narrow for future uses IMO).

This only works if a user only has one trait per category, otherwise you don't have enough data to get a specific trait.

Upvotes: 1

Jovan Perovic
Jovan Perovic

Reputation: 20201

Hard to tell. Try echoing either DQL or SQL before retreiving results:

$q = $this->getEntityManager()->createQuery(....);
die($q->getSql()); //or getDql();

If, for some reason, you have invalid mapping (which I don't see above) you should see it here.

Also, try wrapping method with try-catch with most general Exception catch and seeing if it catches anything...

Furthermore, what is bugging me is the fact that you get NULL back...

Upvotes: 0

Related Questions