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.