0

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'
2
  • 1
    Could you add the code you've tried along with what didn't work please?
    – Clive
    Oct 30, 2013 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, 2013 at 20:59

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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