Multiple unions chain with wheres in rails3

Question

An attempt to construct the query in such a way that I have several operators that define the associations, each of which contains a message chained to them. When the request is launched, I get all the connections, but only the one where from my first call. Here is the body of the method executing the request:

observations_joins = Observation.joins(:obs_session => :project).where(:obs_sessions=>{:project_id=>self.project.id}) descriptor_hash = descriptor_where_hash if tag_descriptors && tag_descriptors.size > 0 puts "The descriptor_hash: #{descriptor_hash}" observations = observations_joins.joins(:obs_descriptors).where("#{descriptor_hash['query_string']}", descriptor_hash['match_values']) if tag_descriptors && tag_descriptors.size > 0 arel = observations.arel puts "The arel sql should be: #{arel.to_sql}" observations 

I have another method that is called from an internal union operator that iterates over potential match values ​​and generates a string and the values ​​used; body is here:

 match_values = [] query_string = "obs_descriptors.tag_name = ?" tag_descriptors.each_index do |index| query_string = query_string + " #{tag_descriptors.fetch(index).qualifier_key} obs_descriptors.tag_name = ?" if index != 0 match_values << tag_descriptors.fetch(index).tag_name end {:match_values=>match_values, :query_string=>query_string} 

Thus, the created sql looks like this:

 SELECT `observations`.* FROM `observations` INNER JOIN `obs_sessions` ON `obs_sessions`.`id` = `observations`.`obs_session_id` INNER JOIN `projects` ON `projects`.`id` = `obs_sessions`.`project_id` INNER JOIN `obs_descriptors` ON `obs_descriptors`.`observation_id` = `observations`.`id` WHERE (`obs_sessions`.`project_id` = 1) 

and does not include a second set of conditions. I also print a hash, just to make sure that I’m not losing my mind and that there are values, and really are.

So, what am I missing to do it the way I expected?

Answer 1

Answering my own question here. The most elegant, concise way that I found to get this work was to go straight down to isl. In addition, there were some problems with the source code, but still I needed to use isl for the correct grouping. For context, I have an object that should dynamically build a translucent query based on the data associated with it, so I wanted to do things like check for the presence of certain related data, and, if any, then stick to additional connections and connections. Here are the final versions of the corresponding methods:

 def find_observations observations = Observation.select('distinct observations.*').includes(:obs_session).includes(:judgements).includes(:concepts).includes(:obs_descriptors) observations = observations.joins(:obs_session => :project).where(:obs_sessions=>{:project_id=>self.project.id}) if tag_descriptors && tag_descriptors.size > 0 observations = observations.where(descriptor_predicate) end if session_descriptors && session_descriptors.size > 0 observations = observations.where(session_predicate) end if user_descriptors && user_descriptors.size > 0 observations = observations.where(user_predicate) end #puts "observations sql is: #{observations.to_sql}" observations.all end 

The above method does not necessarily call the remaining methods, which return the isl value used in the where calls when binding the AR object when building the final request. Pay attention to the disclosure; I had a version of this use of isl entirely that seemed to work, but actually return duplicates. I found links to using a group (some_attribute) to fake things, but this turned out to be causing problems in the chain. So I went back to using ActiveRelation to specify various integrates and includes, and isl for the rest.

The next part was the part that initially gave me a lot of trouble; there is a variable number of possibilities, and each of them can be either an AND or OR condition, and it must be grouped separately so as not to spoil the rest of the generated where clause.

 def descriptor_predicate od = Arel::Table.new :obs_descriptors predicate = nil self.tag_descriptors.each_index do |index| descriptor = self.tag_descriptors.fetch(index) qual_key = descriptor.qualifier_key tag_name = descriptor.tag_name if index == 0 predicate = od[:descriptor].eq(tag_name) else if qual_key == "OR" predicate = predicate.or(od[:descriptor].eq(tag_name)) else predicate = predicate.and(od[:descriptor].eq(tag_name)) end end end predicate end 

And finally, other predicate methods for potential values ​​of a merged entity:

 def session_predicate o = Arel::Table.new :observations predicate = nil self.session_descriptors.each_index do |index| obs = self.session_descriptors.fetch(index) if index == 0 predicate = o[:obs_session_id].eq(obs.entity_id) else predicate = predicate.or(o[:obs_session_id].eq(obs.entity_id)) end end predicate end def user_predicate o = Arel::Table.new :observations predicate = nil self.user_descriptors.each_index do |index| obs = self.user_descriptors.fetch(index) if index == 0 predicate = o[:contributor_id].eq(obs.entity_id) else predicate = predicate.or(o[:contributor_id].eq(obs.entity_id)) end end predicate end def descriptor_where_string(included_where_statements) tag_descriptors.each_index do |index| qual_key = tag_descriptors.fetch(index).qualifier_key tag_name = tag_descriptors.fetch(index).tag_name if index == 0 query_string = "obs_descriptors.descriptor = #{tag_name}" else if qual_key == "OR" query_string = query_string + " #{qual_key} obs_descriptors.descriptor = #{tag_name} AND #{included_where_statements} " else query_string = query_string + " #{qual_key} obs_descriptors.descriptor = ?" end end end query_string end 

Ultimately, I found a better solution related to using the ActiveRelation chain to provide clarity and to enable and use isl directly for the conditions of the corresponding values. Hope this helps someone at some point.