query for associated fields (many to many realtion) with arel

Question

I have a many to many relation like this :

class User < ApplicationRecord
    has_and_belongs_to_many :letter_trackings
end

and

class LetterTracking < ApplicationRecord
    has_and_belongs_to_many :assignees, class_name: "User"
end

I want to use arel to search in assignees like this :

users = User.arel_table
letter_trackings = LetterTracking.arel_table

@users = User.where(users[:name].matches("%#{Afsane Fadaei}%"))
@assignees_ids = @users.pluck(:id).uniq
letters_query = letter_trackings[:assignees].in(@assignees_ids)
@letter_trackings = LetterTracking.where(letters_query)

but it returns this error:

ActionView::Template::Error (SQLite3::SQLException: no such column: letter_trackings.assignees: SELECT "letter_trackings".* FROM "letter_trackings" WHERE "letter_trackings"."assignees" IN (1189)):

That's because I don't know how to query associated (many to many) column in arel.

Any Ideas?

Answer 1

You need to grab join table used to create HABTM association and search assignee_id or user_id for querying assignees in letter tracking. You can take following snippet for reference. I assumed that join table was LetterTrackingUsers.

letter_trackings = LetterTracking.arel_table
letter_trackings_users = LetterTrackingUsers.arel_table # here use HABTM table used by you for joining users and letter_trackings

query = letter_trackings_users[:user_id].in(@assignees_ids)  
or 
query = letter_trackings_users[:assignee_id].in(@assignees_ids)

@letter_trackings = LetterTracking.where(letters_query)