Show in twig the result of a JOIN query in Symfony

Question

I have 2 tables which are not related by ORM so I do theJOIN as follows:

$dql="SELECT u, o
      FROM BackendBundle:Orders o
      JOIN BackendBundle:Users u
      WITH o.userid=u.id";
$query=$em->createQuery($dql);

When I send the query to twig to show it on the screen it shows me an error.

This is the code in twig:

{% for ordenes in pagination %}
  <td>{{ ordenes.orderid }}</td>
  <td>{{ ordenes.orderdate|date("Y-m-d") }}</td>
{% endfor %}

And the error is this:

Neither the property "orderid" nor one of the methods "orderid()", "getorderid()"/"isorderid()" or "__call()" exist and have public access in class "BackendBundle\Entity\Users"

If I run twig this code:

{{dump(pagination)}}

It correctly shows me what the query returns:

  #currentPageNumber: 3
  #numItemsPerPage: 20
  #items: array:40 [▼
    0 => Orders {#522 ▼
      -orderid: 158
      -orderdate: DateTime {#476 ▶}
      -ordernumber: "FFFFFFFFFFF"
      -customername: "UUUUUUUUUUUU"
      -customeraddress: "OOOOOO"
      -countrycode: "US"
      -shippingaddress: "PPPPPP"
      -status: "Ordered"
      -subtotal: "5.00"
      -shippingcost: "2.82"
      -tax: "0.00"
      -discount: "0.00"
      -totalorder: "7.82"
      -shippingcarrier: "Fedex"
      -userid: "1206"
    }
    1 => Users {#525 ▼
      -id: 1206
      -username: "xxxxxxxxxxxxxx@hotmail.com"
      -yourname: null
      -firstname: "GGGG"
      -lastname: "IIIIII"
      -middlename: ""
      -address: "oooooooooo"
      -address2: ""
      -apartment: ""
      -pobox: ""
      -city: "Monsey"
      -state: "NY"
      -zipcode: "88888888"
      -country: "USA"
      -countrycode: "US"
      -email: "xxxxxxxxxxx@hotmail.com"
      -datein: DateTime {#521 ▶}
    }

My question is how can I show this result in twig ??

Answer 1

After reviewing and trying to solve this problem. Finally create the relationship in the files and between the 2 tables in this way:

//BackendBundle\Entity\Orders:
indexes:
    fk_order_user1_idx:
        columns:
            - user_id
......
manyToOne:
    user:
        targetEntity: Users
        cascade: {  }
        fetch: LAZY
        mappedBy: null
        inversedBy: null
        joinColumns:
            user_id:
                referencedColumnName: id
        orphanRemoval: false

Also in the Entity:

//Orders.php
/**
 * @var \BackendBundle\Entity\Users
 */
private $user;
......
/**
 * Set user
 *
 * @param \BackendBundle\Entity\Users $user
 *
 * @return Users
 */
public function setUser(\BackendBundle\Entity\Users $user = null)
{
    $this->user = $user;

    return $this;
}

/**
 * Get user
 *
 * @return \BackendBundle\Entity\Users
 */
public function getUser()
{
    return $this->user;
}

I updated the database:

php bin/console doctrine:schema:update --force

And I make the query in this way:

$dql="SELECT u, o
      FROM BackendBundle:Orders o
      JOIN o.user u";
$query=$em->createQuery($dql);