0

I have the following SQL query and need to translate it to the drupal API:

SELECT
  *,
  (SELECT COUNT(*)
    FROM newsletter
    WHERE email = pupils.email
  ) AS newsletter_flag
FROM 
  memberdata.pupils
LEFT JOIN
  memberdata.pupil_profiles
ON
  memberdata.pupils.id = memberdata.pupil_profiles."pupils.id"
WHERE
  pupils.status != 'u'
ORDER BY
  pupils.timestamp DESC
LIMIT 10;

Background: There is a table with pupils (some basic info including mail adress) and a table newsletter containing the email addresses of pupils that registered for the newsletter. I need a list of all pupil with their fields and additionally the field newsletter_flag, indicating if the pupils email adress is in the newsletter table or not. Additionally I need a join to another table with primary foreign key. I've come this far:

$query = Database::getConnection('default', 'for_migration')
->select('memberdata.pupils', 'pupils')
->fields('pupils', array('id', 'firstname', 'lastname', 'birthday', 'gender'))
->condition('status', 'u', '!=')
->range(0, 10)
->orderBy('pupils.timestamp', 'DESC');

$alias = $query->leftjoin('newsletter', 'nl', 'pupils.email = nl.email');
$query->addExpression("COUNT($alias.email)", 'newsletter_flag');

But it does not what I want (the pupil_profiles join is still missing of course, but the newsletter flag is the bigger problem I guess). The source database is a PostgreSQL one and the query itself is part of a Migration subclass, in which a MigrateSourceSQL is used as source. Therefore the query needs to be a SelectQueryInterface.

1
  • 1
    For complicated queries db_query is a better option a lot of times. – mikeytown2 Nov 28 '14 at 21:39
0

Ok, figured it out but do not like it:

$query = Database::getConnection('default', 'for_migration')
   ->select('memberdata.pupils', 'p')
   ->fields('p', array('id', 'firstname', 'lastname', 'birthday', 'gender'))
   ->condition('status', 'u', '!=')
   ->range(0, 10);
$query->addExpression("(SELECT COUNT(*) FROM newsletter WHERE email = p.email)", 'newsletter_flag');

There should be a smoother way for the newsletter_flag problem.

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.