1

Time to put our database hats on! We have a complex database call that responds to an AJAX request. We've omitted most of the code to just include the portion that is giving us trouble. We are working in Drupal 7.

  $products = "field_data_field_product_ref"; // table that contains the info about the product
  $productref = "field_product_ref_target_id"; // 

  // The following query prints the Title of our Object and the ID of the product it references

  $query = db_query ("Select n.nid AS nid, n.title AS title, p.$productref AS productid
  FROM {node} n
  INNER JOIN {$products} p ON p.entity_id = n.nid");


foreach ($query as $row) {
  print 'Swatch Title: '.$row->title.'<br>';
  print 'Product ID: '.$row->productid.'<br><br>';

}

This outputs:
Swatch Title: Saddle Brown
Product ID: 19

Swatch Title: Sandalwood
Product ID: 20

Swatch Title: Saratoga
Product ID: 22

However, we want to also return the actual title of the product being referenced not its Product ID. A separate query can do this:

    $query = db_query ("Select n.nid AS pnid, n.title AS ptitle, p.entity_id AS productentityid
      FROM {node} n
      INNER JOIN {$products} p ON p.$productref = n.nid");

    foreach ($query as $row) {
      print 'Product Title: '.$row->ptitle.'<br>';
      print 'Swatch ID: '.$row->productentityid.'<br><br>';

    }

Our second query's output:
Product Title: Slab
Swatch ID: 265

Product Title: Stone
Swatch ID: 184

Product Title: Brick
Swatch ID: 185

We would like the results to be:

Swatch Title: Saddle Brown
Product Title: Slab

Swatch Title: Sandalwood
Product Title: Stone

Swatch Title: Saratoga
Product Title: Brick

Can anyone clear up how to combine the 2 queries to get the intended output?

3

I think you just want to join the node table in to your query again and grab the title that way:

$query = db_query("
  SELECT n.nid AS pnid, n.title AS ptitle, n2.title as swatch_title
    FROM {node} n
    INNER JOIN {$products} p ON p.$productref = n.nid
    INNER JOIN {node} n2 ON n2.nid = p.$productref"
);
1
  • BINGO! I knew this was an easy one, but for some reason I was stuck. It was the second join that was confusing, as to how to refer back to the main node table without them conflicting. But this makes total sense and is working! – Starfs Jan 27 '12 at 19:54
3
$query = db_query ("SELECT np.title AS product_title, ns.title AS swatch_title
                    FROM {node} np
                    INNER JOIN {$products} p ON p.entity_id = pn.nid
                    INNER JOIN {node} ns ON p.$productref = ns.nid");

Should work. Hard to tell since you use variables inside your SQL not defined in your code sample. But basically just join the products table and then back to the node table.

2
  • Thanks. This is also correct! I think Clive beat you to it by 3 minutes. ;) – Starfs Jan 27 '12 at 19:56
  • @Starfs That was the time I spent to test my code worked. Well as long as it works for you. – googletorp Jan 27 '12 at 20:19

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.