Doctrine leftJoin entity is not a child of the main entity

Question

I have huge query with many leftJoins. All the leftJoin, except of one, is associated in the entities. The one, which does not gets every time an own entry, if I use the array hydration mode.

Basically, I am using Shopware and this query is part of a plugin. However, every referenced Entity, except of my own, can be found at the Shopware GitHub repo: https://github.com/shopware/shopware/blob/5.3/engine/Shopware/Models/Order/Order.php

My query in my repository:

$builder = $this->getEntityManager()->createQueryBuilder();
$builder->select([
    'orders',
    'details',
    'payment.id',
    'payment.name',
    'payment.description',
    'payment.action',
    'payment.pluginId',
    'paymentInstances',
    'shipping',
    'billing',
    'billingCountry',
    'shippingCountry',
    'paymentPayPal',
    'paymentStatus',
]);

$builder->from('Shopware\Models\Order\Order', 'orders');
$builder->leftJoin('orders.details', 'details')
    ->leftJoin('orders.payment', 'payment')
    ->leftJoin('orders.paymentInstances', 'paymentInstances')
    ->leftJoin('SapExport\Models\PaymentPayPal', 'paymentPayPal', \Doctrine\ORM\Query\Expr\Join::WITH, 'orders.number = paymentPayPal.ordernumber AND payment.name = \'paypal\'')
    ->leftJoin('orders.billing', 'billing')
    ->leftJoin('billing.country', 'billingCountry')
    ->leftJoin('orders.shipping', 'shipping')
    ->leftJoin('shipping.country', 'shippingCountry')
    ->leftJoin('orders.paymentStatus', 'paymentStatus');

$builder->where('orders.cleared = ' . Status::PAYMENT_STATE_COMPLETELY_PAID)
    ->andWhere($builder->expr()->notIn('orders.id', $this->getSapExportIdsQuery()->getDQL()));

$builder->orderBy('orders.id', 'DESC');

The subselect query in where clause:

$builder = $this->getEntityManager()->createQueryBuilder();
$builder->select([
    'sapExport.id'
]);
$builder->from('SapExport\Models\SapExport', 'sapExport');

If I hydrate it to array it outputs something like this (json encoded data):

[
  [
    {
      "id": 10353,
      "number": "650004023",
      "customerId": 4235,
      "status": 0,
      "cleared": 12,
      "paymentId": 7,
      "dispatchId": 10,
      "partnerId": "",
      "shopId": 1,
      "invoiceAmount": 99.95,
      "invoiceAmountNet": 83.99,
      "invoiceShipping": 0,
      "invoiceShippingNet": 0,
      ...
      "details": [
        {
          ...
        }
      ],
      "paymentInstances": [
        {
          ...
        }
      ],
      "billing": {
        ...
      },
      "shipping": {
        ...
      },
      "paymentStatus": {
        ...
      }
    }
  ],
  {
  "0": {
  "id": 251,
  "ordernumber": "650004023",
  "referenceNumber": "34TEST",
  "instructionType": "TEST",
  "bankName": "bank name",
  "accountHolder": "PayPal Europe",
  "iban": "DEXXXXXX",
  "amountValue": "99.95",
  "amountCurrency": "EUR",
  "paymentDueDate": {
  "date": "2018-01-31 00:00:00.000000",
  "timezone_type": 3,
  "timezone": "Europe\/Berlin"
  },
  "links": "[{\"href\":\"https://www.example.com\",\"rel\":\"self\",\"method\":\"GET\"}]"
  },
  "id": 7,
  "name": "paypal",
  "description": "PayPal",
  "action": "payment_paypal",
  "pluginId": 64
  }
]

Needed result / Expected result:

[
  [
    {
      "id": 10353,
      "number": "650004023",
      "customerId": 4235,
      "status": 0,
      "cleared": 12,
      "paymentId": 7,
      "dispatchId": 10,
      "partnerId": "",
      "shopId": 1,
      "invoiceAmount": 99.95,
      "invoiceAmountNet": 83.99,
      "invoiceShipping": 0,
      "invoiceShippingNet": 0,
      ...
      "details": [
        {
          ...
        }
      ],
      "paymentInstances": [
        {
          ...
        }
      ],
      "billing": {
        ...
      },
      "shipping": {
        ...
      },
      "paymentStatus": {
        ...
      },
      "paymentPayPal": {
        "id": 251,
        "ordernumber": "650004023",
        "referenceNumber": "34TEST",
        "instructionType": "TEST",
        "bankName": "bank name",
        "accountHolder": "PayPal Europe",
        "iban": "DEXXXXXX",
        "amountValue": "99.95",
        "amountCurrency": "EUR",
        "paymentDueDate": {
          "date": "2018-01-31 00:00:00.000000",
          "timezone_type": 3,
          "timezone": "Europe\/Berlin"
        },
        "links": "[{\"href\":\"https://www.example.com\",\"rel\":\"self\",\"method\":\"GET\"}]"
      },
      "payment": {
        "id": 7,
        "name": "paypal",
        "description": "PayPal",
        "action": "payment_paypal",
        "pluginId": 64
      }
    }
  ]
]

