0

I am pretty inexperienced with MySQL queries, but I am currently learning them. How would I find all the nil fields of an object and then populate just one of those fields?

For example, I have an inventory - but I want to find all the empty slots to show me how many slots I have left and then find ones that are empty, and then to populate just one of those fields with a string or number.

How would I do this?

This is the code I attempted: 

@cast = Cart.where("user_id = ? AND cart_slot_one = nil AND cart_slot_two = nil AND cart_slot_three = nil AND cart_slot_four = ? AND cart_slot_five = ? AND cart_slot_six = ? AND cart_slot_seven = ? AND cart_slot_eight = ? AND cart_slot_nine = ? AND cart_slot_ten = ?",
    current_user.id, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil ).first
Improve this question
3
  • Several thoughts here: You will find one ALL EMPTY cart or nothing at all. If there are several, you just randomly pick one (first). Is this intended? You have your cart referencing (?) slots? Is this what you want? Shouldn't it be the other way round? Slot belongs to Cart. And then you can have as many slots as you need? Can you show the Models involved here to clarify this? Commented Mar 7, 2016 at 11:18
  • You have put many questions in a single post. First, did your code worked as expected? If not, what was the actual output and what was the expected one? Commented Mar 7, 2016 at 11:25
  • @StormViper The code in your question uses the version of where which has a SQL string supplied as an argument. This contrasts with the hash-argument version you can see in @akz92 answer. When you're using the SQL string version, you need to use IS NULL instead of = nil. Regardless of this syntax issue, you should read about associations so you don't have to make named columns for each "cart slot". Commented Mar 7, 2016 at 11:25

3 Answers 3

Reset to default
2

To find a card in which all slots are empty:

slots = %w( one two three four five six seven eight nine ten )
query = Hash[slots.map { |slot| ["cart_slot_#{slot}", nil] }]

@cart = Cart.where(query).find_by(user: current_user)

Or, if your user has an association to carts:

@cart = current_user.slots.find_by(query)

To find a cart with at least one empty slot:

slots = %w( one two three four five six seven eight nine ten )
query = slots.map { |slot| "cart_slot_#{slot} IS NULL"] }.join(' OR ')

@cart = Cart.where(query).find_by(user: current_user)

Once you find such a cart use the following lines to count or determine empty slots:

# count slots:
count = slots.count { |slot| @cart.send("cart_slot_#{slot}").nil? }

# get first empty slot:
slot_number = slots.find { |slot| @cart.send("cart_slot_#{slot}").nil? }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
@cast = Cart.where(user: current_user, cart_slot_one: nil, cart_slot_two: nil, cart_slot_three: nil, cart_slot_four: nil, cart_slot_five: nil, cart_slot_six: nil, cart_slot_seven: nil, cart_slot_eight: nil)
@cast.each do |c|
  c.update_attributes(cart_slot_one: foo') #update all the attributes you want here
end
Improve this answer

Comments

0
  1. Instead of .where(...).first you can use .find_by(...) which is basically the same but implicitly returns the first element or nil
  2. Consider using composition instead of hard-coding slots. You could have a class CartSlot and in the Cart has_many :cart_slots. Instead of checking each on are empty you could check if there are any.

  3. To improve your solution:

    class Cart   
  def self.find_first_empty_for_user(current_user_id)
    find_by(user_id: current_user_id, cart_slot_one: nil, cart_slot_two: nil, cart_slot_three: nil, cart_slot_four: nil, cart_slot_five: nil, cart_slot_six: nil, cart_slot_seven:nil, cart_slot_eight: nil, cart_slot_nine: nil, cart_slot_ten: nil)   
  end 
end

def process(current_user, new_value = 'Whatever)   
  cart = Cart.find_first_empty_for_user(current_user.try(:id))   
  return unless empty_cart   
  cart.cart_slot_one = new_value   
  cart.save!   
  cart  
end
Improve this answer

Comments