CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
WoodlandDev
WoodlandDev

Reputation: 11

Get record if search term is in one of its related tables related tables in Active Record

Apologies of the title is convoluted but thats basically what I want to do. I have a Table, Building, that is Related to a Table Named Residents, and those Residents can have many Photos and other media.

So If I enter in a term like "picture of Bill's Birthday" And there is a photo with a field that has this string, it should get the building that is related to this photo: photo(term found)->Resident(related to photo)-> Building(related to Resident)

Here is the query I tried :

 Building.joins(:residents).joins(:photos).where("Photographs.notes::varchar ILIKE :search",:search => "Bills_Birthday").ids

I know I have a photo with this title, and that its related to a resident, and that resident is related to a Building, but for some reason its only returning an empty array like it cant find it

Upvotes: 1

Views: 52

Answers (1)

engineersmnky
engineersmnky

Reputation: 29588

Provided your relationships are set up correctly.

search_string = "Bill's Birthday"
Building
  .joins(residents: :photos)
  .where(Photos.arel_table[:notes].matches("%#{search_string}%"))

This will return all the Building objects where the photos.notes field contains the provided search_string

The SQL generated would be akin to

SELECT 
  buildings.*
FROM 
  buildings
  INNER JOIN residents ON residents.building_id = buildings.id 
  INNER JOIN photos ON photos.resident_id = residents.id 
WHERE 
 photos.notes ILIKE '%Bill''s Birthday%'

Sidenote In your post it states: "If I enter in a term like "picture of Bill's Birthday"" and then you tried to search for "Bills_Birthday", using ILIKE.

Ignoring the difference in the 2 statements. Your search term is limited to case insensitive "Bills[0 or 1 character]Birthday", this will not allow for any preceding or trailing characters, nor the apostrophe in the word "Bill's". To allow for the presence or absence of both you could go with "%Bill_s_Birthday%"

Upvotes: 1

Related Questions