0

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); 
4
  • Can you explain what you are trying to retrieve in human terms? Right now you are asking us to try to determine what you want based on a query that isn't working, making it difficult. So if you can put it in words like 'I want X and Y and Z, from tables A, B and C, when SOMETHING is equal to VALUE', it will make it easier for us to help
    – Jaypan
    Commented Apr 11, 2017 at 0:30
  • Ok sorry. I want 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. Make sense? Commented Apr 11, 2017 at 15:01
  • If you found the solution, post it as answer. Don't edit other answers, nor add it to the question. This is a Q&A site: one question, one or more answers. Question and answers need to be kept separated.
    – apaderno
    Commented Apr 12, 2017 at 3:33
  • I was the one who added the answer to his post.
    – Jaypan
    Commented Apr 12, 2017 at 3:45

1 Answer 1

0

If I understand you correctly, you want:

$data = \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.id ' .
  'JOIN {node__field_longitude} AS longitude ' .
  'ON longitude.entity_id = node.id ' .
  'WHERE node.nid = :nid',
  [':nid' => $nid]
)->fetchAssoc();

Set $nid to the node ID you want, and you should be good.

13
  • Ok trying to plug this in now Commented Apr 11, 2017 at 17:16
  • Maybe. I wrote that off the top of my head - untested.
    – Jaypan
    Commented Apr 11, 2017 at 17:20
  • Something about the code above is crashing the page. I'm just getting "The Website encountered an unexpected error. Please try again later. I deleted everything but that code to make sure it wasn't something else. I'm still playing with it though Commented Apr 11, 2017 at 17:32
  • It's something about the line wraps I think because when I shorten it to just the one line it doesn't error. Commented Apr 11, 2017 at 17:35
  • I'm still working on it, seems to be the ON that is causing the problem. I changed to node.nid and it doesn't crash, almost there Commented Apr 11, 2017 at 17:49

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.