How do convert mysql query with sub queries to a db_select version?

Question

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
LEFT JOIN 
(
    select * 
    from field_data_field_address_2

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

) pa2 
ON o.oid = pa2.oid
LEFT JOIN 
(
    select * 
    from field_data_field_company

) pa3 
ON u.uid = pa3.entity_id
WHERE p.id = 'RM11'

Answer 1

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();