Get blank value if field value is not available

Question

I have following query on my D7 project

$query = db_select('payment_logs', 'p');
$query->join('field_data_field_frequency', 'd', 'd.entity_id = p.nid');
$query->join('field_data_field_maid_id', 'm', 'm.entity_id = p.nid');
$query->join('field_data_field_coupon_code', 'c', 'c.entity_id = p.nid');
$query->join('field_data_field_tip_amount', 't', 't.entity_id = p.nid');
$query->fields('p');
$query->fields('d', array('field_frequency_tid'));
$query->fields('m', array('field_maid_id_uid'));
$query->fields('c', array('field_coupon_code_value'));
$query->fields('t', array('field_tip_amount_value'));
$query->condition('p.section','booking','=');
$result = $query->execute();
$res = $result->fetchAll();
return $res;

this query returns data where field_tip_amount_value and field_coupon_code_value are available. I want to get all data with blank field values if not available. these two fields are optional other fields will be there always. so if tip or coupon is not there data must return blank for this field.

I was doing it with separate queries before but it was taking too much time with multiple loops. is there a way to get it done with join ?

Answer 1

You can change $query->join to $query->leftjoin.

This will cause the query to return a row, even if there's no entry in the joined table.

Hope this helps.