#StackBounty: #ruby-on-rails #activerecord #arel matches_any throws exception on empty array writing custom filter for Datatables using…

Bounty: 100

I have am working with Ruby on Rails using the ajax-datatables-rails gem for datatables for which I need a custom filter.

My current filter looks like this:

def filter_status_column
  statuses = []
  ->(column, value) do
    # Do stuff and put statuses in the array

This, when my array is not empty will generate some sql like this:

0> ::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches_any(like_mapped_values).to_sql
=> "(status ILIKE 'Foo' OR status ILIKE 'Bar')"

If the array this causes an exception which my expectation is after running .where("status in ?", []) against the model is like this as that turns [] to null:

"(status ILIKE NULL)"



generates the error

Unsupported argument type: NilClass. Construct an Arel node instead.

What is the correct method to handle an empty array in matches_any? I could also do this without arel I suppose as well. Status is just a column on a model

EDIT: Further background, this datatable on the UI side has a search box and this field differs in text between what’s displayed and what’s actually in the database. This custom filter takes the distinct values from the database and maps the view text to the meaningful database text. It “likes” the viewed text, takes the match from the database side, and needs to apply filters as it goes. So, a partial match on the view text is matched to the actual database match. This means there could be no database matches and match_any? pukes on that.

Get this bounty!!!

#StackBounty: #ruby-on-rails #postgresql #activerecord Get the latest child messages and also parent messages that are childless

Bounty: 100

The following is the Message model

class Message < ApplicationRecord
  belongs_to :parent_message, class_name: 'Message', optional: true
  has_many :child_messages, foreign_key: :parent_message_id, class_name: "Message"
  has_many :message_participants

  scope :latest_messages_by_participant, -> (user_id) do
    select("DISTINCT ON (parent_message_id) messages.*").
        where(message_participants: { user_id: user_id }).
        order("parent_message_id, created_at DESC")

message_participants has a record for each message and the various people who have sent or received that message. It has a user_id on it.

The problem with the above latest_messages_by_participant scope is that it is able to get all the child messages BUT it gets only the last parent message. That is because we are calling DISINTICT ON on parent_message_id, and for childless parent messages this value is NULL and so it’s just calling distinct on NULL and returns on 1 value (the last childless parent message).

How can I fetch all the latest messages including the latest child messages AND the latest childless parent message in a single query?

I’m using Rails 6 and Postgres 11.

P.S: I should also point out a secondary problem which is that the messages are returned in created_at ASC. The created_at DESC is able to get the latest child message but does not sort the overall collection. I can solve this by calling .reverse, but wondering if there was a way to fix that as well.

Get this bounty!!!

#StackBounty: #ruby-on-rails #forms #validation #activerecord #simple-form Rails custom validation that limits the number of has_many :…

Bounty: 50

I have a Rails project with a “Product Variant” form. The Product variant model is called Variant, and on the Variant form users should be able to select one choice for each option available. For instance a T-Shirt might have an “Option” called “Size” with the “Choices” small, medium, or large, and another “Option” called one “Color” with the “Choices” red, green, blue. Thus, the Variant created is a unique SKU such as a “T-shirt — Size: Small, Color: Green.” Or if it were a product that had 3 options instead of 2, the variant would require 3 choices per option, such as “Guitar Strap – Size: Long, Fabric Color: Red, Leather Color: Brown”.

I can’t figure out how to write a custom validation that only allows the user to save one choice per option. Each option should only have one choice selected for each variant. Here’s an illustration.

need a validation that prevents this

correct outcome

Here are my models with the relevant associations…


class Variant < ApplicationRecord    
  has_many :selections
  has_many :choices, through: :selections

  validate :one_choice_per_option

    def one_choice_per_option
      # can't figure out how to do this custom validation here



class Choice < ApplicationRecord
  has_many :variants, through: :selections

  belongs_to :option


class Selection < ApplicationRecord
  belongs_to :choice
  belongs_to :variant


class Option < ApplicationRecord
  has_many :choices, dependent: :destroy

  accepts_nested_attributes_for :choices, allow_destroy: true

The best I’ve managed to do is get this custom validation working on in models/variant.rb

def one_choice_per_option
  self.product.options.each do |option|
    if option.choices.count > 1
      errors.add(:choice, 'Error: select one choice for each option')

But that only allows one Choice total through the variant form. What I want to do is allow one choice for each set of options.

I know that this could be done with Javascript in the UI, but this is essential to keeping the database clean and preventing a user error, so I believe it should be a Rails validation at the model level.

