Teacher search

[peteryates]

This is some prep work for the upcoming addition of a teacher list for ABs. We will want to provide basic searching and filtering, and this change implenents searching.

• Searches that contain 7 digit numbers will search by TRN. If the search term contains multiple 7 digit numbers all will be returned.
• Blank searches will return all teachers, this will be the default state when someone navigates to the page
• Searches with strings of text will use PostgreSQL's full text indexing. For now this is lexeme based, which is really fast. If we want to support matching when something almost matches we can easily implement that too with trigrams via pg_tgrm.

Changes

• Add search column for teachers
• Add search scope to Teacher model
• Add Teachers::Search class with basic searching

How it works

Under the hood, there's a new generated field that's ignored by Rails on the Teacher record called search.

If we pull it directly from the database we can see it looks like this:

ecf2_development=# select * from teachers where id = 1;
┌─[ RECORD 1 ]───┬──────────────────────────────────────────────────────────────────────────────────┐
│ id             │ 1                                                                                │
│ preferred_name │ Charita Konopelski Esq. Walter Feeney                                            │
│ created_at     │ 2024-09-27 15:38:07.436408                                                       │
│ updated_at     │ 2024-09-27 15:38:07.436408                                                       │
│ trn            │ 1000000                                                                          │
│ first_name     │ Charita Konopelski Esq.                                                          │
│ last_name      │ Feeney                                                                           │
│ search         │ 'charita':1 'esq':5 'esq.feeneycharita':3 'feeney':7 'konopelski':2,4 'walter':6 │
└────────────────┴──────────────────────────────────────────────────────────────────────────────────┘

It contains a tsvector which is used by the full text search for matching. The field is indexed which makes matching really fast (see this doc I wrote a few years ago).

That allows us to convert our search string to a tsquery and match.

I'm using PostgreSQL's websearch_to_tsvector function here which gives us some nice stuff for free, like you can search how you might on Google. For example,Bill or Ben and records that match either Bill or Ben are returned. Also you can prepend a - on a word to negate it, like Flags -Paving.

Most people won't notice this stuff and it'll just act like a normal search.

So, we can search like this:

ecf2_development=# select trn, preferred_name, first_name, last_name, search from teachers where teachers.search @@ websearch_to_tsquery('Charita');
┌─────────┬───────────────────────────────────────┬─────────────────────────┬───────────┬──────────────────────────────────────────────────────────────────────────────────┐
│   trn   │            preferred_name             │       first_name        │ last_name │                                      search                                      │
├─────────┼───────────────────────────────────────┼─────────────────────────┼───────────┼──────────────────────────────────────────────────────────────────────────────────┤
│ 1000000 │ Charita Konopelski Esq. Walter Feeney │ Charita Konopelski Esq. │ Feeney    │ 'charita':1 'esq':5 'esq.feeneycharita':3 'feeney':7 'konopelski':2,4 'walter':6 │
│ 1103501 │ Charita Legros Crona Jerde            │ Charita Legros          │ Jerde     │ 'charita':1 'crona':4 'jerd':5 'legro':3 'legrosjerdecharita':2                  │
│ 1104131 │ Charita Collier Thompson Mueller      │ Charita Collier         │ Mueller   │ 'charita':1 'collier':3 'colliermuellercharita':2 'mueller':5 'thompson':4       │
└─────────┴───────────────────────────────────────┴─────────────────────────┴───────────┴──────────────────────────────────────────────────────────────────────────────────┘

Or in Rails

ecf2(dev)> Teacher.search('Charita')
  Teacher Load (0.7ms)  SELECT "teachers"."id", "teachers"."preferred_name", "teachers"."created_at", "teachers"."updated_at", "teachers"."trn", "teachers"."first_name", "teachers"."last_name" FROM "teachers" WHERE (teachers.search @@ websearch_to_tsquery($1)) /* loading for pp */ LIMIT $2  [[nil, "Charita"], ["LIMIT", 11]]
=>
[#<Teacher:0x00007f32b00a8068
  id: 1,
  preferred_name: "Charita Konopelski Esq. Walter Feeney",
  created_at: "2024-09-27 15:38:07.436408000 +0000",
  updated_at: "2024-09-27 15:38:07.436408000 +0000",
  trn: "1000000",
  first_name: "Charita Konopelski Esq.",
  last_name: "Feeney">,
 #<Teacher:0x00007f32b03be608
  id: 3512,
  preferred_name: "Charita Legros Crona Jerde",
  created_at: "2024-09-27 16:10:01.370485000 +0000",
  updated_at: "2024-09-27 16:10:01.370485000 +0000",
  trn: "1103501",
  first_name: "Charita Legros",
  last_name: "Jerde">,
 #<Teacher:0x00007f32b03bdfc8
  id: 4142,
  preferred_name: "Charita Collier Thompson Mueller",
  created_at: "2024-09-27 16:10:06.374944000 +0000",
  updated_at: "2024-09-27 16:10:06.374944000 +0000",
  trn: "1104131",
  first_name: "Charita Collier",
  last_name: "Mueller">]
ecf2(dev)>

And under the hood it's all indexed and fast.

ecf2_development=# explain select trn, preferred_name, first_name, last_name, search from teachers where teachers.search @@ websearch_to_tsquery('Charita');
┌────────────────────────────────────────────────────────────────────────────────────────┐
│                                       QUERY PLAN                                       │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on teachers  (cost=13.09..24.53 rows=3 width=153)                     │
│   Recheck Cond: (search @@ websearch_to_tsquery('Charita'::text))                      │
│   ->  Bitmap Index Scan on index_teachers_on_search  (cost=0.00..13.09 rows=3 width=0) │
│         Index Cond: (search @@ websearch_to_tsquery('Charita'::text))                  │
└────────────────────────────────────────────────────────────────────────────────────────┘