My application defines a list of user's preferred activities as an array of strings stored in a Postgres character varying array field.
The field is defined in Postgres DDL as:
preferred_activities character varying[] COLLATE pg_catalog."default" DEFAULT '{}'::character varying[]
The field is initialised by an API which receives a comma separated list of values (userActivities ) such as "CLIMB,RUN,212" with the following instruction:
@user.preferred_activities = userActivities.split(',')
This results in the expected list stored as {CLIMB,RUN,212} in Postgres field and displayed as expected in the view: CLIMB,RUN,212
As the field can also be manually edited, it also appears in user's form where it is initialised with a consistent format:
<%= f.text_field :preferred_activities, value: f.object.preferred_activities.join(',') %>
which provides the expected field value attribute "CLIMB,RUN,212" (displayed as CLIMB,RUN,212).
And then formatted before user's record update:
@user.preferred_activities = user_params[:preferred_activities].split(',')
which produces the following array: ["CLIM", "RUN", "212"]
This update should be straight forward, but for a reason I don't understand, the following SQL instruction is generated:
User Update (0.9ms) UPDATE "users" SET "preferred_activities" = $1, "updated_at" = $2 WHERE "users"."id" = $3
[["preferred_activities", "{LIM,\"RUN\"}"], ["updated_at", "2022-03-17 12:26:25.195321"], ["id", 1]]
The first letter and the last word have disappeared!
I did try other syntaxes for the instruction, which lead to the same result:
@user.preferred_activities = user_params[:preferred_activities]
@user.preferred_activities = "{#{user_params[:preferred_activities]}}"
Do you see any explanation for this behaviour and any solution to properly update this character varying array field?
User model:
class User < ApplicationRecord
extend CsvHelper
# Audit trail setup
audited except: [:encrypted_password, :reset_password_token, :reset_password_sent_at, :remember_created_at,
:sign_in_count, :current_sign_in_at, :last_sign_in_at, :current_sign_in_ip, :last_sign_in_ip,
:confirmation_token, :confirmed_at, :confirmation_sent_at, :unconfirmed_email,
:failed_attempts, :unlock_token, :locked_at]
#validates_with EmailAddress::ActiveRecordValidator, field: :email
# Virtual attribute for authenticating by either username or email
# This is in addition to a real persisted field like 'username'
attr_accessor :login
# Include default devise modules. Others available are:
# :confirmable, :lockable, :timeoutable and :omniauthable
devise :database_authenticatable, :recoverable, :rememberable,
:trackable, :confirmable, :lockable, :password_archivable, :registerable,
:omniauthable, omniauth_providers: %i[keycloakopenid]#, :secure_validatable
before_save :email_format
before_save :name_update
### validations
# validates :current_playground_id, presence: true
validates :email, :presence => true, uniqueness: {scope: :playground_id, case_sensitive: false}, length: { maximum: 100 }
#validates_format_of :email, with: /\A(\S+)@(.+)\.(\S+)\z/
validate :password_confirmed
validate :password_complexity
validates :first_name, presence: true, length: { maximum: 100 }
validates :last_name, presence: true, length: { maximum: 100 }
validates :user_name, presence: true, uniqueness: {scope: :playground_id, case_sensitive: false}, length: { maximum: 100 }
validates :external_directory_id, length: { maximum: 100 }
validates :created_by, presence: true, length: { maximum: 30 }
validates :updated_by, presence: true, length: { maximum: 30 }
#validate :member_of_Everyone_group
validates :organisation, presence: true
belongs_to :organisation
belongs_to :parent, :class_name => "Playground", :foreign_key => "playground_id"
belongs_to :owner, :class_name => "User", :foreign_key => "owner_id"
# Relations
has_and_belongs_to_many :groups
has_many :groups_users
### Translation support
mattr_accessor :translated_fields, default: ['description']
has_many :translations, as: :document
has_many :description_translations, -> { where(field_name: 'description') }, class_name: 'Translation', as: :document
accepts_nested_attributes_for :translations, :reject_if => :all_blank, :allow_destroy => true
accepts_nested_attributes_for :description_translations, :reject_if => :all_blank, :allow_destroy => true
### Public functions
def activity_status
if self.is_active
if not self.locked_at.nil?
"locked"
else
if self.confirmed_at.nil?
"Unconfirmed"
else
if self.sign_in_count == 0
"Confirmed"
else
"Active"
end
end
end
else
"Inactive"
end
end
def principal_group
self.groups_users.find_by(is_principal: true).group_id
end
def password_complexity
# Regexp extracted from https://stackoverflow.com/questions/19605150/regex-for-password-must-contain-at-least-eight-characters-at-least-one-number-a
return if password.blank? || password =~ /^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,70}$/
errors.add :password, 'Complexity requirement not met. Length should be 8-70 characters and include: 1 uppercase, 1 lowercase, 1 digit and 1 special character'
end
def password_confirmed
return if password == password_confirmation
errors.add :password, 'Password and confirmation do not match'
end
### full-text local search
pg_search_scope :search_by_user_name, against: [:user_name, :name, :description],
using: { tsearch: { prefix: true, negation: true } }
def self.search(criteria)
if criteria.present?
search_by_user_name(criteria)
else
# No query? Return all records, sorted by hierarchy.
order( :updated_at )
end
end
# Allow user creation when a new one comes through OmniAuth
def self.from_omniauth(auth)
where(provider: auth.provider, email: auth.info.email).first_or_create do |user|
user.login = auth.info.email
user.uuid = auth.uid
user.provider = auth.provider
user.email = auth.info.email
user.password = "Odq!1#{Devise.friendly_token[0, 20]}"
user.password_confirmation = user.password
user.first_name = auth.info.first_name
user.last_name = auth.info.last_name
user.name = auth.info.name # assuming the user model has a name
user.user_name = auth.info.email[0, 32]
user.playground_id = 0
user.current_playground_id = 0
user.organisation_id = Parameter.find_by_code("ORGANISATION_ID").property.to_i || 0
user.language = 'fr_OFS'
user.description = 'Created through OmniAuth'
user.active_from = Time.now
user.active_to = Time.now + 3.years
user.is_admin = false
user.is_active = true
user.owner_id = 1
user.created_by = 'OmniAuth'
user.created_at = Time.now
user.updated_by = 'OmniAuth'
user.updated_at = Time.now
user.confirmed_at = Time.now
# If you are using confirmable and the provider(s) you use validate emails,
# uncomment the line below to skip the confirmation emails.
user.skip_confirmation!
end
end
### private functions definitions
private
### before filters
def email_format
self.email = email.downcase
end
def name_update
self.name = "#{first_name} #{last_name}"
end
def self.find_for_database_authentication(warden_conditions)
conditions = warden_conditions.dup
if login = conditions.delete(:login)
where(conditions.to_h).where(["lower(user_name) = :value OR lower(email) = :value", { :value => login.downcase }]).first
elsif conditions.has_key?(:user_name) || conditions.has_key?(:email)
where(conditions.to_h).first
end
end
def member_of_Everyone_group
errors.add(:base, :EveryoneMembershipMissing) unless group_ids.include? 0
end
end
