Last Updated
Viewed 33 Times

Let's say we have a ChessPiece class with the following migration.

create_table :chess_pieces do |t|
  t.string  :type
  t.integer :row
  t.integer :column
  t.integer :game_id, index: true
end

If a Bishop wanted to move from (0,0) to (7,7) then I need to check each space in between the two positions for other pieces that might be blocking my Bishop. IE if there was a Pawn on (5,5).

I could query the database for EACH position that might have a blocking piece like this:

pieces = []
8.times do |i|
  pieces << ChessPiece.where(game_id: @game_id, row: i, column: i)
end

But I want to cut down on queries. Alternatively, I could grab all the chess pieces for a game and iterate over them in Ruby, but that also seems inefficient. What I'd like to do is tell the database to combine the row and column columns together into the form "row-column" and call it position. That way I could run something like the following query:

ChessPiece.where(game_id: @game_id, position: ["1-1", "2-2", "3-3"])

How do I dynamically create this new column so I can run something like the above query? (Assume I can't just edit the database schema.)

I need to order a query of Profile entries in an ActiveRecord::Relation object by a value that is computed on run-time.

I have created and assigned a temporary column distance in my controller for the Relation object by using attr_accessor like so:

@profiles.each do |p|
  p.class_eval do
   attr_accessor :distance
  end
end

@profiles.each do |p|
  p.distance = distance_arr[@profiles.index(p)]  # Distance values obtained from an instance array
end

However, when I try to order the Relation with the order method, I get a no such column: distance error i.e. it's not picking up the temporary field.

Here's how I tried to order it in my controller (note I'm using the will_paginate gem, but that is irrelevant).

@profiles = @profiles.order('distance ASC').paginate(page: params[:page])

ActiveRecord has a very neat syntax for querying records where a column is equal to any value in a given array:

For a simple example, let's say you have 10 products with ids 1,2,3,4,5,6,7,8,9,10.

Product.where(id: [2,3,4,5,6])

will return products 2,3,4,5 and 6.

Is there an ActiveRecord equivalent for querying products where the column does not equal any value in an array?

Something like:

Product.where('id != ?', [2,3,4,5,6])

except that it actually works...

And when you pass it [2,3,4,5,6] in this case, it will return products 1,7,8,9 and 10.

EDIT

I need a Rails 3 solution!!!

Have Addresses and Lists with many-to-many relationship, as shown below.

Sometimes need all the Lists an Address is not in.

Using the find_by_sql query shown, and it works great. But is there a way to do it without using direct SQL?

class List
  has_many :address_list_memberships
  has_many :addresses, :through => :address_list_memberships
end


class Address
  has_many :address_list_memberships, :dependent => :destroy
  has_many :lists, :through => :address_list_memberships

  # Lists that this Address is not in
  def Address.lists_not_in(address_id)
    sql = %Q|
SELECT
  l.*
FROM
  lists l
WHERE
  l.id
NOT IN
(
  SELECT
    l.id
  FROM
    addresses a, lists l, address_list_memberships alm
  WHERE
    a.id = alm.address_id AND l.id = alm.list_id
  AND
    a.id = #{address_id}
)
|
    List.find_by_sql(sql)
  end
end

Similar Question 4 (1 solutions) : ActiveRecord Multi Level Single Table Inheritance Query

Similar Question 6 (2 solutions) : Activerecord query against array column using wildcard

Similar Question 7 (1 solutions) : Rails ActiveRecord: Missing column in grouping query

Similar Question 8 (2 solutions) : Rails 4 get column names in raw activerecord query

cc