Hi,
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?
select a.UnitID,
a.TowerID,
a.Code,
c.PeopleID as Owner_PeopleID,
c.Name as OwnerName,
d.PeopleID as Tenant_PeopleID,
d.TenantName
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)
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.
Your table structure looks a little off - why do the owners and tenants
table contain a PersonID field? Can’t you just use the OwnerID itself
as a key into the people table?
Why is the tenant linked to the
owner, and not to the unit? Surely a single tenant could rent multiple
units from different owners? This doesn’t seem possible in your
current layout. nor does it seem possible for a tenant to rent only
some of the units owned by a particular owner.
Sorry I don’t have any comment on the ActiveRecord side of things,
but perhaps if you change these relationships the corresponding query
will become simpler.
Hi,
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?
select a.UnitID,
a.TowerID,
a.Code,
c.PeopleID as Owner_PeopleID,
c.Name as OwnerName,
d.PeopleID as Tenant_PeopleID,
d.TenantName
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)
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.
Ryan
I created a view in the DB, and wired that up to an AR class of its own.
For complex stuff, I like letting the DB do the heavy lifting. The Rails
app uses the view for index and show methods, and I drop back to the
true underlying models for adding/updating/delete.
Alternatively, you should check out find_by_sql, which I believe will
let you pass arbitrary SQL to marshall your data...
@units = Unit.find_by_sql('your big nasty statement here')
Hi,
Thanks for your replies. I have been contemplating about using
database views for complex queries - like letting the database do the
"heavy lifting" then use AR for CRUD operations. I used find_by_sql in
the same query to generate a tabular data to be shown on the web page.
But I think I will still need to properly setup AR Associations so it
can be used to update related tables. I still have a lot more to learn
in the RoR World.
The tenant is linked to the owner because the Owner is the one who
bought the Unit. He can use the Unit he bought or have it rented by
the Tenants. OwnerID in the owners table is autoincrement primary key.
Owners and Tenants points to PeopleID to get the names of the person.
