1 Reply Latest reply on May 16, 2013 8:37 AM by DecisionPoint Keith Gable

    Advanced Queries group :conditions problem

      My query looks like below:

      cond1 = {
        :conditions => { 
          {
            :name => 'id', 
            :op => 'IN' 
          } => ids, 
          :type => type
        }, 
        :op => 'OR'
      }
      
      cond2 = {
        :conditions => { 
          {
            :name => 'user_id', 
            :op => 'IN'
          } => user_ids
        }   
      }
      
      @item= Item.find( 
        :all, 
        :conditions => [cond1, cond2], 
        :op => 'AND'
      )
      
      

      This works normally when some items match with the cond1 group. However, if cond1 returns false on all of items, the find method still returns all items which match the cond2 group.

      It seems like the find method concerns only the cond2 group when cond1 false.

       

      I need some way to make the above works like the query "where ((id IN (?) OR type=?) AND user_id IN (?))".

       

      Thank you in advance.

        • Re: Advanced Queries group :conditions problem
          DecisionPoint Keith Gable

          You can edit ~/.rvm/gems/ruby-1.9.3-p362/gems/rhodes-3.5.1.12.1/lib/framework/rhom/rhom_db_adapter.rb (use different version numbers as appropriate) to log the queries it's doing. Go to the _execute_sql method and uncomment the three puts statements. This will show you what query that Rhom is building. It logs a lot of information, so you'll want to uncomment this later.

           

          Also, you can word this query like so:

           

          @item = item.find(:all, :conditions => ["(id IN (?) OR type = ?) AND user_id IN (?)", ids, type, user_ids])
          

           

          The problem you may run into is that type will probably not be escaped properly, so you will want to do this when passing it in. Here is a mixin I wrote to do this (require the file at the top of your model, I put it in app/lib/rhom):

           

          module Rhom
            # Escapes the passed string. Also wraps it in quotes.
            # This is only needed when using the array form of conditions.
            # For some reason, arguments aren't escaped there.
            def self.escape(string)
              # The SQLite3 version in use doesn't have a quote method, so I ripped off
              # the quote method here.
              if string.nil?
                return nil
              else
                return "'#{string.gsub( /'/, "''" )}'"
              end
            end
          end
          

           

          Hope that helps!