Multiple On conditions in Doctrine2 Left Join

Question

I have RATE and BRANCH_CURRE table. I want to perform left join operation (joining branch to rate) in Doctrine Query Language (DQL). My SQL Query is:

SELECT r.id rid
    ,r.TIME rtime
    ,r.rate_candidate
    ,r.exchange_rate
    ,r.branch
    ,r.STATUS ratestatus
    ,bc.currency
    ,bc.scale bcscale
    ,bc.STATUS bcstatus
FROM rate r
LEFT JOIN branch_currency bc ON (
        r.branch = bc.branch
        AND (
            r.from_currency = bc.currency
            OR r.to_currency = bc.currency
            )
        )
WHERE r.STATUS = 1
    AND bc.STATUS = 1;

To be more specific, I have two questions here

1. How to select some specific columns from both the tables.
2. How to give the multiple ON conditions while joining tables.

So Please show the DQL query using queryBuilder(). Thanx in advance!!!

Answer 1

I suggest to add the additional conditions into a where condition. Other than that I highly recommend to read the documentation regarding the Doctrine QueryBuilder etc. because you're question does not show that you have any experience with Doctrine at all. Just throwing a MySQL query without any personal effort at us is not a nice and fair way.

This is not tested but should give you some guidance.

    $qb = $this->_em->createQueryBuilder();
    $qb->select('r.branch, bc.exchange_rate');
    $qb->from('rate', 'r');
    $qb->leftJoin('r.branch', 'bc');
    $qb->where($qb->expr()->orX('r.from_currency=bc.currency','r.to_currency = bc.currency));