Find count of predefined words using active record or pure postgres query

Asked Sep 27 '16 at 12:30

Active Sep 27 '16 at 12:43

Viewed 65 times

I want to get some text analysis. As a result I want to get count of some key words in all records like this:

answer id: 1, text: 'some good text'

answer id: 2, text: 'some really bad text, angry angry angry text'

and result should be like this:

good: 1, bad: 1, angry: 3

I don't want to select all records and then map them count this key words on ruby etc because of big amount of data. Also I find some solution:

Answer.pluck('regexp_matches(text, \'\mMyKeyword\M\', \'gi\')').flatten.count

Works fine for 1 keyword. This causes problem that I used to do database request per keyword. I'm using rails 4 and potgres. Any help appreciated

asked Sep 27 '16 at 12:30

How the resultant `key` are taken, Aren't they are non-common words of the two strings – Pravesh Khatri Sep 27 '16 at 12:56
looks like this http://stackoverflow.com/questions/5226202/word-frequencies-from-strings-in-postgres can help you, go to the answer that starts with `You could also use the PostgreSQL text-searching functionality for this, for example:` – fanta Sep 27 '16 at 15:23
keys are predefined in array – Konstantin Plyusnin Sep 28 '16 at 10:20
I saw this answer. But the problem is I got all words count when I while I'm only interested in 5-10 words – Konstantin Plyusnin Sep 28 '16 at 10:27

0 Answers0