query for records by revision status

[ahwagner]

Include a select filter function, with some predefined recipes.

Example recipe:

• all open revision variants not submitted by a certain user

[ahwagner]

this may require a db view or new endpoint to support these queries, as the simple example query:

POST https://civicdb.org/api/variants/search/

Payload:
{
    "operator": "AND",
    "queries": [
        {
            "field": "suggested_changes_count",
            "condition": {
                "name": "is_greater_than_or_equal_to",
                "parameters": [
                    "new", 1
                ]
            }
        }
    ]
}

Takes 90 seconds to execute (and returns an 8MB response)

[ahwagner]

@susannasiebert would love your input on this one.

[susannasiebert]

Under the hood this executes the following query:

sanitized_status = ActiveRecord::Base.sanitize(parameters.shift)
having_clause = comparison(operation_type, 'COUNT(DISTINCT(suggested_changes.id))')

condition = ::Variant.select('variants.id')
    .joins("LEFT OUTER JOIN suggested_changes ON suggested_changes.moderated_id = variants.id AND suggested_changes.status = #{sanitized_status} AND suggested_changes.moderated_type = 'Variant'")
    .group('variants.id')
    .having(having_clause, *parameters.map(&:to_i)).to_sql

so yeah, it's joining the whole variants table on the suggested changes table which... is not ideal. I'm not quite sure how to improve this though.

[ahwagner]

Duplicate of #50