I am currently learning RoR by creating a small hobby project of my
own. After designing the database I immediately ran into problem.
How can I express the following sql query in Active Record?
c.PeopleID as Owner_PeopleID,
c.Name as OwnerName,
d.PeopleID as Tenant_PeopleID,
from units a
left join owners b on a.UnitID = b.UnitID
left join people c on b.PeopleID = c.PeopleID
left join (
SELECT xx.OwnerID as OwnerID, xx.PeopleID, aa.Name as
TenantName FROM `tenants` xx
left join people aa on aa.PeopleID = xx.PeopleID
) as d on b.OwnerID = d.OwnerID
where a.TowerID = 1
order by a.TowerID asc, cast(a.Code as SIGNED ) asc , a.Code
Here are some details of the tables used in the above query:
("*" = primary key, "+" = foreign key)
table name: units , fields : * UnitID, TowerID, UnitName
table name: owners , fields : * OwnerID, +UnitID, +PersonID
table name: tenants , fields : * TenantID, +OwnerID, +PersonID
table name : people, fields : * PersonID, PersonName
Units (rooms in a condominium) can have one or more Owners.
Optionally, the Owner of the Unit may have their rooms rented by
Tenants (leased units). To get the names of the Owners and Tenants,
they point to the table People.
Thank you for your help.