Project

General

Profile

sql query - table with several custom field values

Added by Matias Fusaro over 7 years ago

I want to run a SQL query to the database redmine to bring several values of custom fields.
I can explain how serious?


Replies (7)

RE: sql query - table with several custom field values - Added by Martin Denizet (redmine.org team member) over 7 years ago

Hello,
See and example there: http://stackoverflow.com/questions/20819165/how-to-change-custom-fields-sql-query-on-redmine
Copy of the code found on stakeoverflow below:

SELECT 
    login, firstname, lastname, value
FROM 
    users
LEFT JOIN 
    custom_values ON custom_values.customized_id = users.id 
                  AND customized_type = 'Principal'
WHERE 
    custom_field_id = (SELECT id FROM custom_fields 
                       WHERE type = 'UserCustomField' 
                         AND name = 'MOBILE') 
    AND value IS NOT NULL 
    AND value != ''

However, if it's to use in Ruby code, you will be better off using ActiveRecord.
Cheers,

RE: sql query - table with several custom field values - Added by Matias Fusaro over 7 years ago

Thanks, fails to express what I need.
I have 5 custom fields and need to get a table with these fields more fields of the issues table (id, start_date, etc).

Do not know how to check more than one custom field at a time.

excuse the translation, I'm using google translate.

Example table

issues.id | issues.start_date | custom_field_1 | custom_field_2 | custom_field_3 | custom_field_4 |custom_field_5 | | | | | | | |

RE: sql query - table with several custom field values - Added by Martin Denizet (redmine.org team member) over 7 years ago

By table you mean HTML table, not MySQL table, right?

RE: sql query - table with several custom field values - Added by Matias Fusaro over 7 years ago

Mysql Table.

I am using a web application that makes graphs from SQL statements (re: dash) I need to plot a table with several custom field.

RE: sql query - table with several custom field values - Added by Matias Fusaro over 7 years ago

I need the same data (image) but get them from a SQL query.

Captura.PNG (32.2 KB) Captura.PNG img-example

RE: sql query - table with several custom field values - Added by Linas A. over 7 years ago

hi
maybe it will work in following principle?

SELECT
issues.id, issues.start_date, c1.custom_field_1, c2.custom_field_2, c3.custom_field_3, ...
FROM issues
LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=your_custom_field_1_id
LEFT JOIN custom_values c2 ON c2.customized_id=issues.id AND c2.custom_field_id=your_custom_field_2_id
LEFT JOIN custom_values c3 ON c3.customized_id=issues.id AND c3.custom_field_id=your_custom_field_3_id
...

(your_custom_field_x_id is taken from custom_fields table)

RE: sql query - table with several custom field values - Added by Brenda Haro about 7 years ago

Did you find another way to list those custom fields? :)

    (1-7/7)