CODEWITHSUNDEEP
postgresqlruby-on-rails-4
hellion
hellion

Reputation: 4830

Rails where word count in string column greater than?

I need to filter records by how many words exist in a certain column.

the following works, but of course returns a character count...I need a word count

 Flight.where("length(route) > 3")

Is there a rails way (or any way) to do this?

This is getting closer (its right out of the rails docs) but results in error

 Flight.where("array_length(route, 1) >= 3")

 ERROR: aggregate functions are not allowed in WHERE
 ERROR:  function array_length(character varying, integer) does not exist

Docs also suggest using HAVING .. also not working

Flight.having("route.count > ?", 2)

Upvotes: 3

Views: 1742

Answers (3)

hellion
hellion

Reputation: 4830

Flight.where("array_length(route, 1) >= 3") does work, but the column must be set as type array.

see this post for more Rails Migration changing column to use Postgres arrays

Upvotes: 3

John
John

Reputation: 4372

You can use split to create an array from a string with words spaced by whitespace within the string and count the total.

Flight.select(:route).select { |flight| flight.route.split(" ").length > 3 }

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324385

Assuming you want quite a naïve definition of "word" as "things with spaces in between", split the string and get the length of the resulting array.

In plain SQL, that'd be:

SELECT array_length(string_to_array(route, ' '), 1);

e.g.

SELECT array_length(string_to_array('this is - five, words', ' '), 1);

If you want to get fancier and ignore punctuation etc, use regexp_split_to_array. You can also look at using to_tsvector to create a stemmed and de-duplicated form if desired.

Upvotes: 0

Related Questions