Query Builder自定义on子句(Query Builder custom on clause)

 我想用doctrine映射以下SQL语句 
 
select 
    address.*,
    (CASE WHEN (customer.default_billing_address_id = address.id) THEN 1 ELSE 0 END) as isDefaultBillingAddress,
    (CASE WHEN (customer.default_shipping_address_id = address.id) THEN 1 ELSE 0 END) as isDefaultShippingAddress
from 
    s_user customer
join
    s_user_attributes customerattributes on customerattributes.customer_number = customer.customernumber
join
    s_user_addresses address on address.user_id = customer.id
where customerattributes.userID = 214
 
 但是当我用查询构建器编写我的doctrine查询时，我总是得到一个不同的查询： 
 
[...]
FROM s_user_addresses s1_
[...]
INNER JOIN s_user_attributes s5_ ON s0_.id = s5_.userID
INNER JOIN s_user s0_ ON s0_.id = s5_.userID
AND (s5_.customer_number = s0_.customernumber)
WHERE s5_.userID = 214
ORDER BY sclr_0 DESC
  ,sclr_1 DESC
 
 最后，这是使用查询构建器的代码： 
 
$builder->from(Address::class, 'address')
    ->andWhere('customerAttribute.customerId = :userId')
    ->setParameter('userId', $userId)
    ->join('address.customer', 'customer')
    ->join('customer.attribute', 'customerAttribute', Join::WITH, 'customerAttribute.customerNumber = customer.number')
    ->addSelect([
        '(CASE WHEN (customer.defaultBillingAddress = address.id) THEN 1 ELSE 0 END) as HIDDEN isDefaultBillingAddress',
        '(CASE WHEN (customer.defaultShippingAddress = address.id) THEN 1 ELSE 0 END) as HIDDEN isDefaultShippingAddress',
    ])
    ->addOrderBy('isDefaultBillingAddress', 'DESC')
    ->addOrderBy('isDefaultShippingAddress', 'DESC');
 
 问题是， JOIN子句得到一个AND ，我只想要customer_number加入而不是id 

I want to map the following SQL statement with doctrine
 
select 
    address.*,
    (CASE WHEN (customer.default_billing_address_id = address.id) THEN 1 ELSE 0 END) as isDefaultBillingAddress,
    (CASE WHEN (customer.default_shipping_address_id = address.id) THEN 1 ELSE 0 END) as isDefaultShippingAddress
from 
    s_user customer
join
    s_user_attributes customerattributes on customerattributes.customer_number = customer.customernumber
join
    s_user_addresses address on address.user_id = customer.id
where customerattributes.userID = 214
 
But when I write my doctrine query with the query builder I always get the query a little different:
 
[...]
FROM s_user_addresses s1_
[...]
INNER JOIN s_user_attributes s5_ ON s0_.id = s5_.userID
INNER JOIN s_user s0_ ON s0_.id = s5_.userID
AND (s5_.customer_number = s0_.customernumber)
WHERE s5_.userID = 214
ORDER BY sclr_0 DESC
  ,sclr_1 DESC
 
And finally this is the code that uses the query builder:
 
$builder->from(Address::class, 'address')
    ->andWhere('customerAttribute.customerId = :userId')
    ->setParameter('userId', $userId)
    ->join('address.customer', 'customer')
    ->join('customer.attribute', 'customerAttribute', Join::WITH, 'customerAttribute.customerNumber = customer.number')
    ->addSelect([
        '(CASE WHEN (customer.defaultBillingAddress = address.id) THEN 1 ELSE 0 END) as HIDDEN isDefaultBillingAddress',
        '(CASE WHEN (customer.defaultShippingAddress = address.id) THEN 1 ELSE 0 END) as HIDDEN isDefaultShippingAddress',
    ])
    ->addOrderBy('isDefaultBillingAddress', 'DESC')
    ->addOrderBy('isDefaultShippingAddress', 'DESC');
 
The problem is, that the JOIN clause gets an AND, I just want that the customer_number gets joined not the id's

原文：https://stackoverflow.com/questions/43207925