On 27/01/12 18:48 +0000, Martyn Taylor wrote:
Ack,
Seems a bit strage that AR does not have direct queries for such a
commonly used database feature (i.e. Like)
But this patch does the trick and fixes all errors of this case.
Cheers
Martyn
Thanks for the review Martyn, totally agree, maybe someone will find a
better fix when this is refactored. Patch pushed to staging
On 01/27/2012 04:06 PM, Jason Guiditta wrote:
>ILIKE does not work with mysql, this gets around that fact
>by simple comparing only lower case in all instances.
>
>I think these methods should also be refactored into a single
>method used by all models, by I am going to leave that as a future
>exercise.
>
>Test were run for this patch against, pg, mysql, and sqlite.
>
>All passed.
>---
> src/app/models/catalog.rb | 2 +-
> src/app/models/deployable.rb | 2 +-
> src/app/models/deployment.rb | 4 ++--
> src/app/models/frontend_realm.rb | 2 +-
> src/app/models/hardware_profile.rb | 2 +-
> src/app/models/instance.rb | 2 +-
> src/app/models/pool.rb | 2 +-
> src/app/models/provider_account.rb | 2 +-
> src/app/models/user.rb | 2 +-
> 9 files changed, 10 insertions(+), 10 deletions(-)
>
>diff --git a/src/app/models/catalog.rb b/src/app/models/catalog.rb
>index 69d55f2..00d1e7a 100644
>--- a/src/app/models/catalog.rb
>+++ b/src/app/models/catalog.rb
>@@ -57,7 +57,7 @@ class Catalog< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- includes(:pool).where("catalogs.name ILIKE :search OR pools.name ILIKE
:search", :search => "%#{search}%")
>+ includes(:pool).where("lower(catalogs.name) LIKE :search OR
lower(pools.name) LIKE :search", :search => "%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/deployable.rb b/src/app/models/deployable.rb
>index 4eb5695..95db158 100644
>--- a/src/app/models/deployable.rb
>+++ b/src/app/models/deployable.rb
>@@ -160,7 +160,7 @@ class Deployable< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- where("name ILIKE :search", :search => "%#{search}%")
>+ where("lower(name) LIKE :search", :search =>
"%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/deployment.rb b/src/app/models/deployment.rb
>index 28a2f53..ed9c0d7 100644
>--- a/src/app/models/deployment.rb
>+++ b/src/app/models/deployment.rb
>@@ -469,8 +469,8 @@ class Deployment< ActiveRecord::Base
> if search
> includes(:pool, :provider_accounts => :provider).
> joins("LEFT OUTER JOIN provider_types ON provider_types.id =
providers.provider_type_id").
>- where("pools.name ILIKE :search OR deployments.name ILIKE :search OR
provider_types.name ILIKE :search",
>- :search => "%#{search}%")
>+ where("lower(pools.name) LIKE :search OR lower(deployments.name) LIKE
:search OR lower(provider_types.name) LIKE :search",
>+ :search => "%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/frontend_realm.rb b/src/app/models/frontend_realm.rb
>index 08ab579..032d0fa 100644
>--- a/src/app/models/frontend_realm.rb
>+++ b/src/app/models/frontend_realm.rb
>@@ -53,7 +53,7 @@ class FrontendRealm< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- where("frontend_realms.name ILIKE :search", :search =>
"%#{search}%")
>+ where("lower(frontend_realms.name) LIKE :search", :search =>
"%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/hardware_profile.rb b/src/app/models/hardware_profile.rb
>index 6b7041c..087d2ae 100644
>--- a/src/app/models/hardware_profile.rb
>+++ b/src/app/models/hardware_profile.rb
>@@ -165,7 +165,7 @@ class HardwareProfile< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- where("name ILIKE :search", :search => "%#{search}%")
>+ where("lower(name) LIKE :search", :search =>
"%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/instance.rb b/src/app/models/instance.rb
>index 6afd88b..8aa8401 100644
>--- a/src/app/models/instance.rb
>+++ b/src/app/models/instance.rb
>@@ -475,7 +475,7 @@ class Instance< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- where("instances.name ILIKE :search OR instances.state ILIKE
:search", :search => "%#{search}%")
>+ where("lower(instances.name) LIKE :search OR lower(instances.state) LIKE
:search", :search => "%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/pool.rb b/src/app/models/pool.rb
>index ad9d58c..878c96c 100644
>--- a/src/app/models/pool.rb
>+++ b/src/app/models/pool.rb
>@@ -139,7 +139,7 @@ class Pool< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- includes(:pool_family).where("pools.name ILIKE :search OR
pool_families.name ILIKE :search", :search => "%#{search}%")
>+ includes(:pool_family).where("lower(pools.name) LIKE :search OR
lower(pool_families.name) LIKE :search", :search =>
"%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/provider_account.rb b/src/app/models/provider_account.rb
>index 07e5014..e772f52 100644
>--- a/src/app/models/provider_account.rb
>+++ b/src/app/models/provider_account.rb
>@@ -384,7 +384,7 @@ class ProviderAccount< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- includes(:provider => [:provider_type]).where("provider_accounts.label
ILIKE :search OR providers.name ILIKE :search OR provider_types.name ILIKE :search",
:search => "%#{search}%")
>+ includes(:provider =>
[:provider_type]).where("lower(provider_accounts.label) LIKE :search OR
lower(providers.name) LIKE :search OR lower(provider_types.name) LIKE :search",
:search => "%#{search.downcase}%")
> else
> scoped
> end
>diff --git a/src/app/models/user.rb b/src/app/models/user.rb
>index 8b80ba1..9902464 100644
>--- a/src/app/models/user.rb
>+++ b/src/app/models/user.rb
>@@ -136,7 +136,7 @@ class User< ActiveRecord::Base
>
> def self.apply_search_filter(search)
> if search
>- where("first_name ILIKE :search OR last_name ILIKE :search OR login ILIKE
:search OR email ILIKE :search", :search => "%#{search}%")
>+ where("lower(first_name) LIKE :search OR lower(last_name) LIKE :search OR
lower(login) LIKE :search OR lower(email) LIKE :search", :search =>
"%#{search.downcase}%")
> else
> scoped
> end