TechTreeDev TechTreeDev - 5 months ago 39
PHP Question

Symfony2: Join for find() results

I'm new to Doctrine with Symfony and want to rewrite an old app of mine. For one view I need everything from one table, where I'd nomally use find(). But I also have to access anoter value via join.

Is it possible, to combine the Query Builder with the find() method?

This is my current Action:

$em = $this->getDoctrine()->getManager();
$rep = $em->getRepository('PaulInkBundle:Artist');
$artist = $rep->find($id);
$styles = $rep->getStyles($id);
return $this->render('PaulInkBundle:Artist:artist.html.twig', array(
'artist' => $artist,
'styles' => $styles
));


The getStyles() method fetches the Styles linked to the artist:

public function getStyles($id)
{
return $this->getEntityManager()
->createQuery(
'SELECT s.name
FROM PaulInkBundle:Style s
WHERE s.id IN (
SELECT st.style
FROM PaulInkBundle:ArtistStyle st
WHERE st.artist = :id ) '
)->setParameter('id',$id)
->getResult();
}


Would it be possible to do this in one function and thus having the styles within the artist tat is passed to the view?

Edit:

The associations:

Artist(nr) -> ArtistStyle(artist.nr/style.nr) <- Style (nr,name)

Thus, I want to get all Style-names with a specif artist ID

Answer Source

I assume that you have following:

MANY TO MANY Association mapping with Artist and Style

So instead of writing this:

$artist = $rep->find($id);
$styles = $rep->getStyles($id);

You could create one method like this:

/**
* @var int $id
* @return Artist|null
*/
public function getArtistWithStyles($id)
{
   return $this->createQueryBuilder('a')
       ->join('a.styles', 's')
       ->where('a.id = :id')
       ->setParameter('id', $id)
       ->getQuery()
       ->getOneOrNullResult();
}

now you can do next:

Inside your controller:

$artist = $repository->getArtistWithStyles($id);

return $this->render('PaulInkBundle:Artist:artist.html.twig', array(
    'artist'    => $artist,
));

In your view you can access your styles in simple way:

{% for style in profile.getStyles() %}
   {{ style.getName() }}
{% endfor %}

Another solution might be next:

You could add Doctrine extensions bundle and use GROUP_CONCAT function. Add it to your doctrine config.

dql:
    string_functions:
        group_concat: DoctrineExtensions\Query\Mysql\GroupConcat

and change your getArtistWithStyles method to this:

public function getArtistWithStyles($id)
{
   return $this->createQueryBuilder('a')
       ->select('a, GROUP_CONCAT(s.name SEPARATOR \'; \')')
       ->join('a.styles', 's')
       ->where('a.id = :id')
       ->setParameter('id', $id)
       ->getQuery()
       ->getOneOrNullResult();
}

you'll get the result like this:

[
  'artist' => {Object},
  'styles' => 'style1; style2; style3'
]