Basically, I have a product object already but need to find out the node ID of its product display node. I'm trying to do this directly through a database query but don't know how to JOIN the right tables to end up with the result I need.
Yeah, writing a database query directly to do this could be challenging, because you have to connect through the proper field table. Fortunately, Drupal 7 provides a great tool for building database queries when you need to query entities based on their field values called EntityFieldQuery.
The EntityFieldQuery class is located in includes/entity.inc in your Drupal folder and has pretty complete documentation. What you'll do is build a query of nodes that looks for any node whose product reference field points to your product's ID. If you know your node types, you can bake those into your conditions as well.
If you installed using Commerce Kickstart, then your product reference field is named field_product and your display node type is product_display. I'll use these in the code snippet below and am assuming your product object is stored in a variable named $product.
<?php
$query = new EntityFieldQuery();
$result = $query
->entityCondition('entity_type', 'node', '=')
->entityCondition('bundle', 'product_display', '=')
->fieldCondition('field_product', 'product_id', $product->product_id, '=')
->execute();
?>The return value of the query will be an array of matching node IDs nested inside a node array. There may be multiple nodes matching, but here's an array where just one does:
Array
(
[node] => Array
(
[1] => stdClass Object
(
[nid] => 1
[vid] => 1
[type] => product_display
)
)
)