ruby on rails - How to scope has_many through association that uses custom key names? -

trying learn how navigate has_many through association i'm working , i'm hitting brick wall. i've abstracted classes here they're easier relate to.

a book has many characters. character has many books. makes different typical hmt association when character associated book, added either "good guy" or "bad guy", example:

book.bad_guys << character 

i'm able pull results book.good_guys fine. what's throwing me loop whenever want go other way, take character , find out books associated with, returns book::activerecord_associations_collectionproxy rather actual result list.

if character.books.first outputs (notice how attempts find "good_guy" attribute rather go off of book , character columns part of join table)

document load (1.1ms)  select  "books".* "books" inner join "book_characters" on "books"."id" = "book_characters"."good_guy" "book_characters"."character_id" = ? order "books"."id" asc limit ?  [["character_id", 1], ["limit", 1]] activerecord::statementinvalid: sqlite3::sqlexception: no such column: book_characters.good_guy: select  "books".* "books" inner join "book_characters" on "books"."id" = "book_characters"."good_guy" "book_characters"."character_id" = ? order "books"."id" asc limit ? /home/linux/.rvm/gems/ruby-2.3.1/gems/sqlite3-1.3.11/lib/sqlite3/database.rb:91:in `initialize'     

i suspect need scope how character model carries out it's search through through table , book table, haven't had success.

is possible add scope on character model modify how searches through table? or there else need find associated records? in advance can give!

class character < applicationrecord   has_many :book_characters   has_many :books, -> { where(book_characters: ("character = :character.id")) }, :through => :book_characters # don't think wording correct here  class book < applicationrecord   has_many :book_characters   has_many :good_guys, through: :book_characters, :source => "character"   has_many :bad_guys, through: :book_characters, :source => "character"  class bookcharacter < applicationrecord   belongs_to :character   belongs_to :book, :foreign_key => :bad_guy   belongs_to :book, :foreign_key => :good_guy 

and here migration bookcharacters...

class createbookcharacters < activerecord::migration[5.0]   def change      create_table :book_characters |t|,        t.references :book, foreign_key: true       t.references :entity 

your first problem this:

class bookcharacter < applicationrecord   belongs_to :character   belongs_to :book, :foreign_key => :bad_guy   belongs_to :book, :foreign_key => :good_guy 

there 2 problems here. first, can't double-declare belongs_to :book. second, when :foreign_key => :good_guy, rails expects bookcharacter going have field called good_guy. doesn't. why get:

no such column: book_characters.good_guy 

so, need more like:

class bookcharacter < applicationrecord   belongs_to :character   belongs_to :book 

then, believe scope can like:

class character < applicationrecord   has_many :book_characters   has_many :books, -> { book.where(book_characters: book_characters) } 

i'm not 100% sure book_characters: book_characters bit operate advertised. so, try like:

class character < applicationrecord   has_many :book_characters   has_many :books, -> { book.where(id: book_characters.pluck(:book_id)) } 

having said that, doubt this:

book.bad_guys << character 

is doing think is. because, don't seem persist notion of 'good_guy' or 'bad_guy' anywhere. so, you're not keeping track of , isn't.

finally, noticed you're using sqlite. have no idea you'll deploy app. but, want think through. heroku, instance, doesn't use sqlite , deploy on heroku need convert postgresql. noodle on.