pdoUsers join external table
Is it possible with pdoUsers to join an external table.
In MODX modx_user_attributes I plan to store the ISO code for a country.
On display with pdoUsers I'd like to show the country long name joining my ext_countries on the ISO code.
Thanks
In MODX modx_user_attributes I plan to store the ISO code for a country.
On display with pdoUsers I'd like to show the country long name joining my ext_countries on the ISO code.
Thanks
Комментарии: 3
[[pdoUsers? &users=`[[+modx.user.id]]`
&innerJoin=`{"modUserGroupMember":{"alias":"modUserGroupMember","on":"modUser.id = modUserGroupMember.member"}, "modUserGroup":{"alias":"modUserGroup", "on":"modUserGroupMember.user_group = modUserGroup.id"}}`
&select=`{"modUserGroup":{"group_name": "modUserGroup.name"}}`
]]
This join 2 tables with modUser and get only needed column. You should do the same.
pdoUsers is already joining modUserProfile.
You can output all available placeholders by setting empty &tpl:
You can output all available placeholders by setting empty &tpl:
[[!pdoUsers?
&tpl=``
]]
Sorry I didn't explain it properly. This page is for all users, not just current user, so will Inner Join work or do I need an OuterJoin?
I tried
Could not process query, error #1054: Unknown column 'CountryName' in 'field list'
0.0003600: SQL prepared «SELECT SQL_CALC_FOUND_ROWS `modUser`.`id`, `modUser`.`username`, `modUser`.`password`, `modUser`.`cachepwd`, `modUser`.`class_key`, `modUser`.`active`, `modUser`.`remote_key`, `modUser`.`remote_data`, `modUser`.`hash_class`, `modUser`.`salt`, `modUser`.`primary_group`, `modUser`.`session_stale`, `modUser`.`sudo`, `modUserProfile`.`id`, `modUserProfile`.`internalKey`, `modUserProfile`.`fullname`, `modUserProfile`.`email`, `modUserProfile`.`phone`, `modUserProfile`.`mobilephone`, `modUserProfile`.`blocked`, `modUserProfile`.`blockeduntil`, `modUserProfile`.`blockedafter`, `modUserProfile`.`logincount`, `modUserProfile`.`lastlogin`, `modUserProfile`.`thislogin`, `modUserProfile`.`failedlogincount`, `modUserProfile`.`sessionid`, `modUserProfile`.`dob`, `modUserProfile`.`gender`, `modUserProfile`.`address`, `modUserProfile`.`country`, `modUserProfile`.`city`, `modUserProfile`.`state`, `modUserProfile`.`zip`, `modUserProfile`.`fax`, `modUserProfile`.`photo`, `modUserProfile`.`comment`, `modUserProfile`.`website`, `modUserProfile`.`extended`, `CountryName` AS `CountryName` FROM `modx_users` AS `modUser` JOIN `modx_user_attributes` `modUserProfile` ON modUser.id = modUserProfile.internalKey WHERE ( `modUser`.`active` = 1 AND `modUserProfile`.`blocked` = '0' ) GROUP BY modUser.id ORDER BY modUser.id ASC LIMIT 10 „
My table is o_country and the 2 relevant fields are ISOCode2 and CountryName
I really am using modUserProfile.zip to store the country code.
I tried
[[!pdoUsers?
&innerJoin=`{"o_country":{"alias":"o_country","on":"modUserProfile.zip = o_country.ISOCode2"}}`
&select=`{"o_country":{"CountryName": "CountryName"}}`
&showLog=`1`
]]
but this came back with errorCould not process query, error #1054: Unknown column 'CountryName' in 'field list'
0.0003600: SQL prepared «SELECT SQL_CALC_FOUND_ROWS `modUser`.`id`, `modUser`.`username`, `modUser`.`password`, `modUser`.`cachepwd`, `modUser`.`class_key`, `modUser`.`active`, `modUser`.`remote_key`, `modUser`.`remote_data`, `modUser`.`hash_class`, `modUser`.`salt`, `modUser`.`primary_group`, `modUser`.`session_stale`, `modUser`.`sudo`, `modUserProfile`.`id`, `modUserProfile`.`internalKey`, `modUserProfile`.`fullname`, `modUserProfile`.`email`, `modUserProfile`.`phone`, `modUserProfile`.`mobilephone`, `modUserProfile`.`blocked`, `modUserProfile`.`blockeduntil`, `modUserProfile`.`blockedafter`, `modUserProfile`.`logincount`, `modUserProfile`.`lastlogin`, `modUserProfile`.`thislogin`, `modUserProfile`.`failedlogincount`, `modUserProfile`.`sessionid`, `modUserProfile`.`dob`, `modUserProfile`.`gender`, `modUserProfile`.`address`, `modUserProfile`.`country`, `modUserProfile`.`city`, `modUserProfile`.`state`, `modUserProfile`.`zip`, `modUserProfile`.`fax`, `modUserProfile`.`photo`, `modUserProfile`.`comment`, `modUserProfile`.`website`, `modUserProfile`.`extended`, `CountryName` AS `CountryName` FROM `modx_users` AS `modUser` JOIN `modx_user_attributes` `modUserProfile` ON modUser.id = modUserProfile.internalKey WHERE ( `modUser`.`active` = 1 AND `modUserProfile`.`blocked` = '0' ) GROUP BY modUser.id ORDER BY modUser.id ASC LIMIT 10 „
My table is o_country and the 2 relevant fields are ISOCode2 and CountryName
I really am using modUserProfile.zip to store the country code.
Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.