If I execucte the SQL on the database, the result set is as expected. So, it seems to be a strange doctrine entity mapping.

Edit: For clearance to my actually problem, that I have: The entity payment is not on the main entity, called orders. It gets an own object, right next to orders in the array result set. So, we have two issues there:

payment is an object, not an array.
It gets an own entry, but it should appear under the orders entry in result set.

Also, paymentPayPal entity does completely not appear in the result set. But it should, because I selected it as well.

Edit 2: This is the resulting DQL Query (please note that I reduced details entity a bit, since I don't need all columns of it):

SELECT orders, details.id, details.articleId, details.number, details.articleNumber, details.price, details.quantity, details.articleName, details.mode, details.esdArticle, details.unit, details.packUnit, payment.id, payment.name, payment.description, payment.action, payment.pluginId, paymentInstances, shipping, billing, billingCountry, shippingCountry, paymentPayPal, paymentStatus FROM Shopware\Models\Order\Order orders LEFT JOIN orders.details details LEFT JOIN orders.payment payment LEFT JOIN orders.paymentInstances paymentInstances LEFT JOIN SapExport\Models\PaymentPayPal paymentPayPal WITH orders.number = paymentPayPal.ordernumber AND payment.name = 'paypal' LEFT JOIN orders.billing billing LEFT JOIN billing.country billingCountry LEFT JOIN orders.shipping shipping LEFT JOIN shipping.country shippingCountry LEFT JOIN orders.paymentStatus paymentStatus WHERE orders.cleared = 12 AND orders.id NOT IN(SELECT sapExport.id FROM SapExport\Models\SapExport sapExport) ORDER BY orders.id DESC

Edit 3: The query works fine, if I do not leftJoin the paypal table. I need this table to get additional informations, if the customer has choosen paypal as the payment type for his oder her order. But only for this type I need those additional informations. As far as I know, leftJoin should do the trick. Because, if it's not paypal, it should join an empty record set with nulled columns. But I am wondering why Doctrine behaves this way, the SQL Query pendant works well...

are you looking for somthing like this : https://stackoverflow.com/questions/1990352/mysql-multiple-left-joins — Nishit Manjarawala, Mar 19 '18 at 12:41
if youre using entities with proper relationships setup, then why not just use getters and setters to retrieve the data you need? Let doctrine do the heavy lifting. eg: `Order::getDetails`. If you need lots of info, create a `toArray` method and dump in what you need. — DevDonkey, Mar 19 '18 at 14:02
@NishitManjarawala Yeah, every sql aggregate function needs a group by statement. But I don't have this in my query. However, I tried it by adding an `group by orders.id`. Doesn't change anything. @DevDonkey I don't have properly setup entities, because I can't alter Shopware's core models. That's the point why I need such a complex query. Ok, well, I am able to alter Shopware's core models, but it well be gone after each update. — alpham8, Mar 19 '18 at 14:13

score 0 · Answer 1 · answered Mar 15 '18 at 15:05

0

Did you try use QueryBuilder helper method addSelect with leftJoin.

Personally I never use it before, but I known it exists and it can be useful for you IMO.

Some docs are here and here is some Doctrine tutorial.

answered Mar 15 '18 at 15:05

SlaWitDev

457
2
9

Ok, and how it helps me? – alpham8 Mar 16 '18 at 07:34
If I good understund, it add your select (of join) into existing in query selects and array hydrate should looks similar you expected. – SlaWitDev Mar 16 '18 at 08:44
Well, that's basically the same as I already did. When you use the select method on `QueryBuilder` with an array as parameter, it selects you exactly those fields you specified in the array. And as far as I know, the `QueryBuilder` methods always return the instance of itself. So, as you called it "the existing query". – alpham8 Mar 16 '18 at 09:27
in your example, you have $builder->select(['sapExport.id']); and as a result {...} [0] => {}. Try to use addSelec instead of just select. If you have proper left join for sapExport - it should works as result {.... [sapExport} => {} }. ? – SlaWitDev Mar 16 '18 at 09:32
1

I think you misunderstood me. I edited my question. Please have a look (and please tell me, if I am wrong). – alpham8 Mar 16 '18 at 09:50
Yep. I misunderstund you. Now is clear. You use two different models class, probably there is a problem SapExport\Models\PaymentPayPal for leftJoin in first query but SapExport\Models\SapExport for subquery. You always can check you querybuilder by $qb->getDql(); for test if this simillar for your working SQL. – SlaWitDev Mar 16 '18 at 10:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166951/discussion-between-slawitdev-and-alpham8). – SlaWitDev Mar 16 '18 at 10:46

Doctrine leftJoin entity is not a child of the main entity

1 Answers1