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).
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):
{
"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"}
}
Below is the code (Squeel syntax):
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
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).
Call 1 (one parameter):
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' ))
The result is an Athlete.
Call 2 (two parameters):
The second call however, returns [], but should be the same athlete...
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' ))
Any idea why this isn't working? Bumfuddled!
:::::UPDATE::::: Fixed it by doing the following, and its still pretty fast:
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
Your second query has a WHERE clause that ends like this:
AND (( "stats"."stat_type_id" = 22
AND "stats"."value" > '2.0' ))
AND (( "stats"."stat_type_id" = 2
AND "stats"."value" > '10' ))
stat_type_id can't be equal to 2 and 22 at the same time. You'll need a second join to stats table like:
INNER JOIN "stats"
ON "stats"."athlete_id" = "users"."id"
INNER JOIN "stats" AS s2
ON "s2"."athlete_id" = "users"."id"
(...)
AND (( "stats"."stat_type_id" = 22
AND "stats"."value" > '2.0' ))
AND (( "s2"."stat_type_id" = 2
AND "s2"."value" > '10' ))
If you don't want to do joins dynamicaly the alternative is a subquery like:
SELECT athlete_id
FROM stats
WHERE
( "stats"."stat_type_id" = 22
AND "stats"."value" > '2.0' )
OR ( "s2"."stat_type_id" = 2
AND "s2"."value" > '10' )
GROUP BY athlete_id
HAVING COUNT(*)=<the number of parameters you want to filter by>
This will return all athletes that meet the criteria so join that with the rest of your tables.