Symfony2 twig print values from query on 2 non related tables

Question

I have 2 entities: "Requests" and "Partners" There is a 1to1 relation between Requests and Partners so there is a partners_id field in Requests table I want to display a Partners list, but I need to take a value from a "price" field in Requests table

On my controller I have this query:

$listPartners = $em
    ->getRepository('OandPboBundle:Partners')
    ->createQueryBuilder('p')
    ->select('p')
    ->leftJoin('OandPboBundle:Requests', 'r', 'WITH', 'r.partners = p.id')        
    ->where('p.date LIKE :date AND p.active = :active')
           ->setParameter('date', '%'.$year.'-'.$month.'%')
           ->setParameter('active', 1)

    ->orderBy('p.date', 'DESC')
    ->addOrderBy('p.id', 'DESC')
    ->getQuery()
    ->getResult();

And in my TWIG file I have

{% for partners in listPartners %}
            <tr>
                <td>{{ partners.id }}</td>
                <td>{{ partners.price }}</td>
            </tr>
{% endfor %}

And of course there is an error because they say there is no "price" field in "Partners" (of course I want to take it from "Requests")

Is there a way to do that?

EDIT I post my 2 entities Entity "Requests"

class Requests

{ /** * @ORM\OneToOne(targetEntity="OandP\boBundle\Entity\Partners", cascade={"persist"}) * @ORM\JoinColumn(nullable=false) */ private $partners;

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

/**
 * @var string
 *
 * @ORM\Column(name="price", type="integer")
 */
private $price;

My entity "Partners"

/**

* Partners * * @ORM\Table(name="partners") * @ORM\Entity(repositoryClass="OandP\boBundle\Entity\PartnersRepository") */ class Partners {

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

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

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

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

Answer 1

You can't get it if there is unidirectional relationship. What I can suggest is to create a Twig function which gets the Requests object by Partners, may be like this:

{% for partners in listPartners %}
    {% set requests = requests_by_partners(partners) {# this is a custom twig function, you can create it by extending the Twig, http://symfony.com/doc/current/cookbook/templating/twig_extension.html #} %}
    <tr>
        <td>{{ partners.id }}</td>
        <td>{{ requests.price }}</td>
    </tr>
{% endfor %}

or declare you relationship as bidirectional, which allows you to get requests by partners:

{% for partners in listPartners %}
    <tr>
        <td>{{ partners.id }}</td>
        <td>{{ partners.requests.price }}</td>
    </tr>
{% endfor %}

Answer 2

Define field $requests in Partners Entity

/**
* @ORM\OneToOne(targetEntity="OandP\boBundle\Entity\Requests", mappedBy="partners")
*/
private $requests;

And in your Twig, Simply try:

{% for partners in listPartners %}
    <tr>
        <td>{{ partners.id }}</td>
        <td>{{ partners.requests.price }}</td>
    </tr>
{% endfor %}