我有一个搜索页面，允许用户根据GPA和运动统计等标准搜索运动员。当只使用一个搜索过滤器时，PostgreSQL返回正确的答案。当使用多个搜索过滤器时，只有在两个搜索过滤器中使用相同的状态时，它才会工作。例如，如果我搜索"GPA> 3"one_answers"GPA == 4"，它将返回相应的运动员。如果我执行"GPA> 3"one_answers"ACT> 20"(对于同一运动员，这两个都为真)，它将不返回任何内容(空数组)。
作为整个页面的参考，下面是@conditions的样子(它在代码中被转换为数组而不是散列，以便循环可以迭代，但您了解要点):
{
"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"}
}
下面是代码(Squeel语法):
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
我在下面包含了两个从Ruby -debug中抓取的PostgreSQL调用(我使用的是Squeel, PostgreSQL的Ruby包装器)。如果在两个调用中向下滚动，您将看到第一个和第二个调用是相同的，但是第二个调用有一个额外的and(并且遵循与第一个相同的格式，应该可以正常工作)。
呼叫1(单参数):
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' ))
结果是一个Athlete。
呼叫2(两个参数):
第二个调用返回[]，但应该是同一个运动员…
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' ))
知道为什么这不起作用吗?Bumfuddled !
:::::更新:::::通过以下操作修复它，它仍然相当快:
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
第二个查询有一个WHERE子句，其结尾如下:
AND (( "stats"."stat_type_id" = 22
AND "stats"."value" > '2.0' ))
AND (( "stats"."stat_type_id" = 2
AND "stats"."value" > '10' ))
stat_type_id不能同时等于2和22。您需要第二个连接到stats表，如:
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' ))
如果您不想动态地执行连接，则可以使用子查询，如
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>
这将返回所有符合条件的运动员，因此将其与其他表连接。