504
edits
Changes
→Customized SQL query to get all Kunena users
Line 341:
Line 341:
−
userid AS id,+ name AS username,+ email,+ STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate+
userid AS id,+ name AS username,+ 'no_email' AS email,+ STR_TO_DATE('01,01,1970,12,0','%d,%m,%Y,%h,%i') AS registerDate+ AND (email IS NULL OR email = '')+ AND userid NOT IN(+ SELECT DISTINCT userid AS id+ FROM gvrsi_kunena_messages + WHERE userid NOT IN(SELECT id FROM gvrsi_users)+ AND email IS NOT NULL AND email <> ''+ GROUP BY id+ )+
<source lang="SQL">
<source lang="SQL">
−--- Main Joomla! users table (original query in script)
+SELECT id, username, email, registerDate FROM gvrsi_users
+--- Main Joomla! users table
+--- (original query in script)
+SELECT
+ id,
+ username,
+ email,
+ registerDate,
+ signature,
+ location,
+ websiteurl,
+ birthdate,
+ avatar,
+ moderator
+FROM gvrsi_users
+INNER JOIN gvrsi_kunena_users ON gvrsi_kunena_users.userid = gvrsi_users.id
+UNION
UNION
+
--- Query to fetch users not present in main Joomla! table that have an email address defined
--- Query to fetch users not present in main Joomla! table that have an email address defined
−--- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00)
+--- Registration date does not exist so we put that most old date (first posted message)
−SELECT DISTINCT
+SELECT
− userid,
− name,
− SUBSTRING_INDEX(GROUP_CONCAT(email), ',',1) AS email,
− FROM_UNIXTIME(MIN(time)) AS registerDate,
+ NULL AS signature,
+ NULL AS location,
+ NULL AS website,
+ NULL AS birthdate,
+ NULL AS avatar,
+ 0 AS moderator
FROM gvrsi_kunena_messages
FROM gvrsi_kunena_messages
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
−AND email IS NOT NULL AND email <> ''
+ AND email IS NOT NULL AND email <> ''
−GROUP BY id, username
+GROUP BY userid, name
+UNION
UNION
+
--- Query to fetch users not present in main Joomla! table that does not have an email address defined
--- Query to fetch users not present in main Joomla! table that does not have an email address defined
--- Email address is set to no_email to allow Discourse account creation
--- Email address is set to no_email to allow Discourse account creation
−--- Registration date does not exist so we put that to UNIX EPOCH (01/01/1970, 0h00)
+--- Registration date does not exist so we put that most old date (first posted message)
−SELECT DISTINCT
+SELECT
− userid,
− name,
− 'no_email' AS email,
− FROM_UNIXTIME(MIN(time)) AS registerDate,
+ NULL AS signature,
+ NULL AS location,
+ NULL AS website,
+ NULL AS birthdate,
+ NULL AS avatar,
+ 0 AS moderator
FROM gvrsi_kunena_messages
FROM gvrsi_kunena_messages
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
WHERE userid NOT IN(SELECT id FROM gvrsi_users)
− AND (email IS NULL OR email = '')
− AND userid NOT IN(
− SELECT DISTINCT userid
− FROM gvrsi_kunena_messages
− WHERE userid NOT IN(SELECT id FROM gvrsi_users)
− AND email IS NOT NULL AND email <> ''
− GROUP BY userid
− )
−GROUP BY id, username
+GROUP BY userid, name
ORDER BY id;
ORDER BY id;
</source>
</source>
−===PostgreSQL query to fix quote blocks===
===PostgreSQL query to fix quote blocks===