How can I filter data on a rails index page using columns from a linked table?

Question

I am looking for help with adding a search filter to an existing index page that normally shows all families in my geneaology database.

The family table holds zero or more father_ids and zero or more mother_ids that point to the Persons table which describes a person.

Here is the relevant code in the families controller (simplified until I get it to work).

  def index
    if params[:search]
      @families = Family.where("Person.find('fam_fatherid').first_name like 'bob%'") 
    else
      @families = Family.all.order(params[:sort])
    end

This results in a syntax error. I have also tried several other ways to access the data, but to no avail. Example : @families = Family.where ("fam_fatherid.firstname like 'bob%'"). This doesn't work because fam_fatherid is an integer and not an object (I assume). The error I get is: SQLite3::SQLException: no such column: fam_fatherid.first_name

FWIW, here is the schema for the people table, just to verify that first_name is a valid column.

CREATE TABLE "people" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name_prefix" varchar, "first_name" varchar, "middle_name" varchar, "last_name" varchar, "name_suffix" varchar, "date_of_birth" varchar, "date_of_death" varchar, "gender" varchar, "notes" varchar, "sync_outlook" varchar, "sync_phone" varchar, "flags" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "place_of_birth" varchar, "aliases" varchar);

This is the model:

class Family < ApplicationRecord
    has_many :childlinks, foreign_key: "child_family"
    has_many :people, through: :childlinks

    # attempting to link parents
    has_many :people, foreign_key: "fam_fatherid"
    has_many :people, foreign_key: "fam_motherid"
end

If someone can just point me in the right direction, I would appreciate it. I just started with Rails a few months ago, and I don't mind doing the research, but so far, I have spent many hours and just seem to be going in circles. I've been able to add a search to the people index page because the first_name (etc.) is a column in the Persons table and not a link to another table. Thanks in advance, and I hope I've provided enough information. Jere

Answer 1

Just join with related table and use where to search

def index
  if params[:search].present?
    @families = Family.joins(:people).where("people.first_name LIKE ?", params[:search])
  else
    @families = Family.order(params[:sort])
  end
end

There is useful gem ransack. Using it you can quite easily create more powerful search fields