# Confused with Active Record Associations **URL:** \ **Category:** Rails\ **Created:** [July 10, 2008, 2:45pm UTC](https://www.ruby-forum.com/t/confused-with-active-record-associations/142704 "2008-07-10T14:45:43Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [July 10, 2008, 2:45pm UTC](https://www.ruby-forum.com/t/confused-with-active-record-associations/142704/1 "2008-07-10T14:45:43Z")
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 ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [July 10, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/confused-with-active-record-associations/142704/2 "2008-07-10T18:15:04Z")
removed\_email\_address@domain.invalid 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’) ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [July 11, 2008, 4:02pm UTC](https://www.ruby-forum.com/t/confused-with-active-record-associations/142704/3 "2008-07-11T16:02:11Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](http://nomadicfun.co.uk) > July 12th Eventide Prerelease (Standard) > > removed\_email\_address@domain.invalid – Paul S. DCI Level 2 Judge, Bath FNM Organiser Upcoming events in Bath - [nomadicfun.co.uk](http://nomadicfun.co.uk) July 12th Eventide Prerelease (Standard) removed\_email\_address@domain.invalid ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [July 11, 2008, 5:14pm UTC](https://www.ruby-forum.com/t/confused-with-active-record-associations/142704/4 "2008-07-11T17:14:49Z")
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.