Below is a mysql query that returns a proper result set. How can I convert this into a db_select() version?

SELECT p.id, u.name, pa.field_address_2_value, pa2.display, pa3.field_company_value
FROM ms_order_products p
LEFT JOIN ms_orders o ON p.oid = o.oid 
LEFT JOIN users u ON o.uid = u.uid
    select * 
    from field_data_field_address_2

) pa 
ON u.uid = pa.entity_id
    select * 
    from ms_order_adjustments

) pa2 
ON o.oid = pa2.oid
    select * 
    from field_data_field_company

) pa3 
ON u.uid = pa3.entity_id
WHERE p.id = 'RM11'
  • 1
    Could you add the code you've tried along with what didn't work please? – Clive Oct 30 '13 at 0:30
  • Just curios, in your original query what is the point of using those "left join (select * "s , the query can be simplified a lot – Victor Oct 30 '13 at 20:59

I found the answer, see below. (I added a few more fields as well)

  $sub_query_1 = db_select('field_data_field_address_2', 'addr2');
  $sub_query_1->fields('addr2', array('field_address_2_value', 'entity_id'));

  $sub_query_2 = db_select('ms_order_adjustments', 'a');
  $sub_query_2->fields('a', array('display', 'oid'));

  $sub_query_3 = db_select('field_data_field_company', 'comp');
  $sub_query_3->fields('comp', array('field_company_value', 'entity_id'));

  $query = db_select('ms_order_products', 'p');

  $query->fields('p', array('id', 'oid', 'type', 'amount'));
  $query->fields('o', array('oid', 'uid', 'status', 'order_type', 'created'));
  $query->fields('u', array('uid', 'mail', 'name'));
  $query->fields('fn', array('field_first_name_value'));
  $query->fields('ln', array('field_last_name_value'));
  $query->fields('adr1', array('field_address_1_value'));
  $query->fields('city', array('field_city_value'));
  $query->fields('state', array('field_state_value'));
  $query->fields('zip', array('field_zip_code_value'));
  $query->fields('pa', array('field_address_2_value'));
  $query->fields('pa2', array('display'));
  $query->fields('pa3', array('field_company_value'));

  $query->leftjoin('ms_orders', 'o', 'o.oid = p.oid');
  $query->leftjoin('users', 'u', 'o.uid = u.uid');
  $query->leftjoin('field_data_field_first_name', 'fn', 'u.uid = fn.entity_id');
  $query->leftjoin('field_data_field_last_name', 'ln', 'u.uid = ln.entity_id');
  $query->leftjoin('field_data_field_address_1', 'adr1', 'u.uid = adr1.entity_id');
  $query->leftjoin('field_data_field_city', 'city', 'u.uid = city.entity_id');
  $query->leftjoin('field_data_field_state', 'state', 'u.uid = state.entity_id');
  $query->leftjoin('field_data_field_zip_code', 'zip', 'u.uid = zip.entity_id');

  $query->addJoin('LEFT', $sub_query_1, 'pa', 'u.uid = pa.entity_id');
  $query->addJoin('LEFT', $sub_query_2, 'pa2', 'o.oid = pa2.oid');
  $query->addJoin('LEFT', $sub_query_3, 'pa3', 'u.uid = pa3.entity_id');

  $query->condition('p.id', 'RM11', '=');

  $result = $query->execute();
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.