8

How to perform join with multiple columns using db_select? Any help?

Here below is the sample, which I'm looking for.

SELECT * FROM digitaliq_flds_by_node a
JOIN digitaliq_fld_category b ON
  a.fld_id = b.fld_id AND
  a.fld_collection_id = b.fld_collection_id AND
  a.fld_group_name = b.fld_group_name

2 Answers 2

11

You can use the following code.

$query = db_select('digitaliq_flds_by_node', 'a')->fields('a');
$alias = $query->join('digitaliq_fld_category', 'b', 'a.fld_id = %alias.fld_id AND a.fld_collection_id = %alias.fld_collection_id AND a.fld_group_name = %alias.fld_group_name');
$terms = $query->execute();

Calls to SelectQuery::join() (like SelectQuery::leftJoin(), SelectQuery::innerJoin(), and SelectQuery::addJoin()) aren't chain-able because those methods don't return a SelectQuery object, but the actual alias used for the join. If you execute the following code, you would get an error.

$query = db_select('digitaliq_flds_by_node', 'a')
  ->fields('a')
  ->join('digitaliq_fld_category', 'b', 'a.fld_id = %alias.fld_id AND a.fld_collection_id = %alias.fld_collection_id AND a.fld_group_name = %alias.fld_group_name')
  ->execute();

Strings in PHP are not objects nor they define an execute() method.

To verify the code is executing the right SQL query, cast $query to a string, and print it. You will get the SQL query that would be executed from your code.

$query = db_select('digitaliq_flds_by_node', 'a')->fields('a');
$alias = $query->join('digitaliq_fld_category', 'b', 'a.fld_id = %alias.fld_id AND a.fld_collection_id = %alias.fld_collection_id AND a.fld_group_name = %alias.fld_group_name');
print $query;

With that code, I get the following output.

SELECT a.*
FROM 
{digitaliq_flds_by_node} a
INNER JOIN {digitaliq_fld_category} b ON a.fld_id = b.fld_id AND a.fld_collection_id = b.fld_collection_id AND a.fld_group_name = b.fld_group_name
0
4

Try this

$query = db_select('digitaliq_flds_by_node', 'a');
$query->join('digitaliq_fld_category', 'b', 'a.fld_id = b.fld_id');
$query->condition('a.fld_collection_id', 'b.fld_collection_id' '=');
$query->condition('a.fld_group_name', 'b.fld_group_name', '=');
$query->fields('a');
$query->execute();
5
  • 2
    The code you are using doesn't work: leftJoin() doesn't return a SelectQuery object. The code is calling condition() for something that is not an object.
    – apaderno
    Commented Dec 19, 2012 at 8:59
  • In other words join is not chain able Commented Dec 19, 2012 at 9:30
  • @Mathankumar That is the word I was looking for: chain-able calls. :)
    – apaderno
    Commented Dec 19, 2012 at 9:33
  • 1
    Still the answer is incorrect. check the answer by kiamlaluno Commented Dec 19, 2012 at 9:33
  • Now its fine. Additional note, by default the operator for condition is equal to(=), so its not necessary to specify them. Commented Dec 19, 2012 at 10:18

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.