Find with SQL query then update all columns in an array of objects

Question

Using Rails 3.2. Previously I did this to destroy, but now I don't wanna destroy, but just mark them as inactive instead:

Shop.find_by_sql("SELECT id FROM #{ActiveRecord::Base.connection_config[:database]}.shops AS shops
  LEFT OUTER JOIN thirdparty.othershoplist AS other_shops
  ON shops.other_shop_id = other_shops.ShopID
  WHERE other_shops.ShopID IS NULL").map(&:destroy)

Now I don't want to destroy the records, but wanna set the column for shop.shop_status as inactive for the return set.

Shop.find_by_sql("SELECT id FROM #{ActiveRecord::Base.connection_config[:database]}.shops AS shops
  LEFT OUTER JOIN thirdparty.othershoplist AS other_shops
  ON shops.other_shop_id = other_shops.ShopID
  WHERE other_shops.ShopID IS NULL").update_all(shop_status: 'inactive')

But this is not working because the return set is an array. How can I achieve this as simple as possible (perhaps a one liner)?

For which records in your shop you are trying to update shop_status as inactive? I mean what is your SQL query? — Pavan
– Pavan, Commented Apr 27, 2014 at 17:28

Philip Hallstrom · Accepted Answer · 2014-04-27 17:35:56Z

2

Assuming you want to leave that SQL query as is, I would do this:

ids = Shop.find_by_sql("SELECT id
       FROM #{ActiveRecord::Base.connection_config[:database]}.shops AS shops
       LEFT OUTER JOIN thirdparty.othershoplist AS other_shops
       ON shops.other_shop_id = other_shops.ShopID
       WHERE other_shops.ShopID IS NULL")
Shop.where(id: ids).update_all(shop_status: 'inactive')

answered Apr 27, 2014 at 17:35

Philip Hallstrom

19.9k2 gold badges44 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Victor Over a year ago

Can't do it in one liner?

Philip Hallstrom Over a year ago

Well, you could put that big "Shop.find_by_sql" line where "ids" is in the second line and it would technically be one line. Or you could rewrite that find_by_sql...

Victor Over a year ago

Care to give an example? Thanks.

max pleaner Over a year ago

@Victor -

ids = Shop.where(id: Shop.find_by_sql("SELECT id        FROM #{ActiveRecord::Base.connection_config[:database]}.shops AS shops        LEFT OUTER JOIN thirdparty.othershoplist AS other_shops        ON shops.other_shop_id = other_shops.ShopID        WHERE other_shops.ShopID IS NULL")).update_all(shop_status: 'inactive')

... thats the one-liner

Collectives™ on Stack Overflow

Find with SQL query then update all columns in an array of objects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related