Querying for X when attr == A and Y when attr == B in Rails -
this query i'm trying run. screenshot.
@colleges = college.all @colleges = @colleges.where(category: @university_type) unless @university_type.blank? or @university_type.all? &:blank? @colleges = @colleges.where("us_news_ranking <= ?", @rank_low) unless @rank_low.blank? @colleges = @colleges.where("(sat_math_25+sat_math_75+sat_reading_25+sat_reading_75)/2 >= ?", @sat_low) unless @sat_low.blank? @colleges = @colleges.where("(sat_math_25+sat_math_75+sat_reading_25+sat_reading_75)/2 <= ?", @sat_high) unless @sat_high.blank? @colleges = @colleges.where("(act_composite_25+act_composite_75)/2 >= ?", @act_low) unless @act_low.blank? @colleges = @colleges.where("(act_composite_25+act_composite_75)/2 <= ?", @act_high) unless @act_high.blank? if !@cost_low.blank? if @in_state.blank? @colleges = @colleges.where("out_of_state_tuition+room_and_board >= ?", @cost_low) @colleges = @colleges.where("out_of_state_tuition+room_and_board <= ?", @cost_high) else @colleges = @colleges.where(state: @in_state).where("in_state_tuition+room_and_board >= ? , in_state_tuition+room_and_board <= ?", @cost_low, @cost_high) @colleges = @colleges.where("state != ? , out_of_state_tuition+room_and_board >= ? , out_of_state_tuition+room_and_board <= ?", @in_state, @cost_low, @cost_high) end end i've tested it, , problem else statement. if comment out 1 of lines in else statement, other 1 behaves you'd expect. however, when leave them both uncommented, never returns colleges.
i don't know problem is, figure has me querying state = a in 1 line, , state = b in other. problem? why? if not, problem?
college.rb
def self.get_college(university_type, rank_low, sat_low, sat_high, act_low, act_high, in_state, cost_low, cost_high) colleges = college.all colleges = colleges.where(category: university_type) unless university_type.blank? or university_type.all? &:blank? colleges = colleges.where("us_news_ranking <= ?", rank_low) unless rank_low.blank? colleges = colleges.where("(sat_math_25+sat_math_75+sat_reading_25+sat_reading_75)/2 >= ?", sat_low) unless sat_low.blank? colleges = colleges.where("(sat_math_25+sat_math_75+sat_reading_25+sat_reading_75)/2 <= ?", sat_high) unless sat_high.blank? colleges = colleges.where("(act_composite_25+act_composite_75)/2 >= ?", act_low) unless act_low.blank? colleges = colleges.where("(act_composite_25+act_composite_75)/2 <= ?", act_high) unless act_high.blank? select_fields = sanitize_sql_array( [ <<-endsql, in_state ] ) *, if( colleges.state = ?, in_state_tuition, out_of_state_tuition ) user_tuition, user_tuition + room_and_board total_cost endsql colleges = colleges.select(select_fields).where("total_cost between ? , ?", cost_low, cost_high) return colleges end tools_controller.rb
@colleges = college.get_college(@university_type, @rank_low, @sat_low, @sat_high, @act_low, @act_high, @in_state, @cost_low, @cost_high) if @searched
update following @adamzerner's comment:
say user selects price range , state. want return colleges in price range, , see if
tuition+room_and_boardin range. want calculate tuition usingin_state_tuitionif user in-state, ,out_of_state_tuitionif user out-of-state.
ah, question. mentioned, need know sql, not rails. nice, clean query getting want looks this:
select *, if( colleges.state = @user_state, in_state_tuition, out_of_state_tuition ) user_tuition, user_tuition + room_and_board total_cost colleges total_cost between @cost_low , @cost_high we use if() expression decide whether use in_state_tuition or out_of_state_tuition , give name, user_tuition. take , add room_and_board total_cost. in our where use between, because it's more concise , readable a <= b , b <= c.
once know our sql looks like, it's easy translate activerecord methods.
# give `select` part of query above select_fields = sanitize_sql_array( [ <<-endsql, @in_state ] ) *, if( colleges.state = ?, in_state_tuition, out_of_state_tuition ) user_tuition, user_tuition + room_and_board total_cost endsql @colleges = college.select(select_fields) .where("total_cost between ? , ?", @cost_low, @cost_high) note sanitize_sql_array protected method of activerecord::base, work inside model. querying logic belongs in model anyway. use case perfect rails scopes:
class college < activerecord::base scope :ranked_at_least, ->(rank=nil) { return self if rank.nil? where("us_news_ranking <= ?", rank) } scope :in_state_with_cost_between, ->(state_name, cost_low, cost_high) { select_fields = sanitize_sql_array( [ <<-endsql, state_name ] ) *, if( colleges.state = ?, in_state_tuition, out_of_state_tuition ) user_tuition, user_tuition + room_and_board total_cost endsql select(select_fields) .where("total_cost between ? , ?", cost_low, cost_high) } scope :with_sat_composite_between, ->(score_low, score_high) { # ... } # ...and on... end this allow make nice, clean queries this:
college.in_state_with_cost_between("new york", 10_000, 50_000). ranked_at_least(20). with_sat_composite_between(1_200, 1_500) ...which seems lot nicer me.
update 2 - simpler , works sqlite
i didn't realize sqlite doesn't have if(). think sanitize_sql_array bit overcomplicated things bit, too, let's simplify. below sqlite query equivalent 1 above (which works in mysql , others):
select colleges.*, case when colleges.state = 'new york' colleges.in_state_tuition else colleges.out_of_state_tuition end user_tuition, user_tuition + room_and_board total_cost colleges total_cost between 15000 , 60000 the difference used case when x y else z end instead of if(x, y, z).
now let's turn activerecord query:
# sanitize values user! safe_state_name = activerecord::base.sanitize(@in_state) select_sql = <<-endsql colleges.*, case when colleges.state = #{safe_state_name} colleges.in_state_tuition else colleges.out_of_state_tuition end user_tuition, user_tuition + room_and_board total_cost endsql college.select(select_sql). where("total_cost between ? , ?", @cost_low, @cost_high) when use "?" replacement in where() rails automatically sanitizes @cost_low , @cost_high us, have manually select(). don't skip step, though--it's important!
we have written query way:
where_sql = <<-endsql ( case when colleges.state = ? colleges.in_state_tuition else colleges.out_of_state_tuition end ) between ? , ? endsql college.where(where_sql, @in_state, @cost_low, @cost_high) ...but think using select() makes cleaner queries, , let use calculated value (e.g. user_tuition, total_cost) multiple times.
scopes core part of rails , learning use them write maintainable code. if don't know scopes, don't know rails. they're easy. in case write scope this:
class college < activerecord::base scope :for_state_with_cost_between, ->(state_name, cost_low, cost_high) { safe_state_name = activerecord::base.sanitize(state_name) select_sql = <<-endsql colleges.*, case when colleges.state = #{safe_state_name} colleges.in_state_tuition else colleges.out_of_state_tuition end user_tuition, user_tuition + room_and_board total_cost endsql select(select_sql). where("total_cost between ? , ?", cost_low, cost_high) } # ... this equivalent defining class method this:
class college < activerecord::base def self.for_state_with_cost_between(state_name, cost_low, cost_high) safe_state_name = activerecord::base.sanitize(state_name) select_sql = # ... self.select(select_sql).where("total_cost between ? , ?", cost_low, cost_high) end # ... in both cases use this:
college.for_state_with_cost_between("new york", 10_000, 50_000) using scopes code written cleaner , readable less room bugs. didn't want paste whole thing here, take @ gist (untested, of course).
original answer
let's break down. first this:
@colleges = college.all # ...let's pretend didn't here... @colleges = @colleges.where( state: @in_state ) .where( "in_state_tuition + room_and_board >= ? , in_state_tuition + room_and_board <= ?", @cost_low, @cost_high ) this creates activerecord::relation , assigns @colleges. if called @colleges.all now, generate , execute sql (more or less):
select * colleges state = @in_state , in_state_tuition + room_and_board >= @cost_low , in_state_tuition + room_and_board <= @cost_high next this:
@colleges = @colleges.where( "state != ? , out_of_state_tuition + room_and_board >= ? , out_of_state_tuition+room_and_board <= ?", @in_state, @cost_low, @cost_high ) this takes activerecord::relation object created above , adds more conditions on it. if did @colleges.all now, generate , execute sql this:
select * colleges ( state = @in_state , in_state_tuition + room_and_board >= @cost_low , in_state_tuition + room_and_board <= @cost_high ) , ( state != @in_state , out_of_state_tuition + room_and_board >= @cost_low , out_of_state_tuition + room_and_board <= @cost_high ) this makes pretty obvious problem is. have state = @in_state , state != @in_state in same query. state can't "new york" , not "new york" @ same time, result empty.
activerecord gives nice abstractions , convenience methods doing database queries, in end it's still important know kind of sql it's generating , means.
Comments
Post a Comment