Rails .Where() Query Not Working

ActiveRecord where query not working for attributes but select method is

Ok I solved this... but wildly unsatisfactorily because it's just a plain mystery to me. The solution was different for the 2 different cases above. It seems like the root causes of the 2 were different, but it also seems like in neither case will I ever know the reason why...

MaintenanceOrder model issue

I did a lot of git diffs here to check my sanity. Literally... I turned config.log_level = :debug in production.rb then pushed the app to production. I monitored that the correct SQL statements were being used in the query (as I copied over into the above question). Then I reset config.log_level = :info and pushed to production. Then suddenly it worked.

No other changes.. suddenly MaintenanceOrder.where(work_state:"pending_work").size returned the correct value

Item model issue

Here, the fact that the where query was finding some but not all records, and that the subset it was finding was only recent, made me wonder maaaaaybe the old records... were... saved... improperly somehow? I had after all just done a big database migration.

So I ran Item.all.select {|i| i.aasm_state == "pending_start"}.each { |i| i.update_column(:aasm_state,"pending_start") }, and that fixed the problem

Rails .where() query not working

Try this,

ads_params = @ads_params.require(:ads).permit(:id).to_h
location_params = @location_params.require(:location).permit(:id).to_h
@locations = Location.joins(:ads).where(locations: location_params, ads: ads_params)

Hope that helps!

Rails .where query not working

Refer to the first comment in the original question by @PardeepDhingra

toys = @company.toys.where("store_id = ? AND (name LIKE ? OR description LIKE ?)", params[:store_id], "%#{params[:query]}%", "%#{params[:query]}%"

How to express a NOT IN query with ActiveRecord/Rails?

Rails 4+:

Article.where.not(title: ['Rails 3', 'Rails 5']) 

Rails 3:

Topic.where('id NOT IN (?)', Array.wrap(actions))

Where actions is an array with: [1,2,3,4,5]

Why does a query in rails with a limit not work unless .all is put on the end

I think the behaviour depends on how you are handling the locations variable after setting it. This is because Location.order('id ASC').limit(10) isn't querying records but is returning an object of type ActiveRecord::Relation. The query will only occur once you call all, first, each, map, etc. on that object.

In my testing,

Location.order('id ASC').limit(10).map { |l| l.id }

returns an array of 10 ids as you would expect. But

Location.order('id ASC').limit(10).count

returns the total number of locations in the database, because it executes the SQL

SELECT COUNT(*) FROM "locations" LIMIT 10

which returns the full count of location rows (the limit is on the number of rows returned, not the count itself).

So if you are treating the result of Location.order('id ASC').limit(10) as an array by iterating through it, you should get the same result as if you had added all. If you are calling count, you will not. Kind of unfortunate, as I think ideally they should behave the same and you shouldn't have to know that you are dealing with an ActiveRecord::Relation instead of an array.

Rails Scope Not Working When Regular where() Query Does

Scopes defined with scope are evaluated once, when the scope is defined - so DateTime.now refers to when your app instance first started.

Try:

scope :is_queued, lambda { where("status = 2 OR (status = 0 AND status_expires > ?)", DateTime.now) }

ActiveRecord where query not working

If you look at the SQL generated by the query, it probably includes something like:

SELECT players.* WHERE players.team_id IS NULL

This would work if team was defined via a belongs_to :team association on Player, but its not: its defined via a has_one :team with a through: :ownership option. This means you'll need to join to the teams table through the ownership table and select the records with no associated teams records. Since you'll need to use a left join, you have to write the join "manually":

@players = Player.joins("LEFT OUTER JOIN ownerships ON ownerships.player_id = players.id")
  .joins("LEFT OUTER JOIN teams ON ownerships.team_id = teams.id")
  .where("teams.id IS NULL").order(position: :asc, surname: :asc)

You probably want to remove the extraneous column team_id to avoid this kind of confusion:

remove_column :players, :team_id

Rails Model find Where Not | Query where not

Rails4 / Rails 5:

User.where.not(username: nil)

Rails 3:

User.where("username <> nil")

I would love to see other responses

---

Related Topics