CODEWITHSUNDEEP
ruby-on-rails-3activerecordwhere-clause
Benjamin Crouzier
Benjamin Crouzier

Reputation: 41855

How to properly write a complex where clause in rails with activerecord?

I have a database of Items. Each items belongs_to a User. Each item has a visibility and status field. In order to be searchable, an item must satisfy the following rules:

status must be :available
  AND 
  (visibility must be :everyone
    OR
    (visibility must be :friends AND user_id must be in current_user.friends)
  )

In other words, you see all available and public items, and you see the "private" items of your friends.

How can I retrieve the items that follow this condition ?

I have tried the following:

class Item < ActiveRecord::Base

  belongs_to :user
  attr_accessible :description, :photo, :title, :user_id, :visibility

  #...

  scope :searchable, lambda { |user|
    where('status IN ? AND (visibility IN ? OR (visibility IN ? AND user_id IN ?))',
          [:available, :lent],
          [:everyone],
          [:friends],
          user.friends)
  }
end

And in my controller:

@items = Item.searchable(current_user)

But I have an error:

There is no paren around IN generated clauses

ActiveRecord::StatementInvalid in Items#search

SQLite3::SQLException: near ",": syntax error: SELECT "items".* FROM "items"  WHERE (status IN 'available','lent' AND (visibility IN 'everyone' OR (visibility IN 'friends' AND user_id IN 'foo')))

Upvotes: 0

Views: 3277

Answers (3)

jvnill
jvnill

Reputation: 29599

I personally prefer declaring a class method over scope with a lambda. I think it's easier to read. plus, it's easier to set a default.

def self.with_status(statuses)
  where(status: statuses)
end

def self.visible_to_friends_of(user)
  where('visibility = ? OR (visibility = ? AND user_id IN (?))',
    'everyone',
    'friends',
    user.friends
  )
end

def self.searchable(user)
  with_status([:available, :lent]).visible_to_friends_of(user)
end

Upvotes: 4

Tauqeer Ahmad
Tauqeer Ahmad

Reputation: 540

i think you should use thinking_sphinx gem for this.

define_index do
indexes title
indexes description
indexes uploads.file_file_name, :as => :upload_file_name
indexes uploads.file_content_type, :as => :upload_file_content_type

has :id
has price
has user_id
has created_at
has purchase_count
has images.photo_file_size
has tags.id, :as => :tag_id, :facet => true
has tags.parent_id, :as => :tag_parent_id, :facet => true
has "state='active'", :as => :active, :type => :boolean, :facet => true
has "count(images.id) > 0", :as => :has_image, :type => :boolean
has "sum(uploads.file_file_size)", :as => :total_size, :type => :integer

where "state in ('active', 'pending')"

set_property :delta => true
set_property :morphology => 'stem_en'
set_property :min_prefix_len => 3
set_property :enable_star    => true

end

to satisfy your requirements you can use has condition or where clause. you find out more on Click here

Upvotes: 0

Benjamin Crouzier
Benjamin Crouzier

Reputation: 41855

Well, I didn't know you have to put the paren yourself around the ?s (as you say @MurifoX)

scope :searchable, lambda { |user|
    where('status IN (?) AND (visibility IN (?) OR (visibility IN (?) AND user_id IN (?)))',
          [:available, :lent],
          [:everyone],
          [:friends],
          user.friends)
}

I am still open if there is a better way to implement this behavior.

Upvotes: 2

Related Questions