1

I've inherited this code that runs on our home page. It's used to do a custom display of specific pieces of related info. Here's how things are set up:

We have an article. It has a series of node reference fields that allow us to link related images (actually nodes themselves, which contain a headshot and some text; it's not an actual attached image.) Each of those headshots has a thumbnail created via ImageCache so that we can display a small version in search results and on list-type pages.

We have a new requirement that if the related image node for a specific article has a certain taxonomy term (tid) associated with it, it should display differently than other images.

The existing query (which I did not write) looks like this:

SELECT DISTINCT n.title, nr.teaser, n.nid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image, cfri.field_related_images_nid as image_id 

FROM node n JOIN node_revisions nr ON n.nid = nr.nid 
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) 
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid 
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid  
JOIN term_node tn2 ON n.nid = tn2.nid 

WHERE n.status = 1 AND n.type = 'article'   AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 ORDER BY n.created DESC LIMIT 3

This returns a recordset that looks like this:

TITLE         |  TEASER           |  NID |  DATE            |  IMAGE                            | IMAGE_ID
==========================================================================================================
Exec Profile  |  Could you please | 67491 | April 29, 2011  | sites/default/files/kcjones.jpg   | 67572
Unilver Ads   |  Unilever topped  | 67421 | April 20, 2011  | sites/default/files/unilever.jpg  | 66889

This is all well and good. But what I need to do is (in the same query, somehow) also return the tid that goes with the image_id from the term_node table. On its own for a single image node that query is simple:

SELECT tid FROM term_node WHERE nid = 67572

But how do I modify the existing query to bring in the tid based on the image_id found in the main part of the query? I'm guessing I need some kind of subquery, but this is stretching the limits of my sql chops...

1

You already have the {term_node} table joined in your query so I'm pretty sure you should just be able to change your first line to include tn2.tid. Try this:

SELECT DISTINCT n.title, nr.teaser, n.nid, tn2.tid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image, cfri.field_related_images_nid as image_id 
FROM node n JOIN node_revisions nr ON n.nid = nr.nid 
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) 
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid 
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid  
JOIN term_node tn2 ON n.nid = tn2.nid 

WHERE n.status = 1 AND n.type = 'article'   AND nr.body LIKE '%kimberly-clark%' AND     tn2.tid = 143 ORDER BY n.created DESC LIMIT 3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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