sum depend in the tow custom filed

[ashrafalzyoud]

depend in same issue #228

my_value_471 = CustomValue.where("custom_field_id = 471 AND value ='259'").pluck(:customized_id)

my_value_472 = CustomValue.where("custom_field_id = 472 AND value ='1'").pluck(:customized_id)

self.custom_field_values = { 463=> (my_value_471 & my_value_472).uniq.size.to_i.to_s }

where i can added project id and tracker id

[picman]

"were i can added" ? If nobody understands your question, you can hardly expect any help... Try to formulate it better.

[ashrafalzyoud]

Thx for your answer

In this example the results be in all tracker and all project Because that I need selection issue Project _id and tracker _id in formula

depend in same issue #228

my_value_471 = CustomValue.where(" tracker_id= self tracker_id AND project_id=self project_id AND custom_field_id = 471 AND value ='259'").pluck(:customized_id)

[picman]

if self.project_id == xxx and self.tracker_id = yyy
...

[ashrafalzyoud]

If we put the if condition out side formula The value 471= will search in all projects and all tracker

The condition must be inside Custom.value where()

[picman]

CustomValue.joins('JOIN issues ON issue.id == custom_values.customized_id).where(custom_values: { custom_field_id: 471, value: '259', customized_type: 'Issue' }, issues: { project_id: x, tracker_id: z })

[ashrafalzyoud]

thx for your answer and help can i added also another custom field also to be like this CustomValue.joins('JOIN issues ON issue.id == custom_values.customized_id).where(custom_values: { custom_field_id: 471, value: '259',custom_field_id = 472 AND value ='1', customized_type: 'Issue' }, issues: { project_id: x, tracker_id: z })

[picman]

No, I think that you would have to use or:

CustomValue.joins('JOIN issues ON issue.id == custom_values.customized_id).where(custom_values: { custom_field_id: 471, value: '259', customized_type: 'Issue' }, issues: { project_id: x, tracker_id: z }).or((custom_values: { custom_field_id: 472, value: '1', customized_type: 'Issue' }, issues: { project_id: x, tracker_id: z }))

[ashrafalzyoud]

thx for your corruption my last code like this plz check it

@issue.custom_field_values = { '66' => CustomValue.joins('JOIN issues ON issue.id == custom_values.customized_id).where(custom_values: { custom_field_id: 496, value: self.custom_field_value(496), customized_type: 'Issue' }, issues: { project_id: self.project_id, tracker_id: self.tracker_id }).or((custom_values: { custom_field_id: 497, value: self.custom_field_value(497), customized_type: 'Issue' }, issues: { project_id: self.project_id, tracker_id: self.tracker_id })).all.sum{|c| c.custom_field_value(68).to_f}.to_f}

when im excute it its give me error

Workflow script executable before saving observable object contains error: (eval):8: syntax error, unexpected constant, expecting ')' ...e(496), customized_type: 'Issue' }, issues: { project_id: y,... ... ^~~~~ (eval):8: syntax error, unexpected constant, expecting end-of-input ...e(497), customized_type: 'Issue' }, issues: { project_id: y,... ... ^~~~~

[ashrafalzyoud]

@picman

[picman]

id1 = 496
val1 = 1
id2 = 497
val2 = 2
scope = CustomValue.where(custom_values: { custom_field_id: id1,
                                       value: val1,
                                       customized_type: 'Issue' },
                      issues: { project_id: self.project_id,
                                tracker_id: self.tracker_id })
               .or(CustomValue.where(custom_values: { custom_field_id: id2,
                                    value:  val2,
                                    customized_type: 'Issue' },
                   issues: { project_id: self.project_id,
                             tracker_id: self.tracker_id }))
               .joins('JOIN issues ON issues.id = custom_values.customized_id')
sum = scope.sum('custom_values.value')
@issue.custom_field_values = { '66' => sum }

[ashrafalzyoud]

thx for your answer but the sum value need it in another custom filed common 68 my case i have 1000 issues there three custom field custom_field_id(1) > key valuelist custom_field_id(2) > key value list custom_field_id(3) > float value

i need choose which issues have cfs(1) value A and cfs(2) value (100) issues have cfs(1) value (A) 130 issues issues have cfs(2) value (100) 170 issues issues have both 50 issue then total value in issues selected (50) must sum all cfs(3) in issues

id1 = 496 val1 = 1 id2 = 497 val2 = 2 id3 = 498

scope = CustomValue.where(custom_values: { custom_field_id: id1,
                                       value: val1,
                                       customized_type: 'Issue' },
                      issues: { project_id: self.project_id,
                                tracker_id: self.tracker_id })
               .or(CustomValue.where(custom_values: { custom_field_id: id2,
                                    value:  val2,
                                    customized_type: 'Issue' },
                   issues: { project_id: self.project_id,
                             tracker_id: self.tracker_id }))
               .joins('JOIN issues ON issues.id = custom_values.customized_id')
sum = scope.sum('custom_values.value') // total value for id3 //`.all.sum{|c| c.custom_field_value(68).to_f}.to_f}

@issue.custom_field_values = { '66' => sum }

`

[ashrafalzyoud]

im try this code depend in your formula but its not working

id1 = 496
val1 = 2015
id2 = 497
val2 = 1
scope = CustomValue.where(custom_values: { custom_field_id: id1,
                                       value: val1,
                                       customized_type: 'Issue' },
                      issues: { project_id: self.project_id,
                                tracker_id: self.tracker_id })
               .and(CustomValue.where(custom_values: { custom_field_id: id2,
                                    value:  val2,
                                    customized_type: 'Issue' },
                   issues: { project_id: self.project_id,
                             tracker_id: self.tracker_id }))
               .joins('JOIN issues ON issues.id = custom_values.customized_id')
sum = scope.all.sum{|c| c.custom_field_value(68).to_f}.to_f
num = scope.uniq.size.to_i.to_s
@issue.custom_field_values = { '66' => sum }
@issue.custom_field_values = { '67' => num }

[picman]

I'd modify those two lines as follows:

sum = scope.all.sum { |v| v.value.to_f }
num = scope.all.size

Add some log messages to see what doesn't work as expected, e.g.:

Rails.logger.info ">>> #{scope.to_sql}"
Rails.logger.info ">>> #{sum}"
Rails.logger.info ">>> #{num}"

[ashrafalzyoud]

sum = scope.all.sum { |v| v.value.to_f } what value its sum in your example ???

i need value of sum = scope.all.sum{|c| c.custom_field_value(68).to_f}.to_f

[picman]

The code scope.all wil return an array of instances of CustomValue class. This class has no method custom_field_value, just the property value.

[ashrafalzyoud]

ok then how i can choose the value i need sum cfs[68] in your formula we need like this my case my case i have 1000 issues there three custom field custom_field_id(1) > key valuelist custom_field_id(2) > key value list custom_field_id(3) > float value

i need choose which issues have cfs(1) value A and cfs(2) value (100) issues have cfs(1) value (A) 130 issues issues have cfs(2) value (100) 170 issues issues have both 50 issue then total value in issues selected (50) must sum all cfs(3) in issues

[ashrafalzyoud]

im also try this code not working

if project.present?
 @list1 = Issue.joins(:custom_values).where("tracker_id = 'self.tracker_id' AND project_id = 'self.project_id' AND custom_field_id = '496' AND value = '#{custom_field_value(496).to_s}'").pluck(:id)
 @float_value1 = CustomValue.where("customized_id IN (#{@list1.join(',').to_s}) AND custom_field_id = '68'").pluck(:value)

 @list2 = Issue.joins(:custom_values).where("tracker_id = 'self.tracker_id' AND project_id = 'self.project_id' AND custom_field_id = '497' AND value = '#{custom_field_value(497).to_s}'").pluck(:id)
 @float_value2 = CustomValue.where("customized_id IN (#{@list2.join(',').to_s}) AND custom_field_id = '68'").pluck(:value)

      @float_value3 = @float_value2 & @float_value1

        @issue.custom_field_values = { '498' => @float_value3.uniq.size.to_i.to_s}
        @issue.custom_field_values = { '66' => @float_value3.inject { |sum, n| sum.to_f + n.to_f }.round(2).to_f }
end

[picman]

Stop posting here and spend more time with analyzing and programming. All custom values are stored in _customvalues table. it's not so difficult to sum values according to _custom_filedid, _customizedid and value.