How to change custom fields sql query on redmine

Question

I use zapier to send sms to users when a new issue is created on Redmine. I want to make it easy for users and sophisticated. To do so; I've created a custom field on USER section which is named MOBILE. In Custom Fields menu, the user writes his/her phone number in this field.

I also created a custom field on ISSUES section. This custom field brings user list to ISSUES. User list custom field as you know is default redmine feature in ISSUES section for redmine 2.4.x.

So here is my question; I want user list custom field feature to bring user MOBILE numbers which I've created field on users accounts page. Current query brings name and surname and user to. I checked apps-models-custom_field.rb however no luck. I couldn't find a solution. I think inner join with custom_values table could solve my problem but I do not know how to do it, actually I don't know which file contains this queries for custom field user list.

Default user list feature of custom fields prints User firstname and lastname on ISSUES page also it just brings user id on the xml api. So all I want to do is get user custom field content with their user id on the xml.

I hope I've made myself clear.

What are your suggestions?

marc_s · Accepted Answer · 2015-10-01 20:49:10Z

2

You won't be able to find SQL queries in Redmine code as Redmine uses Rails' ActiveRecond framework to build SQL queries dynamically.

As I understand the query, you are looking for, should look like:

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 != ''

Hope, this helps.

edited Oct 1, 2015 at 20:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 28, 2013 at 22:27

Andriy Lesyuk

4822 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

orko Over a year ago

Yes thank you. This is the query but i do not know how to run it on custom fields. There is an article states that writing sql queries right into custom field text box however it is not so clear. Do you know how can i make this query work?

Andriy Lesyuk Over a year ago

Where did you find such article? It's not possible, as far as I know.

orko Over a year ago

It turns out that it was a feature suggestion.

Collectives™ on Stack Overflow

How to change custom fields sql query on redmine

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related