How to Write Complex Active Record Query in Rails

Question

I need a Active Record Query to fetch an array. CRUDS for the models are working correctly but i need to fetch array in view file. I have following SQL syntax.

select FirstName from Person.Person
LEFT JOIN Sales.SalesPerson
ON Person.BusinessEntityID = SalesPerson.BusinessEntityID
LEFT JOIN Sales.SalesTerritory
ON SalesPerson.TerritoryID = SalesTerritory.TerritoryID
where SalesPerson.BusinessEntityID is null and SalesPerson.TerritoryID is null

What will be the Active Record Query syntax in rails? Any Idea?

Answer 1

Except for case and tables names your query should look something like this

Person.left_joins(:sales_person, :sales_territory).
    where(sales_persons: {business_entity_id: nil, territory_id: nil}).
    pluck(:first_name)

Answer 2

The accepted answer will generate a wrong query as below

select * from persons 
left outer join sales_persons on sales_persons.person_id = persons.id 
left outer join sales_territories on sales_territories.person_id = persons.id

SalesTerritory table is joined with Person but according to your SQL it should join with SalesPerson

As it is a nested join so following will generate correct SQL

Person.select(:first_name).left_joins(sales_person: :sales_territory).
where(sales_persons: {business_entity_id: nil, territory_id: nil}).
pluck(:first_name)