MySQL query for getting Joomla users and their profile fields
joomla, mysql
Solution
In the end I decided to go for a pivot table approach
example:
SELECT
#__users.*,
MAX(CASE WHEN profile_key = "example.firstname" THEN profile_value END) AS FirstName,
MAX(CASE WHEN profile_key = "example.lastname" THEN profile_value END) AS LastName,
MAX(CASE WHEN profile_key = "example.company" THEN profile_value END) AS Company
FROM #__users LEFT JOIN #__user_profiles ON #__users.id = #__user_profiles.user_id
GROUP BY #__users.id
This allows me to have all the extra user data in one row so I can easily order and filter.
Problem
I am creating a user profile plugin in Joomla 2.5 to extend the standard user fields. The issue is I need an efficient way to retrieve the users (along with the extra fields) via a MySQL query. Currently the best method I can think of is querying the #__user_profiles table and processing that data to determine the extra fields to load and then producing a query that creates a separate join on the #__user_profiles table for each extra user field. Obviously that isn't very efficient and on a large user base the query is quite slow. Is there a better way to combine extra user fields that are separate records in another table into one query? EDIT: I have an external script that needs to grab all the users and their extended fields so I need to combine the #_users and #_user_profiles tables