Rails where word count in string column greater than?

Question

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)

Swapnil Chincholkar · Accepted Answer · 2019-12-05 13:31:18Z

3

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

edited Dec 5, 2019 at 13:31

Swapnil Chincholkar

2,9093 gold badges25 silver badges22 bronze badges

answered Oct 9, 2014 at 6:07

hellion

4,9206 gold badges43 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Swapnil Chincholkar Over a year ago

Perfect answer for newer versions of PSQL when array columns are introduced.. Instead of manipulating array in memory, let Db server handle it.

John · Accepted Answer · 2014-10-09 06:06:26Z

1

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 }

answered Oct 9, 2014 at 6:06

John

4,3825 gold badges34 silver badges50 bronze badges

1 Comment

hellion Over a year ago

Data type array didnt work for me after all. I reverted back to basic string type column. This is the best answer so far.

Craig Ringer · Accepted Answer · 2014-10-09 06:00:23Z

0

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.

answered Oct 9, 2014 at 6:00

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

1 Comment

hellion Over a year ago

Thanks for answer. I'm using ActiveRecord. Postgres offers an array column type that, if set up correctly, provides Flight.where("array_length(route, 1) >= 3"). I didnt know I needed the column to be of type array in order for that to work.

Collectives™ on Stack Overflow

Rails where word count in string column greater than?

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related