Rails - Encrypted Data Storage with Raw SQL Queries

Question

I have users table and redemptions Table. So for security reasons we have encrypted our users table email and mobile column.

User Table columns before encryption:

id | mobile | email | name | 

User Table columns after encryption:

id | name | email_ciphertext | mobile_ciphertext | email_bidx | mobile_bidx

User.rb

has_many :redemptions

Redemption.rb

belongs_to :User

So In one place I wanted to get Redemption information along with User details. My query before encrypting the User's mobile and email was

Redemption.joins(:user).group('users.id, users.mobile').select('users.id AS uid, users.name AS uname, users.email AS uemail, users.mobile AS umobile, count(distinct(redemptions.id)) AS total)

So this would give me Users and their redemptions count. Now that I have encrypted my data using Lockbox and BlindIndex and dropped both email and mobile column from users table, what approach should I follow to achieve the same result for the above query.

Answer 1

If you're trying to get redemption count by user, you can do:

redemptions_by_user_id = Redemption.joins(:user).group("users.id").distinct.count

Then query the user information in a separate query.

users = User.where(id: redemptions_by_user_id.keys)

Then combine them

users.each do |user|
  puts "#{user.id} #{user.email} #{user.phone} #{redemptions_by_user_id[user.id]}"
end