Ruby - Return record with longest string in column-CodePudding

I just need the record with the longest string in the product_description column.

A record could have nil in the product_description column

Which is why this won't work:

Product.where(parent_product_id: 22033).pluck(:product_description).max_by(&:length)

Then I try SQL and get:

ActiveRecord::UnknownAttributeReference (Query method called with non-attribute argument(s): "max(length(product_description))")

From this query:

Product.where(parent_product_id: 22033).pluck("max(length(product_description))")

This returns the same:

Product.where(parent_product_id: 22033).order("MAX(CHAR_LENGTH(product_description)) desc").limit(1)

But product_description is definitely a column on the Products table.. that's not the issue

CodePudding user response：

You can order by length and take first like this

Product
  .where(parent_product_id: 22033)
  .where.not(product_description: nil)
  .order("LENGTH(product_description) DESC")
  .first

LENGTH is RDBMS function and depends on specific system and may differ therefore

CodePudding user response：

You can use the length function of your RDBMS to calculate the length, then order by it.

Ties

There might be many products with the same description length. In order to have consistent results, you will need a tie breaker as otherwise the order within the products with same description length is not defined. You could ass an order by id clause.

NULL

Be aware that

select length(null)

will return null and not 0. null might be sorted before actual values or after (depending on your RDBMS and its config).

If you always need a numeric value you can do

select length(coalesce(null, ''))

which will return 0 coalescereturns the firstnon-nullargument and therefore ensures that we always pass at least an empty string tolength`.

You can also use the null last option for the order clause.

You can also exclude records with a null value for the description:

products = Product.where.not(product_description: nil)

to avoid dealing with null values alltogether.

If the collumn is not nullable, then there is no problem either.

Now if you just use this:

products = Product.all # or whatever conditions you need
products
  .order("length(coalesce(product_description, '')) desc")
  .order(id: :asc)
  .first

then Rails might complain (depends on the Version you are using) for security reasons with something like ActiveRecord::UnknownAttributeReference: Dangerous query method

which means you need to wrap the whole thing in Arel.sql

products = Product.all # or whatever conditions you need
products
  .order(Arel.sql("length(coalesce(product_description, '')) desc"))
  .order(id: :asc)
  .first

Index

If you have many records, you might want to add an index on the column öength. See https://sqlfordevs.com/function-based-index for how to create a funciton based index.