I need to pull LATITUDE from the latitude table, LONGITUDE from the longitude table. I also want to pull the TITLE from the standard node_field_data table where the NID matches the entity_id from either the longitude or latitude table. I should also mention that this is Drupal 8, so it's not easy to find this info on any forums yet. I didn't use the usual db_query, because it's deprecated.
The below is what I have so far, but I cannot figure out how to get the LATITUDE and LONGITUDE based on the NID of the Title.
I have to get three fields from three different tables in the database. TABLENAME FIELDNAME node__field_latitude field_latitude_value node__field_longitude field_longitude_value node_field_data title
This is the query I have now
$db = \Drupal::database();
$query1= $db
->select('node__field_latitude','t')
->fields('t',array('field_latitude_value'));
$query2= $db
->select('node__field_longitude','b')
->fields('b',array('field_longitude_value'));
$titles= $db
->select('node_field_data','c')
->fields('c',array('title'));
$query1->union($query2, 'UNION ALL');
$query1->join('node_field_data','alias','alias.nid = t.entity_id');
$query1->union($titles, 'UNION ALL');
It works, but it gets all titles in the node_field_data table. There are only three that I need and the nid matches the entity_id in either the latitude table or the longitude table.
How can I fix this query to pull only the titles associate with the latitudes and longitudes I am pulling?
BONUS,
For some reason when I try to var_dump the results to an array, the array is creating two empty values. See my loop and the corresponding var_dump
$result=$query1->execute();
$test = array();
if ($result) {
while ($row = $result->fetchAssoc()) {
$test[] = $row['field_latitude_value'];
$test[] = $row['field_longitude_value'];
$test[] = $row['title'];
}
}
array(45) {
[0]=>
string(17) "32.79690170288086"
[1]=>
NULL
[2]=>
NULL
[3]=>
string(16) "32.7942008972168"
[4]=>
NULL
[5]=>
NULL
[6]=>
string(18) "32.711700439453125"
[7]=>
NULL
[8]=>
NULL
[9]=>
string(19) "-117.25499725341797"
[10]=>
NULL
[11]=>
NULL
[12]=>
string(19) "-117.25599670410156"
[13]=>
NULL
[14]=>
NULL
[15]=>
string(19) "-117.15699768066406"
[16]=>
NULL
[17]=>
NULL
[18]=>
string(27) "2$ off beers, kids eat free"
[19]=>
NULL
[20]=>
NULL
[21]=>
string(12) "Taco Tuesday"
[22]=>
NULL
[23]=>
NULL
[24]=>
string(16) "House Wine Night"
[25]=>
NULL
[26]=>
NULL
[27]=>
string(12) "Taco Tuesday"
[28]=>
NULL
[29]=>
NULL
[30]=>
string(24) "Backyard Kitchen and Tap"
[31]=>
NULL
[32]=>
NULL
[33]=>
string(23) "Pacific Beach Shoreclub"
[34]=>
NULL
[35]=>
NULL
[36]=>
string(13) "Knotty Barrel"
[37]=>
NULL
[38]=>
NULL
[39]=>
string(13) "Deals Near Me"
[40]=>
NULL
[41]=>
NULL
[42]=>
string(16) "Craft beer night"
[43]=>
NULL
[44]=>
NULL
}
EDIT: Jaypan helped me figure this out, here is the final code:
$result = \Drupal::database()->query('SELECT node.title, latitude.field_latitude_value, longitude.field_longitude_value '.
'FROM {node_field_data} AS node '.
'JOIN {node__field_latitude} AS latitude '.
'ON latitude.entity_id = node.nid '.
'JOIN {node__field_longitude} AS longitude '.
'ON longitude.entity_id = node.nid '.
'JOIN {node__field_postal_code} AS zipcode '.
'ON zipcode.entity_id = node.nid '.
'WHERE zipcode.field_postal_code_value = '.$postal_code);