sql query - table with several custom field values
Added by Matias Fusaro about 8 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) about 8 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 about 8 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) about 8 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 about 8 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 about 8 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. almost 8 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 over 7 years ago
Did you find another way to list those custom fields? :)