StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is this Ruby Squeel (PostgreSQL) call returning nil, when it's clearly not?
text
Body
copied!<p>I have a search page that allows a user to search for athletes based on criteria such as GPA and sport stats. When only one search filter is used, the PostgreSQL returns the correct answer. When more than one search filter is used, it will work ONLY if the same stat is being used in both search filters. For instance, if I do a search of "GPA > 3" and "GPA == 4" it will return the corresponding athlete. If I do "GPA > 3" and "ACT > 20" (both of which are true on the same athlete), it returns nothing (empty array).</p> <p>For reference throughout this page, here is what @conditions looks like (it's transformed into an array in the code rather than a hash so the loop can iterate, but you get the gist):</p> <pre><code>{ "0"=>{"category"=>"1", "stat_type_id"=>"22", "predicate"=>"Greater Than", "q"=>"2", "type"=>"stat"}, "1"=>{"category"=>"1", "stat_type_id"=>"2", "predicate"=>"Greater Than", "q"=>"10", "type"=>"stat"} } </code></pre> <p>Below is the code (Squeel syntax):</p> <pre><code>def query relation = Athlete.listed.for_sport( self.sport ) return relation if @conditions.blank? @conditions = @conditions.map { |k, v| v } if @conditions.is_a?(Hash) debugger @conditions.each do |condition| case condition[:type] when "stat" if !condition[:q].eql?("") relation = case condition[:predicate] when /less than/i relation.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.lt (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /greater than/i relation.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.gt (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /equal to/i relation.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.eq (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /not equal to/i relation.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.not_eq (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} end end when "academic" relation when "social" relation end end relation end </code></pre> <p>I have included below two PostgreSQL calls grabbed from ruby-debug (I'm using Squeel, a Ruby wrapper for PostgreSQL). If you scroll down in both calls, you'll see that the first and second calls are identical, but the second has an extra AND (and follows the same format as the first, should work fine).</p> <p>Call 1 (one parameter):</p> <pre><code>SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."first_name" AS t0_r2, "users"."last_name" AS t0_r3, "users"."facebook_id" AS t0_r4, "users"."access_token" AS t0_r5, "users"."birthday" AS t0_r6, "users"."gender" AS t0_r7, "users"."device_token" AS t0_r8, "users"."current_city" AS t0_r9, "users"."hometown" AS t0_r10, "users"."relationship_status" AS t0_r11, "users"."avatar_file_name" AS t0_r12, "users"."avatar_content_type" AS t0_r13, "users"."avatar_file_size" AS t0_r14, "users"."avatar_updated_at" AS t0_r15, "users"."email" AS t0_r16, "users"."encrypted_password" AS t0_r17, "users"."reset_password_token" AS t0_r18, "users"."reset_password_sent_at" AS t0_r19, "users"."remember_created_at" AS t0_r20, "users"."sign_in_count" AS t0_r21, "users"."current_sign_in_at" AS t0_r22, "users"."last_sign_in_at" AS t0_r23, "users"."current_sign_in_ip" AS t0_r24, "users"."last_sign_in_ip" AS t0_r25, "users"."authentication_token" AS t0_r26, "users"."created_at" AS t0_r27, "users"."updated_at" AS t0_r28, "users"."time_zone" AS t0_r29, "users"."username" AS t0_r30, "users"."middle_name" AS t0_r31, "users"."primary_sport_id" AS t0_r32, "users"."state" AS t0_r33, "users"."high_school" AS t0_r34, "users"."phone" AS t0_r35, "users"."user_type_id" AS t0_r36, "users"."agreed_to_terms_and_conditions" AS t0_r37, "users"."agreed_to_information_integrity" AS t0_r38, "users"."agreed_to_content_responsibility" AS t0_r39, "users"."account_type_id" AS t0_r40, "users"."social_score" AS t0_r41, "users"."recruit_year" AS t0_r42, "users"."college_major" AS t0_r43, "users"."secondary_sport_id" AS t0_r44, "users"."handedness" AS t0_r45, "users"."checkdin_id" AS t0_r46, "users"."allow_twitter_sync" AS t0_r47, "users"."allow_facebook_sync" AS t0_r48, "users"."head_coach" AS t0_r49, "users"."head_coach_email" AS t0_r50, "users"."has_recruited_others" AS t0_r51, "users"."customer_id" AS t0_r52, "users"."last_4_digits" AS t0_r53, "users"."removed_from_listing" AS t0_r54, "users"."slug" AS t0_r55, "users"."college_minor" AS t0_r56, "users"."type" AS t0_r57, "users"."billing_expiration_month" AS t0_r58, "users"."billing_expiration_year" AS t0_r59, "users"."billing_name" AS t0_r60, "users"."billing_address" AS t0_r61, "users"."billing_city" AS t0_r62, "users"."billing_state" AS t0_r63, "users"."billing_zip" AS t0_r64, "users"."college_id" AS t0_r65, "users"."agreed_to_age_requirements" AS t0_r66, "users"."primary_sport_primary_position_id" AS t0_r67, "users"."secondary_sport_primary_position_id" AS t0_r68, "users"."recruit_status_id" AS t0_r69, "users"."active_flag" AS t0_r70, "users"."public" AS t0_r71, "invitations"."id" AS t1_r0, "invitations"."sender_id" AS t1_r1, "invitations"."role_id" AS t1_r2, "invitations"."to" AS t1_r3, "invitations"."accepted_at" AS t1_r4, "invitations"."token" AS t1_r5, "invitations"."created_at" AS t1_r6, "invitations"."updated_at" AS t1_r7, "invitations"."current_state" AS t1_r8, "invitations"."requester_id" AS t1_r9, "invitations"."user_type" AS t1_r10, "invitations"."type" AS t1_r11 FROM "users" INNER JOIN "stats" ON "stats"."athlete_id" = "users"."id" LEFT OUTER JOIN "invitations" ON "invitations"."requester_id" = "users"."id" WHERE "users"."removed_from_listing" = 'f' AND "users"."type" IN ( 'Athlete' ) AND (( "invitations"."current_state" IN ( 'accepted' ) OR "invitations"."current_state" IS NULL )) AND ( users.primary_sport_id = 13 OR users.secondary_sport_id = 13 ) AND (( "stats"."stat_type_id" = 22 AND "stats"."value" > '2.0' )) </code></pre> <p>The result is an Athlete.</p> <p>Call 2 (two parameters):</p> <p>The second call however, returns [], but should be the same athlete...</p> <pre><code>SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."first_name" AS t0_r2, "users"."last_name" AS t0_r3, "users"."facebook_id" AS t0_r4, "users"."access_token" AS t0_r5, "users"."birthday" AS t0_r6, "users"."gender" AS t0_r7, "users"."device_token" AS t0_r8, "users"."current_city" AS t0_r9, "users"."hometown" AS t0_r10, "users"."relationship_status" AS t0_r11, "users"."avatar_file_name" AS t0_r12, "users"."avatar_content_type" AS t0_r13, "users"."avatar_file_size" AS t0_r14, "users"."avatar_updated_at" AS t0_r15, "users"."email" AS t0_r16, "users"."encrypted_password" AS t0_r17, "users"."reset_password_token" AS t0_r18, "users"."reset_password_sent_at" AS t0_r19, "users"."remember_created_at" AS t0_r20, "users"."sign_in_count" AS t0_r21, "users"."current_sign_in_at" AS t0_r22, "users"."last_sign_in_at" AS t0_r23, "users"."current_sign_in_ip" AS t0_r24, "users"."last_sign_in_ip" AS t0_r25, "users"."authentication_token" AS t0_r26, "users"."created_at" AS t0_r27, "users"."updated_at" AS t0_r28, "users"."time_zone" AS t0_r29, "users"."username" AS t0_r30, "users"."middle_name" AS t0_r31, "users"."primary_sport_id" AS t0_r32, "users"."state" AS t0_r33, "users"."high_school" AS t0_r34, "users"."phone" AS t0_r35, "users"."user_type_id" AS t0_r36, "users"."agreed_to_terms_and_conditions" AS t0_r37, "users"."agreed_to_information_integrity" AS t0_r38, "users"."agreed_to_content_responsibility" AS t0_r39, "users"."account_type_id" AS t0_r40, "users"."social_score" AS t0_r41, "users"."recruit_year" AS t0_r42, "users"."college_major" AS t0_r43, "users"."secondary_sport_id" AS t0_r44, "users"."handedness" AS t0_r45, "users"."checkdin_id" AS t0_r46, "users"."allow_twitter_sync" AS t0_r47, "users"."allow_facebook_sync" AS t0_r48, "users"."head_coach" AS t0_r49, "users"."head_coach_email" AS t0_r50, "users"."has_recruited_others" AS t0_r51, "users"."customer_id" AS t0_r52, "users"."last_4_digits" AS t0_r53, "users"."removed_from_listing" AS t0_r54, "users"."slug" AS t0_r55, "users"."college_minor" AS t0_r56, "users"."type" AS t0_r57, "users"."billing_expiration_month" AS t0_r58, "users"."billing_expiration_year" AS t0_r59, "users"."billing_name" AS t0_r60, "users"."billing_address" AS t0_r61, "users"."billing_city" AS t0_r62, "users"."billing_state" AS t0_r63, "users"."billing_zip" AS t0_r64, "users"."college_id" AS t0_r65, "users"."agreed_to_age_requirements" AS t0_r66, "users"."primary_sport_primary_position_id" AS t0_r67, "users"."secondary_sport_primary_position_id" AS t0_r68, "users"."recruit_status_id" AS t0_r69, "users"."active_flag" AS t0_r70, "users"."public" AS t0_r71, "invitations"."id" AS t1_r0, "invitations"."sender_id" AS t1_r1, "invitations"."role_id" AS t1_r2, "invitations"."to" AS t1_r3, "invitations"."accepted_at" AS t1_r4, "invitations"."token" AS t1_r5, "invitations"."created_at" AS t1_r6, "invitations"."updated_at" AS t1_r7, "invitations"."current_state" AS t1_r8, "invitations"."requester_id" AS t1_r9, "invitations"."user_type" AS t1_r10, "invitations"."type" AS t1_r11 FROM "users" INNER JOIN "stats" ON "stats"."athlete_id" = "users"."id" LEFT OUTER JOIN "invitations" ON "invitations"."requester_id" = "users"."id" WHERE "users"."removed_from_listing" = 'f' AND "users"."type" IN ( 'Athlete' ) AND (( "invitations"."current_state" IN ( 'accepted' ) OR "invitations"."current_state" IS NULL )) AND ( users.primary_sport_id = 13 OR users.secondary_sport_id = 13 ) AND (( "stats"."stat_type_id" = 22 AND "stats"."value" > '2.0' )) AND (( "stats"."stat_type_id" = 2 AND "stats"."value" > '10' )) </code></pre> <p>Any idea why this isn't working? Bumfuddled!</p> <p>:::::UPDATE::::: Fixed it by doing the following, and its still pretty fast:</p> <pre><code>def query athletes = Athlete.listed.for_sport( self.sport ) relation = athletes return relation if @conditions.blank? @conditions = @conditions.map { |k, v| v } if @conditions.is_a?(Hash) @conditions.each do |condition| case condition[:type] when "stat" if !condition[:q].eql?("") relation = case condition[:predicate] when /less than/i athletes.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.lt (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /greater than/i athletes.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.gt (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /equal to/i athletes.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.eq (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} when /not equal to/i athletes.joins{stats}.where{(stats.stat_type_id.eq condition[:stat_type_id]) & (stats.value.not_eq (StatType.where(:id => condition[:stat_type_id]).first.display_as_decimal == true ? condition[:q].to_f.to_s : condition[:q].to_i.to_s))} end end when "academic" relation when "social" relation end end relation end </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload