[PATCH conductor] Filter for provider name on the list of deployments

[Imre Farkas]

This patch fixes the following issue:
https://bugzilla.redhat.com/show_bug.cgi?id=770622

I consider this patch a little bit hackish but unfortunately I haven't found any cleaner way to join the providers table to the deployments table.
Please feel free to NACK it and suggest any better solution you can think of.

[Imre Farkas]

From: Imre Farkas

https://bugzilla.redhat.com/show_bug.cgi?id=770622
---
 src/app/models/deployment.rb |   14 +++++++++++++-
 1 files changed, 13 insertions(+), 1 deletions(-)

diff --git a/src/app/models/deployment.rb b/src/app/models/deployment.rb
index 52880c0..458584a 100644
--- a/src/app/models/deployment.rb
+++ b/src/app/models/deployment.rb
@@ -440,7 +440,19 @@ class Deployment < ActiveRecord::Base

def self.apply_search_filter(search)
     if search
-      includes(:pool).where("pools.name ILIKE :search OR deployments.deployable_xml ILIKE :search OR deployments.name ILIKE :search", :search => "%#{search}%")
+      join_statement = <<-EOS
+        LEFT OUTER JOIN
+        ( SELECT DISTINCT deployment_id, providers.name AS provider_name
+          FROM instances
+          LEFT OUTER JOIN provider_accounts ON (provider_accounts.id = instances.provider_account_id)
+          LEFT OUTER JOIN providers ON (providers.id = provider_accounts.provider_id) ) AS deployments_providers
+        ON (deployments_providers.deployment_id = deployments.id)
+      EOS
+
+      includes(:pool).
+          joins(join_statement).
+          where("pools.name ILIKE :search OR deployments.name ILIKE :search OR deployments_providers.provider_name ILIKE :search",
+                :search => "%#{search}%")
     else
       scoped
     end

[Matt Wagner]

On Mon, Jan 09, 2012 at 01:13:36PM +0100, [e-mail] wrote:
> From: Imre Farkas
> https://bugzilla.redhat.com/show_bug.cgi?id=770622
>
> src/app/models/deployment.rb |   14 +++++++++++++-
>  1 files changed, 13 insertions(+), 1 deletions(-)
>
> diff --git a/src/app/models/deployment.rb b/src/app/models/deployment.rb
> index 52880c0..458584a 100644
> --- a/src/app/models/deployment.rb
> +++ b/src/app/models/deployment.rb
> @@ -440,7 +440,19 @@ class Deployment < ActiveRecord::Base
>
> def self.apply_search_filter(search)
>      if search
>  includes(:pool).where("pools.name ILIKE :search OR deployments.deployable_xml ILIKE :search OR deployments.name ILIKE :search", :search => "%#{search}%")
>  join_statement = <<-EOS
>    LEFT OUTER JOIN
>    ( SELECT DISTINCT deployment_id, providers.name AS provider_name
>      FROM instances
>      LEFT OUTER JOIN provider_accounts ON (provider_accounts.id = instances.provider_account_id)
>      LEFT OUTER JOIN providers ON (providers.id = provider_accounts.provider_id) ) AS deployments_providers
>    ON (deployments_providers.deployment_id = deployments.id)
>  EOS
>  includes(:pool).
>      joins(join_statement).
>      where("pools.name ILIKE :search OR deployments.name ILIKE :search OR deployments_providers.provider_name ILIKE :search",
>            :search => "%#{search}%")
> else
>      scoped
>    end

I'm going to go ahead and ACK this, because it does work and improve
functionality over what we have now.

It looks like Arel does provide a way of doing this sort of thing
without resorting to raw SQL, between https://gist.github.com/487749
and https://gist.github.com/935641

But in this case, I'm honestly not sure that the above would be any
cleaner than just doing straight SQL. Plus, I think that we want 
to implement a "real" (non-SQL) search engine at some point in the 
future anyway.

-- Matt

[Jan Provazník]

On 01/09/2012 01:13 PM, [e-mail] wrote:
> From: Imre Farkas
> https://bugzilla.redhat.com/show_bug.cgi?id=770622
>
> src/app/models/deployment.rb |   14 +++++++++++++-
>   1 files changed, 13 insertions(+), 1 deletions(-)
>
> diff --git a/src/app/models/deployment.rb b/src/app/models/deployment.rb
> index 52880c0..458584a 100644
> --- a/src/app/models/deployment.rb
> +++ b/src/app/models/deployment.rb
> @@ -440,7 +440,19 @@ class Deployment<  ActiveRecord::Base
> def self.apply_search_filter(search)
>   if search
>  includes(:pool).where("pools.name ILIKE :search OR deployments.deployable_xml ILIKE :search OR deployments.name ILIKE :search", :search =>  "%#{search}%")
>  join_statement =<<-EOS
>    LEFT OUTER JOIN
>    ( SELECT DISTINCT deployment_id, providers.name AS provider_name
>      FROM instances
>      LEFT OUTER JOIN provider_accounts ON (provider_accounts.id = instances.provider_account_id)
>      LEFT OUTER JOIN providers ON (providers.id = provider_accounts.provider_id) ) AS deployments_providers
>    ON (deployments_providers.deployment_id = deployments.id)
>  EOS
>  includes(:pool).
>      joins(join_statement).
>      where("pools.name ILIKE :search OR deployments.name ILIKE :search OR deployments_providers.provider_name ILIKE :search",
>            :search =>  "%#{search}%")
> else
>   scoped
> end

if you add association to Deployment mdoel:
"has_many :provider_accounts, :through => :instances"

then above query could be:
Deployment.includes(:pool, :provider_accounts => :provider).where(orig 
where str)

(Since deployment has many instances, there can be duplicate rows 
returned from DB, but AR handles this itself)

Jan