MySQL query for getting Joomla users and their profile fields

Question

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

Answer 1

This is simply a join between the two tables

select u.*, p.*
from #__users u
left join #__user_profiles p on p.user_id = u.id

This retrieves all user and associated profile entries. Depending on what rows or profile entries you really need, you can restrict this query with an additional where clause.

If you want the user and associated profile entries in one row, you can combine the profile entries with group_concat

select u.*, group_concat(p.profile_key, '=', p.profile_value)
from #__users u
left join #__user_profiles p on p.user_id = u.id
group by u.id

Answer 2

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.