Lookup against MYSQL TEXT type column

My table/model has TEXT type column, and when filtering for the records on the model itself, the AR where produces the correct SQL and returns correct results, here is what I mean :

MyNamespace::MyValue.where(value: 'Good Quality') 

Produces this SQL :

SELECT `my_namespace_my_values`.*  FROM `my_namespace_my_values`  WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"' 

Take another example where I m joining MyNamespace::MyValue and filtering on the same value column but from the other model (has relation on the model to my_values). See this (query #2) :

OtherModel.joins(:my_values).where(my_values: { value: 'Good Quality' }) 

This does not produce correct query, this filters on the value column as if it was a String column and not Text, therefore producing incorrect results like so (only pasting relevant where) :

WHERE my_namespace_my_values`.`value` = 'Good Quality' 

Now I can get past this by doing LIKE inside my AR where, which will produce the correct result but slightly different query. This is what I mean :

OtherModel.joins(:my_values).where('my_values.value LIKE ?, '%Good Quality%') 

Finally arriving to my questions. What is this and how it's being generated for where on the model (for text column type)?

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"' 

Maybe most important question what is the difference in terms of performance using :

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"' 

and this :

(my_namespace_my_values.value LIKE '%Good Quality%') 

and more importantly how do I get my query with joins (query #2) produce where like this :

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"' 

4 Answers

Answers 1

(Partial answer -- approaching from the MySQL side.)

What will/won't match

Case 1: (I don't know where the extra backslashes and quotes come from.)

WHERE `my_namespace_my_values`.`value` = '\\\"Good Quality\\\"'  \"Good Quality\"               -- matches Good Quality                   -- does not match The product has Good Quality.  -- does not match 

Case 2: (Find Good Quality anywhere in value.)

WHERE my_namespace_my_values.value LIKE '%Good Quality%'  \"Good Quality\"               -- matches Good Quality                   -- matches The product has Good Quality.  -- matches 

Case 3:

WHERE `my_namespace_my_values`.`value` = 'Good Quality'  \"Good Quality\"               -- does not match Good Quality                   -- matches The product has Good Quality.  -- does not match 

Performance:

• If value is declared TEXT, all cases are slow.
• If value is not indexed, all are slow.
• If value is VARCHAR(255) (or smaller) and indexed, Cases 1 and 3 are faster. It can quickly find the one row, versus checking all rows.

Phrased differently:

• LIKE with a leading wildcard (%) is slow.
• Indexing the column is important for performance, but TEXT cannot be indexed.

Answers 2

What is this and how it's being generated for where on the model (for text column type)?

Thats generated behind Active Records (Arel) lexical engine. See my answer below on your second question as to why.

What is the difference in terms of performance using...

The "=" matches by whole string/chunk comparison While LIKE matches by character(s) ( by character(s)).

In my projects i got tables with millions of rows, from my experience its really faster to the use that comparator "=" or regexp than using a LIKE in a query.

How do I get my query with joins (query #2) produce where like this...

Can you try this,

OtherModel.joins(:my_values).where(OtherModel[:value].eq('\\\"Good Quality\\\"')) 

Answers 3

I think it might be helpful.

to search for \n, specify it as \n. To search for \, specify it as \\ this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

link

LIKE and = are different operators.

= is a comparison operator that operates on numbers and strings. When comparing strings, the comparison operator compares whole strings.

LIKE is a string operator that compares character by character.

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci; +-----------------------------------------+ | 'ä' LIKE 'ae' COLLATE latin1_german2_ci | +-----------------------------------------+ |                                       0 | +-----------------------------------------+ mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci; +--------------------------------------+ | 'ä' = 'ae' COLLATE latin1_german2_ci | +--------------------------------------+ |                                    1 | +--------------------------------------+ 

Answers 4

The '=' op is looking for an exact match while the LIKE op is working more like pattern matching with '%' being similar like '*' in regular expressions.

So if you have entries with

1. Good Quality
2. More Good Quality

only LIKE will get both results.

Regarding the escape string I am not sure where this is generated, but looks like some standardized escaping to get this valid for SQL.

Read More

Count sold products by specific families in orders by date

I'm trying to figured out the best way to count sold products by a given date range on orders by a specific family.

These are my simplified models:

• Order placed_on
• OrderItem order_id, product_id, amount
• Product family_id
• Family

So, given now some dates, say d1 and d2, I need to count how many Product of a given Family are in those Order.

The desired output would be something like this:

# all these are products from the same family sold in the last week [   {"product_24": 3435},   {"product_34": 566},   {"product_83": 422}   … ] 

I know how to do it looping all over the orders, but I think there should be a better way.

3 Answers

Answers 1

Assuming your data model and variables it should be something like:

OrderItem.joins(:order)          .joins(product: :family)          .where(orders: {created_at: d1..d2})          .where(products: {family_id: <YOUR_FAMILY_ID>})          .group(:product_id)          .sum(:amount) 

This will generate the following sql:

SELECT     SUM("order_items"."amount") AS sum_amount,     "order_items"."product_id" AS order_items_product_id FROM "order_items"     INNER JOIN "orders" ON "orders"."id" = "order_items"."order_id"     INNER JOIN "products" ON "products"."id" = "order_items"."product_id"      INNER JOIN "families" ON "families"."id" = "products"."family_id"  WHERE     ("orders"."created_at" BETWEEN ? AND ?)         AND "products"."family_id" = ? GROUP BY "order_items"."product_id" 

and return the following structure:

=> [{product_id => <sum of this product id since d1 until d2 for family_id>}, ...] 

Also I'm assuming you want to sum the amount of each product. Let me know if works for you.

Answers 2

class CreateOrders < ActiveRecord::Migration[5.1]   def change     create_table :orders do |t|       t.timestamps     end   end end  class CreateOrderItems < ActiveRecord::Migration[5.1]   def change     create_table :order_items do |t|       t.integer :order_id, index:true       t.integer :product_id, index:true       t.integer :amount       t.timestamps     end   end end  class CreateProducts < ActiveRecord::Migration[5.1]   def change     create_table :products do |t|       t.text :name       t.integer :family_id, index:true       t.timestamps     end   end end  class CreateFamilies < ActiveRecord::Migration[5.1]   def change     create_table :families do |t|       t.text :name       t.timestamps     end   end end  class Family < ApplicationRecord   has_many :products end  class Order < ApplicationRecord   has_many :order_items   has_many :products, through: :order_items end  class OrderItem < ApplicationRecord   belongs_to :order   belongs_to :product end  class Product < ApplicationRecord   belongs_to :family   has_many :order_items end  irb(main):015:0> Order.joins(:order_items).joins(:products).where("products.family_id":2).where("orders.created_at": [(Time.now).to_date..(Time.now + 1.day).to_date]).count    (0.6ms)  SELECT COUNT(*) FROM "orders" INNER JOIN "order_items" ON "order_items"."order_id" = "orders"."id" INNER JOIN "order_items" "order_items_orders_join" ON "order_items_orders_join"."order_id" = "orders"."id" INNER JOIN "products" ON "products"."id" = "order_items_orders_join"."product_id" WHERE "products"."family_id" = ? AND ("orders"."created_at" BETWEEN '2018-04-20' AND '2018-04-21')  [["family_id", 2]] => 3 

Answers 3

Not the full answer, as you have two pretty detailed ones already - but some notes

I'd setup scopes ... because we're not sure if how vertical your db is ... also, do avoid any time range issues, ensure you get the whole day before feeding the mess into the query. These are just examples not syntax checked or recommended best forms

  # ensure we are getting whole of each day   full_d1 = d1.beginning_of_day   full_d2 = d2.end_of_day    #  change the order based on whatever you have more of   scope :orders -> {where(created_at: full_d1..full_d2)}   scope :family -> {where(product: family_id)}    # use something like - swap order based on db conditions   Order.orders.family  # ... then add the rest of what they are throwing down or .size / .count 

Also, as noted elsewhere in S/O you can cache counts on has_many relationships - link

Read More

Table name corruption errors in ActiveRecord

Sporadically we get PG::UndefinedTable errors while using ActiveRecord. The association table name is some how corrupted and I quite often see Cancelled appended to the end of the table name.

E.g:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "fooCancell" does not exist  ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "Cancelled" does not exist ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "barC" does not exist 

In the example above, I have obfuscated the table name by using foo and bar.

We see this errors when the rails project is running inside Puma. Queue workers seems to be doing okay.

The tables in the error message doesn't correspond to real tables or models. It looks like the case of memory corruption. Has anyone seen such issues? If so how did you get around it?

puma.rb

on_worker_boot do   ActiveRecord::Base.establish_connection end 

database.yml

production:   url:  <%= ENV["DATABASE_URL"] %>   pool: <%= ENV['DB_CONNECTION_POOL_SIZE'] || 5%>   reaping_frequency: <%= ENV['DB_CONNECTION_REAPING_FREQUENCY'] || 10 %>   prepared_statements: false 

2 Answers

Answers 1

It looks like reaping_frequency may be the issue. I found a couple claims that they may have a threading bug. I would try removing that option or setting it to nil and see if that works. The only other thing I can think of is if you are manually calling Thread.new and using active record within it. Here are the few claims against reaping:

http://omegadelta.net/2014/03/15/the-rails-grim-reaper/

https://github.com/mperham/sidekiq/issues/1936

Search for "DO fear the Reaper" here: https://www.google.com/amp/s/bibwild.wordpress.com/2014/07/17/activerecord-concurrency-in-rails4-avoid-leaked-connections/amp/

Answers 2

I'm hazarding a guess here...

But you might be either:

1. calling fork within your application; OR
2. calling ActiveRecord routines (using database calls) before the server (puma) is forking it's worker processes (during the app initialization).

Either of these will break ActiveRecord's synchronization and cause multiple processes to share the database connection pool without synchronizing it's use (resulting in interlaced and corrupt database commands).

If you are using fork, make sure to close all the ActiveRecord database connections and reinitialize the connection pool (there's a function call that does it, but I don't remember it of the top of my head, maybe ActiveRecord.disconnect! or ActiveRecord.connection_pool.disconnect!).

Otherwise, before running Puma (either during the initialization process or using Puma's at_fork), close all the ActiveRecord database connections and reinitialize the connection pool.

Read More

Rails: eager-loading on an already-left-joined table?

We are already left-joining a table so that we can order by a column, if the relation exists:

people = Person   .joins("LEFT JOIN addresses ON addresses.id = people.address_id")   .order("addresses.country")   .all 

This results in a single SQL query, but I'd like to have people.first.address not trigger SQL to load the address. I am left-joining because some people don't have addresses.

.includes(:address) triggers a separate query.

You can do what I'm suggesting with inner joins, using includes, but that triggers 2 SQL queries:

Person.includes(:address).all 

While joins + includes triggers only one (but INNER joins):

Person.joins(:address).includes(:address).all 

Active record also uses left joins if you force a join while eager-loading .eager_load(:addresses).

Can you take an existing left-join and have rails eager-load with those results? So far I can't find this.

3 Answers

Answers 1

Well, in your case you could select your query creating an alias for the fields you are going to use from address something like this:

people = Person          .joins("LEFT JOIN addresses ON addresses.id = people.address_id")          .select("people.*, addresses.country as address_country")          .order("address_country")          .all 

This won't change your desired query and will not result in extra queries, not for country at least.

Answers 2

Try this:

people = Person.   eager_load(:address).   merge(Address.order("coalesce(country, '')")).   all  people.first.address 

eager_load forces eager loading by performing a LEFT OUTER JOIN.

I added a coalesce on country so you have more control over where people without addresses appear in the results.

Here is what it looks like for me:

people = Person.   eager_load(:address).   merge(Address.order("coalesce(country, '')")).   all  (0.5ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people" LEFT OUTER JOIN "addresses" ON "addresses"."person_id" = "people"."id" SQL (2.1ms)  SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, "people"."created_at" AS t0_r2, "people"."updated_at" AS t0_r3, "addresses"."id" AS t1_r0, "addresses"."person_id" AS t1_r1, "addresses"."address" AS t1_r2, "addresses"."country" AS t1_r3, "addresses"."created_at" AS t1_r4, "addresses"."updated_at" AS t1_r5 FROM "people" LEFT OUTER JOIN "addresses" ON "addresses"."person_id" = "people"."id" ORDER BY coalesce(addresses.country, '')   people.first.address nil  people.last.address #<Address:0x007febabb508a8> {             :id => 1,      :person_id => 4,        :address => "24175 Gerhold Prairie",        :country => "O",     :created_at => Thu, 01 Feb 2018 18:47:45 UTC +00:00,     :updated_at => Thu, 01 Feb 2018 18:47:45 UTC +00:00 } 

Note that no queries are run when you access the addresses

I'll point that you've ordered by a table that left outer joined, so you need to decide how you want to treat nulls.

Answers 3

You can uses #references with #includes to LEFT JOIN your relation and achieve what you are after.

people = Person   .includes(:addresses)   .references(:addresses)   .order("addresses.country") 

This will yield one query and all the people, whether or not they have an address, and also eager load the addresses to avoid the dreaded N + 1 queries.

The documentation doesn't do a great job of explaining that #references will add a LEFT JOIN but it's purpose is to allow adding SQL clauses (WHERE, ORDER, GROUP, etc.) on the relation in conjunction with #includes.

Read More

What can I write to fix ActiveRecord::RecordNotUnique?

I can't seem to shake this exception. I have the following code:

class Batch < ApplicationRecord    before_create :upcase_session_id         def self.for_session(session_id, opts)     batch = Batch.where(session_id: session_id.upcase).first_or_initialize     if batch.new_record?       batch.property = opts[:property]       batch.save!     end     batch   end    private    def upcase_session_id     self.session_id = session_id.upcase   end end 

Calling Code:

def retrieve_batch     self.batch ||= Batch.for_session(batch_session_id, property: property) end 

I'm not sure how I can change this code to stop getting this exception that I keep getting.

1 Answers

Answers 1

The exception is raised when a record cannot be inserted because it would violate a uniqueness constraint. You should tell us the table constraints because the problem could be any column.

You could try this:

class Batch < ApplicationRecord    def self.for_session(session_id, opts)     Batch.where(session_id: session_id.upcase).first_or_create(property: opts[:property])   end  end 

Read More

ActiveRecord pluck to SQL

I know these two statements perform the same SQL:

Using select

User.select(:email) # SELECT  `users`.`email` FROM `users` 

And using pluck

User.all.pluck(:email) # SELECT `users`.`email` FROM `users` 

Now I need to get the SQL statement derived from each method. Given that the select method returns an ActiveRecord::Relation, I can call the to_sql method. However, I cannot figure out how to get the SQL statement derived from a pluck operation on an ActiveRecord::Relation object, given that the result is an array.

Please, take into account that this is a simplification of the problem. The number of attributes plucked can be arbitrarily high.

Any help would be appreciated.

2 Answers

Answers 1

You cannot chain to_sql with pluck as it doesn't return ActiveRecord::relation. If you try to do, it throws an exception like so

NoMethodError: undefined method `to_sql' for [[""]]:Array 

I cannot figure out how to get the SQL statement derived from a pluck operation on an ActiveRecord::Relation object, given that the result is an array.

Well, as @cschroed pointed out in the comments, they both(select and pluck) perform same SQL queries. The only difference is that pluck return an array instead of ActiveRecord::Relation. It doesn't matter how many attributes you are trying to pluck, the SQL statement will be same as select

Example:

User.select(:first_name,:email) #=> SELECT "users"."first_name", "users"."email" FROM "users" 

Same for pluck too

User.all.pluck(:first_name,:email) #=> SELECT "users"."first_name", "users"."email" FROM "users" 

So, you just need to take the SQL statement returned by the select and believe that it is the same for the pluck. That's it!

Answers 2

You could monkey-patch the ActiveRecord::LogSubscriber class and provide a singleton that would register any active record queries, even the ones that doesn't return ActiveRecord::Relation objects:

class QueriesRegister   include Singleton   def queries     @queries ||= []   end    def flush     @queries = []   end end  module ActiveRecord  class LogSubscriber < ActiveSupport::LogSubscriber    def sql(event)     QueriesRegister.instance.queries << event.payload[:sql]     "#{event.payload[:name]} (#{event.duration}) #{event.payload[:sql]}"       end  end end 

Run you query:

User.all.pluck(:email) 

Then, to retrieve the queries:

QueriesRegister.instance.queries 

Read More

select all records holding some condition in has_many association - Ruby On Rails

I have a model profile.rb with following association

class User < ActiveRecord::Base    has_one :profile end  class Profile < ActiveRecord::Base     has_many :skills     belongs_to :user end 

I have a model skills.rb with following association

class Skill < ActiveRecord::Base     belongs_to :profile end 

I have following entries in skills table

id:         name:           profile_id: ==================================================== 1           accounting          1 2           martial arts        2 3           law                 1 4           accounting          2 5           journalist          3 6           administration      1 

and so on , how can i query all the profiles with ,lets say, "accounting" & "administration" skills which will be profile with id 1 considering the above recode. so far i have tried following

Profile.includes(:skills).where(skills: {name: ["accounting" , "administration"]} ) 

but instead of finding profile with id 1 - It gets me [ 1, 2 ] because profile with id 2 holds "accounting" skills and it's performing an "IN" operation in database

Note: I'm using postgresql and question is not only about a specific id of profile as described (which i used only as an example) - The original question is to get all the profiles which contain these two mentioned skills.

My activerecord join fires the following query in postgres

SELECT FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" IN ('Accounting', 'Administration') 

In below Vijay Agrawal's answer is something which i already have in my application and both, his and mine, query use IN wildcard which result in profile ids which contain either of skills while my question is to get profile ids which contain both the skills. I'm sure that there must be a way to fix this thing in the same query way which is listed in original question and i'm curious to learn that way . I hope that i'll get some more help with you guys - thanks

For clarity, I want to query all the profiles with multiple skills in a model with has_many relationship with profile model - using the Profile as primary table not the skills

Reason for using Profile as primary table is that in pagination i don't want to get all skills from related table ,say 20_000 or more rows and then filter according to profile.state column . instead anyone would like to select only 5 records which meet the profile.state , profile.user.is_active and other columns condition and match the skills without retrieving thousands of irrelevant records and then filter them again.

5 Answers

Answers 1

You should do this to get all profile_ids which have both accounting and administration skills :

Skill.where(name: ["accounting", "administration"]).group(:profile_id).having("count('id') = 2").pluck(:profile_id) 

If you need profiles details, you can put this query in where clause of Profile for id.

Note the number 2 in query, it is length of your array used in where clause. In this case ["accounting", "administration"].length

UPDATE::

Based on updated question description, instead of pluck you can use select and add subquery to make sure it happens in one query.

Profile.where(id: Skill.where(name: ["accounting", "administration"]).group(:profile_id).having("count('id') = 2").select(:profile_id)) 

More over you have control over sorting, pagination and additional where clause. Don't see any concerns over there which are mentioned in question edit.

UPDATE 2::

Another way to get intersect of profiles with both the skills (likely to be less efficient than above solution):

profiles = Profile  ["accounting", "administration"].each do |name|   profiles = profiles.where(id: Skill.where(name: name).select(:profile_id)) end 

Answers 2

Profile.includes(:skills).where("skills.name" => %w(accounting administration)) 

For more information, read about finding through ActiveRecord associations.

Update

If this is not working for you then you likely do not have your database and models properly configured, because in a brand new Rails app this works as expected.

class CreateProfiles < ActiveRecord::Migration[5.1]   def change     create_table :profiles do |t|       t.timestamps     end   end end  class CreateSkills < ActiveRecord::Migration[5.1]   def change     create_table :skills do |t|       t.string :name       t.integer :profile_id       t.timestamps     end   end end  class Profile < ApplicationRecord   has_many :skills end  class Skill < ApplicationRecord   belongs_to :profile end  Profile.create Profile.create Skill.create(name: 'foo', profile_id: 1) Skill.create(name: 'bar', profile_id: 1) Skill.create(name: 'baz', profile_id: 2)  Profile.includes(:skills).where("skills.name" => %w(foo))   SQL (0.3ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'foo' LIMIT ?  [["LIMIT", 11]]   SQL (0.1ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'foo' AND "profiles"."id" = 1  => #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]>  Profile.includes(:skills).where("skills.name" => %w(bar))   SQL (0.3ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'bar' LIMIT ?  [["LIMIT", 11]]   SQL (0.1ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'bar' AND "profiles"."id" = 1  => #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]>  Profile.includes(:skills).where("skills.name" => %w(baz))   SQL (0.3ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'baz' LIMIT ?  [["LIMIT", 11]]   SQL (0.1ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = 'baz' AND "profiles"."id" = 2  => #<ActiveRecord::Relation [#<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]> 

Update 2

Downvoting an answer because you changed your question later is poor form.

You should change your model relationships from has_many and belongs_to to has_and_belongs_to_many. This will allow you to stop recording a new skill every time; if someone adds the skill administration and then later on someone else adds that skill, you don't have to create a new skill. You just re-use the existing skill and associate it with multiple profiles:

class Profile < ApplicationRecord   has_and_belongs_to_many :skills end  class Skill < ApplicationRecord   has_and_belongs_to_many :profiles end 

Add a join table with a unique index (so each profile can have each skill once and only once):

class Join < ActiveRecord::Migration[5.1]   def change     create_table :profiles_skills, id: false do |t|       t.belongs_to :profile, index: true       t.belongs_to :skill, index: true       t.index ["profile_id", "skill_id"], name: "index_profiles_skills_on_profile_id_skill_id", unique: true, using: :btree     end   end end 

Create your models:

Profile.create Profile.create Skill.create(name: 'foo') Skill.create(name: 'bar') Skill.create(name: 'baz') Profile.first.skills << Skill.first Profile.first.skills << Skill.second Profile.second.skills << Skill.second Profile.second.skills << Skill.third 

And then run your query to return just the first profile:

skills = %w(foo bar).uniq Profile.includes(:skills).where('skills.name' => skills).group(:id).having("count(skills.id) >= #{skills.size}")   SQL (0.4ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN ('foo', 'bar') GROUP BY "profiles"."id" HAVING (count(skills.id) = 2) LIMIT ?  [["LIMIT", 11]]   SQL (0.2ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN ('foo', 'bar') AND "profiles"."id" = 1 GROUP BY "profiles"."id" HAVING (count(skills.id) = 2)  => #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]> 

Confirm with additional testing:

Should return both profiles:

skills = %w(bar).uniq Profile.includes(:skills).where('skills.name' => skills).group(:id).having("count(skills.id) >= #{skills.size}")   SQL (0.4ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" = 'bar' GROUP BY "profiles"."id" HAVING (count(skills.id) >= 1) LIMIT ?  [["LIMIT", 11]]   SQL (0.3ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" = 'bar' AND "profiles"."id" IN (1, 2) GROUP BY "profiles"."id" HAVING (count(skills.id) >= 1)  => #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">, #<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]> 

Should return just the second profile:

skills = %w(bar baz).uniq   SQL (0.3ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN ('bar', 'baz') GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2) LIMIT ?  [["LIMIT", 11]]   SQL (0.2ms)  SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN ('bar', 'baz') AND "profiles"."id" = 2 GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2)  => #<ActiveRecord::Relation [#<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]> 

Should return no profiles:

skills = %w(foo baz).uniq Profile.includes(:skills).where('skills.name' => skills).group(:id).having("count(skills.id) >= #{skills.size}")   SQL (0.3ms)  SELECT  DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN ('foo', 'baz') GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2) LIMIT ?  [["LIMIT", 11]]  => #<ActiveRecord::Relation []> 

Answers 3

PostgreSQL dependent solution:

where_clause = <<~SQL   ARRAY(     SELECT name FROM skills WHERE profile_id = profiles.id   ) @> ARRAY[?] SQL Profile.where(where_clause, %w[skill1 skill2]) 

It works, but it makes sense to change DB structure for speed-up. There are two options:

• has_and_belongs_to_many way adds consistency (skills tables turns into the dictionary) and ability to use indexes
• skills as array|jsonb column of profile - adds fast search by index without sub-selects or joins.

Answers 4

I would use EXISTS with a correlated sub-query, like this:

required_skills = %w{accounting administration} q = Profile.where("1=1") required_skills.each do |sk|   q = q.where(<<-EOQ, sk)     EXISTS (SELECT 1             FROM   skills s             WHERE  s.profile_id = profiles.id             AND    s.name = ?)   EOQ end 

There are some other ideas at this similar question but I think in your case multiple EXISTS clauses is the simplest and most likely fastest.

(By the way in Rails 4+ you can start with Profile.all instead of Profile.where("1=1"), because all returns a Relation, but in the old days it used to return an array.)

Answers 5

The problem with the following query

Profile.includes(:skills).where(skills: { name: ["accounting" , "administration"] } ) is that it create a query with IN operator like IN ('Accounting', 'Administration')

Now as per the SQA standard, it will match all the records which matches any value and not all the values from the array.

Here is a simplest solution

skills = ["accounting" , "administration"]  Profile.includes(:skills).where(skills: { name: skills }).group(:profile_id).having("count(*) = #{skills.length}") 

P.S. This assumes you will have at least one skill. Adjust having condition as per your usecase

Read More

Retrieve all association's attributes of an AR model?

How do you think is the more optimum way to retrieve all the attributes for each association that an AR model has?

i.e: let's say we have the model Target.

class Target < ActiveRecord::Base   has_many :countries   has_many :cities   has_many :towns   has_many :colleges   has_many :tags    accepts_nested_attributes_for :countries, :cities, ... end 

I'd like to retrieve all the association's attributes by calling a method on a Target instance:

target.associations_attributes >> { :countries => { "1" => { :name => "United States", :code => "US", :id => 1 },                       "2" => { :name => "Canada", :code => "CA", :id => 2 } },      :cities => { "1" => { :name => "New York", :region_id => 1, :id => 1 } },      :regions => { ... },      :colleges => { ... }, ....    } 

Currently I make this work by iterating on each association, and then on each model of the association, But it's kind of expensive, How do you think I can optimize this?

Just a note: I realized you can't call target.countries_attributes on has_many associations with nested_attributes, one_to_one associations allow to call target.country_attributes

2 Answers

Answers 1

I'm not clear on what you mean with iterating on all associations. Are you already using reflections?

Still curious if there's a neater way, but this is what I could come up with, which more or less results in the hash you're showing in your example:

class Target < ActiveRecord::Base   has_many :tags    def associations_attributes     # Get a list of symbols of the association names in this class     association_names = self.class.reflect_on_all_associations.collect { |r| r.name }     # Fetch myself again, but include all associations     me = self.class.find self.id, :include => association_names     # Collect an array of pairs, which we can use to build the hash we want     pairs = association_names.collect do |association_name|       # Get the association object(s)       object_or_array = me.send(association_name)       # Build the single pair for this association       if object_or_array.is_a? Array         # If this is a has_many or the like, use the same array-of-pairs trick         # to build a hash of "id => attributes"         association_pairs = object_or_array.collect { |o| [o.id, o.attributes] }         [association_name, Hash[*association_pairs.flatten(1)]]       else         # has_one, belongs_to, etc.         [association_name, object_or_array.attributes]       end     end     # Build the final hash     Hash[*pairs.flatten(1)]   end end 

And here's an irb session through script/console to show how it works. First, some environment:

>> t = Target.create! :name => 'foobar' => #<Target id: 1, name: "foobar"> >> t.tags.create! :name => 'blueish' => #<Tag id: 1, name: "blueish", target_id: 1> >> t.tags.create! :name => 'friendly' => #<Tag id: 2, name: "friendly", target_id: 1> >> t.tags => [#<Tag id: 1, name: "blueish", target_id: 1>, #<Tag id: 2, name: "friendly", target_id: 1>] 

And here's the output from the new method:

>> t.associations_attributes => {:tags=>{1=>{"id"=>1, "name"=>"blueish", "target_id"=>1}, 2=>{"id"=>2, "name"=>"friendly", "target_id"=>1}}} 

Answers 2

try this with exception handling:

class Target < ActiveRecord::Base    def associations_attributes     tmp = {}     self.class.reflections.symbolize_keys.keys.each do |key|       begin         data = self.send(key) || {}         if data.is_a?(ActiveRecord::Base)           tmp[key] = data.attributes.symbolize_keys!         else           mapped_data = data.map { |item| item.attributes.symbolize_keys! }           tmp[key] = mapped_data.each_with_index.to_h.invert         end       rescue Exception => e         tmp[key] = e.message       end     end     tmp   end  end 

Read More

`form_for` is bypassing model accessors. How to make it stop?

I set these methods to automatically encrypt values.

class User < ApplicationRecord   def name=(val)     super val.encrypt   end   def name     (super() || '').decrypt   end 

When I try to submit the form and there is an error (missing phone), then the name attribute shows up garbled.

<input class="form-control" type="text" value="Mg8IS1LB2A1efAeZJxIDJMSroKcq6WueyY4ZiUX+hfI=" name="user[name]" id="user_name"> 

It works when the validations succeeds. It also works in the console when I go line-by-line through my controller #update.

irb(main):015:0> u = User.find 1 irb(main):016:0> u.name => "Sue D. Nym" irb(main):017:0> u.phone => "212-555-1234" irb(main):018:0> u.update name: 'Sue D. Nym', phone: ''    (10.0ms)  BEGIN    (1.0ms)  ROLLBACK => false irb(main):020:0> u.save => false irb(main):029:0> u.errors.full_messages.join ',' => "Phone can't be blank" irb(main):031:0> u.build_image unless u.image => nil irb(main):033:0> u.name => "Sue D. Nym" 

users_controller.rb

  def update     @user = User.find current_user.id     @user.update user_params     if @user.save       flash.notice = "Profile Saved"       redirect_to :dashboard     else       flash.now.alert = @user.errors.full_messages.join ', '       @user.build_image unless @user.image       render :edit     end   end 

The view is somehow getting the encrypted value without going through #name, and only after a validation failure.

---

I reduced the controller to the absolute minimum and it fails immediately after #update. However, it's working on the console!

  def update     @user = User.find current_user.id     @user.update user_params     render :edit     return 

---

I reduced my view to the absolute minimum and it shows the name, but only outside of form_for. I don't know why yet.

edit.haml

=@user.name =form_for @user, html: { multipart: true } do |f|   =f.text_field :name 

HTML source

<span>Sue D. Nym</span> <form class="edit_user" id="edit_user_1" enctype="multipart/form-data" action="/users/1" accept-charset="UTF-8" method="post">   <input name="utf8" type="hidden" value="✓"><input type="hidden" name="_method" value="patch"><input type="hidden" name="authenticity_token" value="C/ScTxfENNxCKgzG0qAlPElOKI7nOYxZimQ7BsB64wIWQ9El4+vOAfxX3qHL08rbr0sxRiJnzQti13e4DAgkfQ==">     <input type="text" value="sER9cjwa6Ov5weXjEQN2KJYoTOXtVBytpX/cI/aPrFs=" name="user[name]" id="user_name"> </form> 

---

I noticed attributes still returned encrypted values so I tried adding this but form_for still manages to obtain the encrypted value and put it in the form!

  def attributes     attr_hash = super()     attr_hash["name"] = name     attr_hash   end 

Rails 5.0.2

3 Answers

Answers 1

While you can work around this by overloading name_before_type_case, I think this is actually the wrong place to be doing this kind of transformation.

Based on your example, the requirements here appear to be:

1. plaintext while in memory
2. encrypted at rest

So if we move the encrytion/decryption transformation to the Ruby-DB boundary, this logic becomes much cleaner & reusable.

Rails 5 introduced a helpful Attributes API for dealing with this exact scenario. Since you have provided no details about how your encryption routine is implemented, I'm going to use Base64 in my example code to demonstrate a text transformation.

class EncryptedTextType < ActiveRecord::Type::Text   # this is called when saving to the DB   def serialize(value)     Base64.encode64(value) unless value.nil?   end    # called when loading from DB   def deserialize(value)     Base64.decode64(cast value) unless value.nil?   end end  ActiveRecord::Type.register(:encrypted, EncryptedTextType) 

Now, you can specify this attribute as encrypted in the model:

class User < ApplicationRecord   attribute :name, :encrypted end 

The name attribute will be transparently encrypted & decrypted during roundtrips to the DB. This also means that you can apply the same transform to as many attributes as you like without rewriting the same code.

Answers 2

Why are you exposing it as name at all ?

class User < ApplicationRecord     def decrypted_name=(val)        name = val.encrypt     end      def decrypted_name        name.decrypt     end end 

Then you use @model.decrypted_name instead of @model.name as name is encrypted, and such saved in DB.

edit.haml =@user.decrypted_name =form_for @user, html: { multipart: true } do |f|   =f.text_field :decrypted_name 

And name if it is encrypted should not be handled directly but with this decrypted_name accessor.

Answers 3

I found this similar question: How do input field methods (text_area, text_field, etc.) get attribute values from a record within a form_for block?

I added

  def name_before_type_cast     (super() || '').decrypt   end 

And now it works!

Here is the full solution:

  @@encrypted_fields = [:name, :phone, :address1, :address2, :ssn, ...]   @@encrypted_fields.each do |m|     setter = (m.to_s+'=').to_sym     getter = m     getter_btc = (m.to_s+'_before_type_cast').to_sym     define_method(setter) do |v|       super v.encrypt     end     define_method(getter) do       (super() || '').decrypt     end     define_method(getter_btc) do       (super() || '').decrypt     end   end 

Some docs: http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/BeforeTypeCast.html

Read More

Rails: Self referential parent/child hierarchy without through table

I have an Event model with parent_id and date attributes:

Event.rb

has_many :children, :class_name => "Event" belongs_to :parent, :class_name => "Event"  

I have no issues calling event.parent or event.children. A child event never has a child itself.

I am trying to add a scope to this model so that I can return the child with the nearest future date for every parent. Something like:

scope :future, -> {     where("date > ?", Date.today)   }  scope :closest, -> {     group('"parent_id"').having('date = MAX(date)')  }  Event.future.closest ==> returns the closest child event from every parent 

But the above :closest scope is returning more than one child per parent.

3 Answers

Answers 1

I ended up using:

  scope :closest, -> {     where(id: Event.group(:parent_id).minimum(:date).keys)   } 

Answers 2

Your own answer looks good, but I would refine it the following way:

scope :closest, -> {   where.not(parent_id: nil).group(:parent_id).minimum(:date) } 

And very important or else in production you would always get the deployment date as Date.today because it will only reload in development:

scope :future, -> {   where("date > ?", Proc.new { Date.today }) } 

Answers 3

Ignoring Rails for a moment, what you are doing in SQL is the greatest-n-per-group problem. Here are lots of solutions. I would choose either DISTINCT ON or LEFT OUTER JOIN LATERAL. Here is how it might look in Rails:

scope :closest, -> {   select("DISTINCT ON (parent_id) events.*").     order("parent_id, date ASC") } 

This will give you the child objects. (You probably also want a condition to exclude rows with no parent_id.) From your own solutions, it sounds like that's what you want. If instead you want the parent objects, with an optional attached child object, then use a lateral join. That is a little trickier to translate into ActiveRecord though. If it's acceptable to do it in two queries, this looks like it should work (sticking with DISTINCT ON):

has_one :closest_child, -> {   select("DISTINCT ON (parent_id) events.*").     order("parent_id, date ASC") }, class_name: Event, foreign_key: "parent_id" 

Then you can say Event.includes(:closest_child). Again, you probably want to filter out all the non-parents though.

Read More

How to get most clicked records and at-least one child levels resource will be include

I have models like this with polymorphic relationship

class Level1  has_and_belongs_to_many :level2s  has_many :resources ,:as => :mediable end  class Level2     has_and_belongs_to_many :level1s     has_many :level3s     has_many :resources ,:as => :mediable end  class Level3     belongs_to :level2     has_many :resources ,:as => :mediable end  class Resource     belongs_to :mediable , polymorphic: true     has_many :resources ,:as => :mediable     has_many :clicks ,:as => :mediable end  class click     belongs_to :clickable , polymorphic: true end 

When user add a resource in level1/level2/level3(image or media) I show these media somewhere where user can click on this and each click I save an entry in clicks table

Now I need to when user on level1's show page I need to show top 50 resources of the level1s and level2s combined based on the click counts and at least one resource will be fetched from database

I am going to try like this:

Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("(resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')")             .group("resources.id")             .order("click_counts").limit(50) 

It will return top 50 resources related to level 1 and its related level2s but not not guarantee to I have at least one resources related to level2.

can you help me how can I do that

There are possibilities a resource never clicked but I have to get that resources as well as I need at least one resource for each level So I think inner join should change to left outer

1 Answers

Answers 1

If is not a problem for you do a little extra computation, you can achieve it easily with:

resources = Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("(resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')")             .group("resources.id")             .order("click_counts").limit(50)  unless resources.pluck(:mediable_type).include? 'Level2'    resources = resources.limit(49) + Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2'")             .group("resources.id")             .order("click_counts").limit(1) end 

Otherwise, you could try with something like this (not tested):

sub_select = "(    SELECT resources.*, count(clicks.id) as click_counts, 1 as SortKey    FROM resources     INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'    WHERE resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2'    GROUP BY resources.id    ORDER BY mediable_type, click_counts     LIMIT 1     UNION ALL     SELECT resources.*, count(clicks.id) as click_counts, 2 as SortKey    FROM resources     INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'    WHERE (resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')    GROUP BY resources.id    ORDER BY SortKey, mediable_type, click_counts) as t"  select_sql = "SELECT DISTINCT resources.*, click_counts FROM #{sub_select} LIMIT 50"  results = ActiveRecord::Base.connection.select_all(select_sql).rows # array results_ordered = results.sort { |a, b| a.last <=> b.last } 

Notes:

• The SortKey extra attribute guarantee the queries order
• UNION ALL does not eliminate any duplicate record. So we need to add a DISTINCT on resources.*, click_counts columns to remove a possibile duplicate record (the first of 'Level2')

Hope it helps!

Read More

How to get record which have min has_many rec ords(joins data)

user.rb

has_many :properties 

property.rb

belongs_to :user 

I want to get a user who have min properties like wise for max also.

I cant find any query related to that

3 Answers

Answers 1

To find the user with min properties you can simply do,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").last 

And to find the user with max properties,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").first 

Note: Because its a join operation with properties, so user with no properties will not appear in this query.

Answers 2

You could use counter_cache.

The :counter_cache option can be used to make finding the number of belonging objects more efficient.

From here

belongs_to :user, counter_cache: true 

Then create the migration:

def self.up   add_column :users, :properties_count, :integer, :default => 0    User.reset_column_information   User.find(:all).each do |u|     User.update_counters u.id, :properties_count => u.properties.length   end end 

Then you can fetch user which have max properties_count

User.maximum("properties_count") 

Here is an awesome RailsCast about counter_cache

Answers 3

I think you can do like this by scopes

class User   has_many :properties   scope :max_properties,     select("users.id, count(properties.id) AS properties_count").     joins(:properties).     group("properties.id").     order("properties_count DESC").     limit(1)    scope :min_properties,     select("users.id, count(properties.id) AS properties_count").     joins(:properties).     group("properties.id").     order("properties_count ASC").     limit(1) 

And just call User.max_properties and User.min_properties

UPDATED:

It will aslo work like BoraMa suggeted

class User   has_many :properties   scope :max_properties,     select("users.*, count(properties.id) AS properties_count").     joins(:properties).     group("users.id").     order("properties_count DESC").     limit(1)    scope :min_properties,     select("users.*, count(properties.id) AS properties_count").     joins(:properties).     group("users.id").     order("properties_count ASC").     limit(1) 

Read More

Rails - Keep a table out of structure.sql during migrations

It is straightforward to ignore tables when your schema format is :ruby, but is there a way to do it when your schema format is :sql?

Ideally something like this in environment.rb:

ActiveRecord::SQLDumper.ignore_tables = ['table_name'] 

After a quick perusal through the AR source code it looks unpromising.

1 Answers

Answers 1

There is currently no way to do this, when the schema format is set to :sql, Rails doesn't go through the regular SchemaDumper but instead uses the tasks in ActiveRecord::Tasks::PostgreSQLDatabaseTasks to do the dump, check it out here.

The code is quite straightforward. I came up with a simple patch for ActiveRecord that should work as expected. It relies on setting the tables to ignore in your database.yml file. It basically adds the following code:

ignore_tables = configuration['ignore_tables'] unless ignore_tables.blank?   args += ignore_tables.split(',').map do |table|     "-T #{table}"   end end 

I just submitted a pull request to rails with those changes. In case you'd want to test it.

Read More

How do I optimize an ActiveRecord find_in_batches query?

I'm using Rails 4.0.0 and Ruby 2.0.0. My Post (as in blog posts) model is associated with a user with a combination of the user's user_name, first_name, last_name. I'd like to migrate the data so that posts are associated to users by a foreign key, which is the user's id.

I have about 11 million records in the posts table.

I'm running the below code to migrate the data, using a rake task on a Linux server. However, my task keeps getting "Killed" by the sever, presumably due to the rake task, specifically the below code, consuming too much memory.

I've found that lowering the batch_size to 20 and increasing sleep(10) to sleep(60) allows the task to run longer, updating more records in total without being Killed, but takes significantly more time.

How can I optimize this code for speed and memory usage?

Post.where(user_id: nil).find_in_batches(batch_size: 1000) do |posts|   puts "*** Updating batch beginning with post #{posts.first.id}..."   sleep(10) # Hopefully, saving some memory usage.   posts.each do |post|     begin       user = User.find_by(user_name: post.user_name, first_name: post.first_name, last_name: post.last_name)       post.update(user_id: user.id)     rescue NoMethodError => error # user could be nil, so user.id will raise a NoMethodError       puts "No user found."     end   end   puts "*** Finished batch." end 

5 Answers

Answers 1

Do all the work in the database which is WAY faster than moving data back and forth.

This can be accomplished with ActiveRecord. Of course PLEASE test this before you unleash it on important data.

Post   .where(user_id: nil)   .joins("inner join users on posts.user_name = users.user_name")   .update_all("posts.user_id = users.id") 

Further, if posts have an index on user_id, and users has an index on user_name, then that will help this particular query run more quickly.

Answers 2

Check out the #uncached method on AR models. Basically, for request optimization, AR will cache a lot of query data as it is doing #find_in_batches, but it's a hinderance to large processing scripts like this.

Post.uncached do   # perform all your heavy query magic here end 

Ultimately, if that doesn't work, consider using the mysql2 gem to avoid the AR overhead, as long as you're not depending on any callbacks/business logic in the update.

Answers 3

If a join is possible I'd go with the approach from z5h. Otherwise you could add an index to the user model (possibly in a separate migration) and also skip the validations, callbacks and stuff when updating each post:

add_index :users, [:user_name, :first_name, :last_name] # Speed up search queries Post.where(user_id: nil).find_each do |post|   if user = User.find_by(user_name:  post.user_name,                          first_name: post.first_name,                          last_name:  post.last_name)     post.update_columns(user_id: user.id) # ...to skip validations and callbacks.   end end 

Please note that find_each is equivalent to find_in_batches + iterating over each post, but possibly not faster (see Rails Guides on Active Record Query Interface)

Good luck!

Answers 4

Combining other answers, I was able to join tables, and update multiple columns, in batches of 1000 rows, with a reduction in speed and without my process being killed by the server.

Here's the combines approach that I found to work best, keeping the code within the ActiveRecord API as much as possible.

Post.uncached do   Post.where(user_id: nil, organization_id: nil).find_each do |posts|     puts "** Updating batch beginning with post #{posts.first.id}..."      # Update 1000 records at once     posts.map!(&:id) # posts is an array, not a relation     Post.where(id: posts).       joins("INNER JOIN users ON (posts.user_name = users.user_name)").       joins("INNER JOIN organizations ON (organizations.id = users.organization_id)").       update_all("posts.user_id = users.id, posts.organization_id = organizations.id")      puts "** Finished batch."   end end 

Answers 5

Add new temporary boolean attribute updated

Post.where(updated: false).find_in_batches(batch_size: 1000) do |posts|   ActiveRecord::Base.transaction do     puts "*** Updating batch beginning with post #{posts.first.id}..."     posts.each do |post|       user = User.find_by(user_name: post.user_name, first_name: post.first_name, last_name: post.last_name)       if user         post.update_columns(user_id: user.id, updated: true)       else         post.update_columns(updated: true)       end     end     puts "*** Finished batch."   end end 

Read More

Rails and Azure: TinyTds::Error: Adaptive Server connection failed

I'm trying to configure my Rails project with SQL on Azure. I'm using Mac OS X 10.11.

This is part of my config/database.yml:

staging:   adapter: sqlserver    mode: dblib    host: db-staging.database.windows.net   port: 1433    database: db-staging   username: myuser@db-staging   password: mypass   timeout: 5000   azure: true  

When I run tsql seems like is everything ok:

$ tsql -H db-staging.database.windows.net -U myuser -P 'mypass' -v -p 1433 -D db-staging locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Setting db-staging as default database in login packet Changed database context to 'db-staging'. Changed language setting to us_english. 1>  

But when I run rake db:migrate I receive this error TinyTds::Error: Adaptive Server connection failed.

Here is the complete trace:

$ rake db:migrate RAILS_ENV=staging --trace                                                                       ** Invoke db:migrate (first_time) ** Invoke environment (first_time) ** Execute environment config.eager_load is set to nil. Please update your config/environments/*.rb files accordingly:    * development - set it to false   * test - set it to false (unless you use a tool that preloads your test environment)   * production - set it to true  ** Invoke db:load_config (first_time) ** Execute db:load_config ** Execute db:migrate rake aborted! TinyTds::Error: Adaptive Server connection failed /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/tiny_tds-0.7.0/lib/tiny_tds/client.rb:74:in `connect' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/tiny_tds-0.7.0/lib/tiny_tds/client.rb:74:in `initialize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/connection_adapters/sqlserver_adapter.rb:311:in `new' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/connection_adapters/sqlserver_adapter.rb:311:in `dblib_connect' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/connection_adapters/sqlserver_adapter.rb:302:in `connect' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/connection_adapters/sqlserver_adapter.rb:58:in `initialize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/sqlserver_base.rb:17:in `new' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.10/lib/active_record/sqlserver_base.rb:17:in `sqlserver_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:438:in `new_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:448:in `checkout_new_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:422:in `acquire_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:349:in `block in checkout' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:348:in `checkout' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:263:in `block in connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:262:in `connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:571:in `retrieve_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_handling.rb:113:in `retrieve_connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_handling.rb:87:in `connection' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:912:in `initialize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:819:in `new' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:819:in `up' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:797:in `migrate' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/tasks/database_tasks.rb:137:in `migrate' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/activerecord-4.2.3/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `call' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `block in execute' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `each' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `execute' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/2.0.0/monitor.rb:211:in `mon_synchronize' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/task.rb:173:in `invoke' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:150:in `invoke_task' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block (2 levels) in top_level' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `each' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block in top_level' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:115:in `run_with_threads' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:100:in `top_level' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:78:in `block in run' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:176:in `standard_exception_handling' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/lib/rake/application.rb:75:in `run' /Users/monteirobrena/.rvm/rubies/ruby-2.0.0-p481/lib/ruby/gems/2.0.0/gems/rake-11.1.2/bin/rake:33:in `<top (required)>' /Users/monteirobrena/.rvm/gems/ruby-2.0.0-p481/bin/rake:23:in `load' /Users/monteirobrena/.rvm/gems/ruby-2.0.0-p481/bin/rake:23:in `<main>' Tasks: TOP => db:migrate 

I saw other questions like this, but did not help me:

TinyTds::Error: Adaptive Server connection failed

TinyTds Error: Adaptive Server connection timed out

[Updated]

I tried do exactly like here: https://github.com/Azure/azure-sql-database-samples/tree/master/Ruby%20on%20Rails/Sample%20Mac

Following this post I found the correct configurations to set in my config/database.yml.

https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-ruby-simple-mac-osx/

staging:   adapter: sqlserver   username: 'username@database'   password: 'password'   host: 'db-staging.database.windows.net'   port: 1433   database: 'db-staging'   azure: true 

And add this gems im my Gemfile:

gem 'tiny_tds' gem 'activerecord-sqlserver-adapter' 

Now when I access my project folder and run rails s I can connect with my database and everything work fine. But, if I run my project with Puma and Nginx, I receive this error:

TinyTds::Error (Adaptive Server connection failed): 

[/Updated]

1 Answers

Answers 1

I need install FreeTDS by myself, if apt-get we can't pass the arguments of compilation:

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz  $ tar -zxvf freetds-stable.tgz  $ cd freetds-0.91/  $ ./configure --with-tdsver=8.0 --with-openssl=/usr/bin  $ make  $ sudo make install 

And now I can access my database:

$ tsql -C # OK!  $ TDSVER=8.0 tsql -H mydb.database.windows.net -p 1433 -U myuser@mydb -P mypassword -D mydb 

And everything works when I run and access with Puma, Nginx and Capistrano.

See more here:

https://github.com/rails-sqlserver/tiny_tds/issues/266

Read More

Rails: Validate uniqueness of multiple columns

Is there a rails-way way to validate that an actual record is unique and not just a column? For example, a friendship model / table should not be able to have multiple identical records like:

user_id: 10 | friend_id: 20 user_id: 10 | friend_id: 20 

3 Answers

Answers 1

You can scope a validates_uniqueness_of call as follows.

validates_uniqueness_of :user_id, :scope => :friend_id 

Answers 2

You can use validates to validate uniqueness on one attribute:

validates :user_id, uniqueness: {scope: :friend_id} 

The syntax for the validation on multiple columns is similar, but you should provide an array of fields instead:

validates :attr, uniqueness: {scope: [:attr1, ... , :attrn]} 

---

However, approaches that are shown above suffer from race conditions, consider the following example:

1. database table records are supposed to be unique by n fields;

2. multiple (two or more) concurrent requests, handled by separate processes each (application server, sidekiq or whatever you are using), try to write the same record to the table;

3. each process in parallel validates if there is a record with the same n fields;

4. validation for each request is passed and each process creates a record in the table with the same data.

To avoid this kind of behaviour, one should add a unique constraint to the db table. You can set it with add_index for multiple (or one) fields by running the following migration:

class AddUniqueConstraints < ActiveRecord::Migration   def change    add_index :table_name, [:field1, ... , :fieldn], unique: true   end end 

Caveat : even after you've set the unique constraint, two or more concurrent requests will try to write the same data to the db, but instead of creating duplicate records, this will result in the raise of the ActiveRecord::RecordNotUnique exception, which you should handle separately:

begin # writing to the database rescue ActiveRecord::RecordNotUnique => e # handling the case when record already exists end  

Answers 3

You probably do need actual constraints on the db, because validates suffers from race conditions.

validates_uniqueness_of :user_id, :scope => :friend_id 

When you persist a user instance, Rails will validate your model by running a SELECT query to see if any user records already exist with the provided user_id. Assuming the record proves to be valid, Rails will run the INSERT statement to persist the user. This works great if you’re running a single instance of a single process/thread web server.

In case two processes/threads are trying to create a user with the same user_id around the same time, the following situation may arise.

With unique indexes on the db in place, the above situation will play out as follows.

Answer taken from this blog post - http://robots.thoughtbot.com/the-perils-of-uniqueness-validations

Read More

find all that are nil in the association in ruby on rails

Here I've got a 1-to-many relationship between Products and Users:

class Property < ActiveRecord::Base   has_many :users end  class User < ActiveRecord::Base   belongs_to :property end 

How could I get all the properties which do not belong to any user?

6 Answers

Answers 1

To get all properties that have no user, try this:

Property.includes(:users).where(users: { property_id: nil }) 

Answers 2

You can try this query:

Property.where.not(:id=>User.where.not(:property_id=>nil).pluck(:property_id)) 

or

 Property.where.not(:id=>User.where.not(:property_id=>nil).pluck("DISTINCT property_id")) 

Answers 3

One more approach would be to write some SQL:

Property.joins("LEFT OUTER JOIN users ON users.property_id = properties.id"). where('users.id IS NULL'). uniq 

The code above is being translated to the following pure SQL query to the database:

SELECT DISTINCT properties.* FROM properties  LEFT OUTER JOIN users on users.property_id = properties.id  WHERE users.id IS NULL; 

LEFT JOIN keyword returns all rows from the left table (properties), with the matching rows in the right table (users). The result is NULL in the right side when there is no match. Afterwards WHERE keyword filters results by a condition that we're intrested in those rows which have NULL on the right side only.

Reference: SQL LEFT JOIN Keyword

Answers 4

You can do it like this too:

Property.where('id NOT IN (SELECT DISTINCT(property_id) FROM users)') 

Another option would be:

Property.where("(select count(*) from users where property_id = properties.id) = 0") 

You can always check which is more efficient according to you application by checking the time take to execute the queries and choose an option accordingly.

Answers 5

use this code:

@users= User.includes(:properties).where(properties: { property_id: nil }) 

Answers 6

Also You can write scope based on this query just for easy use.

class Property < ActiveRecord::Base   has_many :users   scope :incomplete, -> { joins(:users).where("property.user_id is null") } end 

Then, You can call this scope like this: Property.incomplete

Read More