$c = $this->modx->newQuery('modUser');
$c->leftJoin('modUserProfile', 'Profile');
$c->leftJoin('GoodNewsGroupMember', 'GroupMember', 'GroupMember.member_id = modUser.id');
$c->leftJoin('GoodNewsCategoryMember', 'CategoryMember', 'CategoryMember.member_id = modUser.id');
$c->where(array(
'modUser.active' => true,
'Profile.blocked' => false,
'GroupMember.goodnewsgroup_id:IN' => $groups,
));
$c->orCondition(array(
'CategoryMember.goodnewscategory_id:IN' => $categories,
));
$c->select($this->modx->getSelectColumns('modUser', 'modUser', '', array('id')));
//$c->select($this->modx->getSelectColumns('modUserProfile', 'Profile', '', array('blocked')));
//$users = $this->modx->getCollection('modUser', $c);
$users = $this->modx->getIterator('modUser', $c);
foreach ($users as $user) {
$userID = $user->get('id');
$recipients[$userID] = array(self::GON_USER_NOT_YET_SENT,0);
}
This question has been answered by gadgetto. See the first response.
MODX Staff
Without knowing the details of your custom tables, my first inclination is to say, don't try to grab that many records at once. My second is to make sure the fields being used in your joins/where conditions are properly indexed. Finally, you don't want to load all those objects. If you ultimately want just an array of id's, you should only select that column in your query and then use PDOStatement->fetchAll(PDO::FETCH_COLUMN) instead of any xPDO methods that load the data into objects.
Any hints on how to improve the performance of this code part?
Without knowing the details of your custom tables, my first inclination is to say, don't try to grab that many records at once.
My second is to make sure the fields being used in your joins/where conditions are properly indexed.
<object class="GoodNewsGroupMember" table="goodnews_group_members" extends="xPDOSimpleObject">
<field key="goodnewsgroup_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
<field key="member_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
<index alias="goodnewsgroup_id" name="goodnewsgroup_id" primary="false" unique="false" type="BTREE">
<column key="goodnewsgroup_id" length="" collation="A" null="false" />
</index>
<index alias="member_id" name="member_id" primary="false" unique="false" type="BTREE">
<column key="member_id" length="" collation="A" null="false" />
</index>
<aggregate alias="GoodNewsGroup" class="GoodNewsGroup" local="goodnewsgroup_id" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="User" class="modUser" local="member_id" foreign="id" cardinality="one" owner="foreign" />
</object>
Finally, you don't want to load all those objects. If you ultimately want just an array of id's, you should only select that column in your query and then use PDOStatement->fetchAll(PDO::FETCH_COLUMN) instead of any xPDO methods that load the data into objects.
☆ A M B ☆
Do you really want blocked and inactive users in the array? It appears that you'll get them with the or condition. Maybe I'm missing something.
$c->where(array(
'modUser.active' => true,
'Profile.blocked' => false,
'GroupMember.goodnewsgroup_id:IN' => $groups,
));
$c->orCondition(array(
'CategoryMember.goodnewscategory_id:IN' => $categories,
));
The pdoTools library is pretty fast. You might find it interesting to see how the pdoUsers snippet does it.
$c->prepare(); echo $c->toSQL();
WHERE
modUser.Active => true,
AND
Profile.blocked => false
AND (
'GroupMember.goodnewsgroup_id:IN' => $groups,
OR
'CategoryMember.goodnewscategory_id:IN' => $categories,
)
// Select subscribers based on groups/categories
$tblUsers = $this->modx->getTableName('modUser');
$tblUserAttributes = $this->modx->getTableName('modUserProfile');
$tblGoodNewsGroupMember = $this->modx->getTableName('GoodNewsGroupMember');
$tblGoodNewsCategoryMember = $this->modx->getTableName('GoodNewsCategoryMember');
$groupslist = implode(',', $groups);
$categorieslist = implode(',', $categories);
$sql = "SELECT DISTINCT {$tblUsers}.id
FROM {$tblUsers}
LEFT JOIN {$tblUserAttributes} ON {$tblUserAttributes}.internalKey = {$tblUsers}.id
LEFT JOIN {$tblGoodNewsGroupMember} ON {$tblGoodNewsGroupMember}.member_id = {$tblUsers}.id
LEFT JOIN {$tblGoodNewsCategoryMember} ON {$tblGoodNewsCategoryMember}.member_id = {$tblUsers}.id
WHERE ({$tblGoodNewsGroupMember}.goodnewsgroup_id IN ({$groupslist}) OR {$tblGoodNewsCategoryMember}.goodnewscategory_id IN ({$categorieslist}))
AND {$tblUsers}.active = 1
AND {$tblUserAttributes}.blocked = 0";
$query = $this->modx->query($sql);
if ($query) {
$users = $query->fetchAll(PDO::FETCH_COLUMN);
}
// Initialize each userid with status not_yet_sent + timestamp placeholder 0
foreach ($users as $id) {
$recipients[$id] = array(self::GON_USER_NOT_YET_SENT,0);
}
I'm not a whiz at xPDO queries, but you're basically saying, "Give me the records where these three conditions are true OR this other condition is true."
Check the SQL and look at the parentheses in the WHERE clause: