Project

General

Profile

Forums » Help »

Filtering a custom query in a self-made plugin

Added by Stefan Pichler almost 8 years ago

Hi!

I would like to make a pie chart that shows the ratio of issues that have a specific custom field set. For that I had a plugin made with the following statement in the controller:

@issues = Issues.select("custom_values.value AS cv, count(*) AS cnt, custom_values.custom_field_id AS cfi")
.joins("JOIN projects ON issues.project_id = projects.id LEFT JOIN custom_values ON custom_values.customized_type = 'Issue' AND issues.id = custom_values.customized_id")
.where("(project_id = #{@project.id} or (projects.lft > #{@project.lft} AND projects.rgt < #{@project.rgt})) AND custom_field_id = '#{@listId}'").group("custom_values.value")

Issues is a subclass of ActiveRecord::Base. This actually works fine. Now I would like to be able to filter the query like one does when using the gantt chart or the issue list. I figured out how to render the filter-form, but I'm just not able to apply the filter to my custom query above.

I tried a lot of things. Here just some examples

  • deriving from Query instead of ActiveRecord::Base and using build_from_params
  • deriving from Query and implementing a custom made build_from_params
  • using IssueQuery instead of my own model "Issues" and apply select, join,... and build_from_params on that one

Caon somebody help me on this?

Regards,
Stefan


Replies (2)

RE: Filtering a custom query in a self-made plugin - Added by Stefan Pichler almost 8 years ago

Found it!

@query = IssueQuery.new(:name => "_")
@query.project = @project
if params[:set_filter]
    @query.build_from_params(params)
end

@issues = Issues.select("custom_values.value AS cv, count(*) AS cnt, custom_values.custom_field_id AS cfi")
        .joins("JOIN projects ON issues.project_id = projects.id LEFT JOIN custom_values ON custom_values.customized_type = 'Issue' AND issues.id = custom_values.customized_id")
        .where(@query.statement)
        .where("(project_id = #{@project.id} or (projects.lft > #{@project.lft} AND projects.rgt < #{@project.rgt})) AND custom_field_id = '#{@listId}'").group("custom_values.value")

I suppose that the where clause could be adjusted because the filtering for subprojects would already be included in @query.statement.

RE: Filtering a custom query in a self-made plugin - Added by Stefan Pichler almost 8 years ago

And of course one should use paramerized queries to avoid sql injection:

@query = IssueQuery.new(:name => "_")
@query.project = @project
if params[:set_filter]
    @query.build_from_params(params)
end

@issues = Issues.select("custom_values.value AS cv, count(*) AS cnt, custom_values.custom_field_id AS cfi")
        .joins("JOIN projects ON issues.project_id = projects.id LEFT JOIN custom_values ON custom_values.customized_type = 'Issue' AND issues.id = custom_values.customized_id")
        .where(@query.statement)
        .where("(project_id = ? or (projects.lft > ? AND projects.rgt < ?)) AND custom_field_id = ?", @project.id, @project.lft, @project.rgt, @listId).group("custom_values.value")
    (1-2/2)