By jeremy_a on

Hi,

In copying over SQL queries from Views, often the tables are labeled "node data" which (I think) is confusing me. For example, this simple query will generate a list of node reference titles.

SELECT node.nid AS nid,
   node_data_field_country.field_country_nid AS node_data_field_country_field_country_nid,
   node_data_field_country.nid AS node_data_field_country_nid,
   node.type AS node_type
 FROM node node 
 LEFT JOIN content_field_country node_data_field_country ON node.vid = node_data_field_country.vid

But looking at the query, it seems that only the nids are being returned not the titles. How can I spit out a list of titles from this in PHP, mimicking Views? I'm at a loss to know what to put in here.

$matches[$row->nid] = $row->???

Any help appreciated. Thanks

⋅ Categories: Drupal 6.x

Comments

jeremy_a’s picture

jeremy_a commented

Just to explain a little more: that query in Views will return a list of the names of referenced nodes, in my case countries, eg UK, USA, France. But looking at my database tables, both the field_country_nid and nid columns are just nids, not the node title.

I don't understand the difference between node_data_field_country.nid and (the usual) field_country.nid. What exactly does node_data do and how does it make the title of the node available...

Cheers

nevets’s picture

nevets commented

First the title, if I understand the query and your question to have the title returned, change SELECT node.nid AS nid, to SELECT node.nid AS nid, node.title as title,.

As for node_data views is aliasing the table content_field_country as node_data_field_country though I can not say why.

A why not just use views?

jeremy_a’s picture

jeremy_a commented

hi nevets,

Sorry, I think I messed up the question a little by being too complex ;) Basically what I'd like to know is: how does Views return the title of a node reference without resorting to a node_load or using relationships?

In my example above, adding in "SELECT node.title as title" of course, the row it produces looks like this:

nid | 22
node_title | Boots Chemists
node_data_field_country_field_country_nid | 33
node_data_field_country_nid | 22

22 is the nid of Boots. But if you use Views, it returns much more usefully Boots, Paris where Paris is node 33. After checking in Devel it seems it does this without wastefully loading nodes. So how does this happen?

nevets’s picture

nevets commented

Why not just use views? You can even embed views in a variety of ways.