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)
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.
Ryan
[email protected] wrote:
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)
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.
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’)
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.
On Thu, Jul 10, 2008 at 1:53 PM, Paul S. [email protected]
wrote:
perhaps if you change these relationships the corresponding query will
left join owners b on a.UnitID = b.UnitID
(“*” = primary key, “+” = foreign key)
Upcoming events in Bath - nomadicfun.co.uk
July 12th Eventide Prerelease (Standard)
[email protected]
–
Paul S.
DCI Level 2 Judge, Bath FNM Organiser
Upcoming events in Bath - nomadicfun.co.uk
July 12th Eventide Prerelease (Standard)
[email protected]
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.