Query on Custom Fields Table

Discussion regarding Joomla! 3.x Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Post Reply
miriamxdeig
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Dec 18, 2017 10:42 am

Query on Custom Fields Table

Post by miriamxdeig » Mon Jul 16, 2018 12:39 pm

Hi,

I need to make a query to retrieve custom fields for a given user.

So I pass the user Id and I would like to return all fields in column.
This is difficult to me, with a good performing query.

And I think it is due to the way the custom fields table is made:
item_id, field_id, value.
Then I made a query to obtain a pivot table ( rows to columns ):

SELECT
U.id, U.username, U.name,FV.value
MAX(CASE WHEN FV.field_id = 8 THEN FV.value END) AS company,
MAX(CASE WHEN FV.field_id = 29 THEN FV.value END) AS area_manager,
MAX(CASE WHEN FV.field_id = 33 THEN FV.value END) AS company_type
FROM kizgv_users U LEFT JOIN kizgv_fields_values FV ON U.id = FV.item_id

Still that's not at all performing and I got a 500 error if I do it in a medium site .

Can you suggest a smarter way?
Last edited by imanickam on Mon Jul 16, 2018 2:33 pm, edited 1 time in total.
Reason: Per the forum rules, changed the case of the Subject from ALL UPPERCASE to Mixed Case. Please do not repeat this in the future.

Post Reply

Return to “Performance - Joomla! 3.x”