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
10

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-wise 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, and they don't define the 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 this 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
| improve this answer | |
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();
| improve this answer | |
  • 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. – kiamlaluno Dec 19 '12 at 8:59
  • In other words join is not chain able – Mathankumar Dec 19 '12 at 9:30
  • @Mathankumar That is the word I was looking for: chain-able calls. :) – kiamlaluno Dec 19 '12 at 9:33
  • 1
    Still the answer is incorrect. check the answer by kiamlaluno – Mathankumar Dec 19 '12 at 9:33
  • Now its fine. Additional note, by default the operator for condition is equal to(=), so its not necessary to specify them. – Mathankumar Dec 19 '12 at 10:18

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.