current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to convert the following SQL query in calls to the database API.

SELECT p.id, p.message,t.posted, p.posted AS reply_date,p.poster_id,t.id,t.subject, t.forum_id FROM punbb.punbb_posts AS p JOIN punbb.punbb_topics AS t ON p.topic_id = t.id WHERE p.id = t.first_post_id;

What I get is the following code. 

$query = db_select('punbb_posts', 'p')
  ->fields('p', array('id', 'message', 'posted', 'poster_id'));
$query->join('punbb_topics', 't', 'p.topic_id = t.id');
$query->addField('t', 'posted');
$query->addField('t', 'id');
$query->addField('t', 'subject');
$query->addField('t', 'forum_id');
$query->addField('t', 'first_post_id');
$query->condition('t.first_post_id','p.id');

The code generates the following SQL query.

    SELECT p.id AS id, p.message AS message, p.posted AS posted, p.poster_id AS poster_id, t.posted AS t_posted, t.id AS t_id, t.subject AS subject, t.forum_id AS forum_id, t.first_post_id AS first_post_id
FROM 
{punbb_posts} p
INNER JOIN {punbb_topics} t ON p.topic_id = t.id
WHERE  (t.first_post_id = :db_condition_placeholder_0)

Why can't I get "PID"?

share|improve this question
2  
Add the condition to your join like $query->join('punbb_topics', 't', 'p.topic_id = t.id AND t.first_post_id = p.id'); –  Paul Nov 11 '14 at 8:10
    
Thanks @Paul, it is working. –  Bhanuji Nov 13 '14 at 10:26
    
@Paul you should post your answer as an actual answer instead of a comment. –  rooby Nov 18 '14 at 9:22

2 Answers 2

up vote 0 down vote accepted

As pointed out by rooby, I will add my comment as an answer. Instead of using ->condition which expects a placeholder to be dynamic, you can add the static condition to your ->join syntax as in

$query = db_select('punbb_posts', 'p')
  ->fields('p', array('id', 'message', 'posted', 'poster_id'));
$query->join('punbb_topics', 't', 'p.topic_id = t.id AND t.first_post_id = p.id');
$query->addField('t', 'posted');
$query->addField('t', 'id');
$query->addField('t', 'subject');
$query->addField('t', 'forum_id');
$query->addField('t', 'first_post_id');

Alternatively, you can of course use Clive's option to use the ->where syntax. However, from my experience adding it to the join should be a bit faster (if that matters in your application).

share|improve this answer
up vote 1 down vote

You need a where():

$query = db_select('punbb_posts', 'p')
->fields('p', array('id', 'message', 'posted', 'poster_id'));
$query->join('punbb_topics', 't', 'p.topic_id = t.id');
$query->addField('t', 'posted');
$query->addField('t', 'id');
$query->addField('t', 'subject');
$query->addField('t', 'forum_id');
$query->addField('t', 'first_post_id');
$query->where('t.first_post_id = p.id');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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