What is a “Railsy” way to do this type of custom validation? Should I be trying to do a custom validation on the Selection model? If so, how?


Based on the discussion in the comments. It seems that I need to do some combination of Active Record querying to make this work. @sevensidedmarble’s “EDIT 2” below is closer but that is giving me this error: Type Error compared with non class/module

If I save the wrong behavior to the database and then call Variant.last.choices in the console it feels like I’m getting closer:

illustrating the form behavior I need to prevent with a validation

showing the console response

So essentially, what I need to do is not allow the Variant form to save if there is more than one Selection with the same option_id. A selection shouldn’t save unless the option_id is unique to the associated Variant.

Something like this is what I’m trying to do:

validate :selections_must_have_unique_option


    def selections_must_have_unique_option
      unless self.choices.distinct(:option_id)
        errors.add(:options, 'can only have one choice per option')

But that code doesn’t work. It just saves the form as if the validation weren’t there.

Get this bounty!!!

#StackBounty: #ruby-on-rails #postgresql #activerecord Is it OK to specify a schema in `table_name_prefix`?

Bounty: 50

TL;DR: Is it OK to specify a schema in table_name_prefix?

We have a large Rails application that is not quite a traditional multi-tenant app. We have a hundred clients, all supported by one app, and that number will never grow more than 1-2 per year. Currently, every client has their own Postgresql database.

We are addressing some infrastructure concerns of having so many distinct databases…most urgently, a high number of simultaneous database connections when processing many clients’ data at the same time.

The app is not visible, even to clients, so a lot of traditional multi-tenant web site philosophies don’t apply here neatly.

  • Each tenant has a distinct Postgres database, managed in
  • Each database has a schema, named for the tenant.
  • We have a model specific to each tenant with notably different code.
  • Each model uses establish_connection to select a different database and schema.
  • Each model uses a distinct table_name_prefix with the client’s unique name.

The tables vary extensively for each tenant. There is no hope or desire to normalize the clients together. Clients are not provisioned dynamically — it is always a new code release with migrations.

We intend to move each of the client schemas into one database, so fewer distinct connection pools are required. The unique names we currently have at the database, schema, and table names mean there is no possibility of name collisions.

We’ve looked at the Apartment gem, and decided it is not a good fit for what we’re doing.

We could add all hundred schemas to schema_search_path, so all clients could share the same connection pool and still find their schema. We believe this would reduce our db connection count one-hundred-fold. But we’re a bit uneasy about that. I’ve found no discussions of how many are too many. Perhaps that would work, and perhaps there would not have a performance penalty finding tables.

We’ve found a very simple solution that seems promising, by adding the schema in the table_name_prefix. We’re already setting this like:

def self.table_name_prefix

Through experimenting and looking within Rails 4 (our current version) and Rails 5 source code, this works to specify the schema (‘tenant_99’) as well as the traditional table prefix (‘client99’) :

def self.table_name_prefix

Before that change, queries looked like this:

SELECT COUNT(*) FROM "client99_products"

After, they include the schema, as desired:

SELECT COUNT(*) FROM "tenant_99.client99_products"

This seems to answer our needs, with no downsides. I’ve searched the Interwebs for people encouraging or discouraging this practice, and found no mention of it either way.

So through all this, here are the questions I haven’t found definitive answers for:

  • Is there a concern of having too many schemas listed in schema_search_path?
  • Is putting a schema name in table_name_prefix okay?

Get this bounty!!!

#StackBounty: #scala #join #activerecord #group-by Write join query with groupby in Scala ActiveRecord

Bounty: 150

I am trying to write a specific query in scala Active record. But it always returns nothing. I have read the wiki on the github page but it does not contain a lot of info on it. The query I am trying to write is

SELECT e.name, e.id, COUNT(pt.pass_id) as pass_count, e.start_date, e.total_passes_to_offer
FROM events e inner join passes p on e.id = p.event_id inner join pass_tickets pt on p.id = pt.pass_id where e.partner_id = 198 group by e.name, e.id

What I have tried is

Event.joins[Pass, PassTicket](
                (event, pass, passTicket) => (event.id === pass.eventId, pass.id === passTicket.passId)
                (event, _, _) => event.partnerId === partnerId
                (event, pass, _) => (event.name, event.id, PassTicket.where(_.passId === pass.id).count, event.startDate, event.totalPassesToOffer)
            ).groupBy( data => data._2)

But first, the return type becomes a map, not a list. And second when executed, it doesnt return anything even though the data exists and the SQL executes fine.

Get this bounty!!!