Rails - Jul 2008 - confused with Active Record Associations

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Smith
<paul-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org> wrote:
> 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:45 PM,
ryanbayona-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org <
> ryanbayona-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>> >>
>>
>
>
> --
> Paul Smith
> DCI Level 2 Judge, Bath FNM Organiser
>
> Upcoming events in Bath - nomadicfun.co.uk
> July 12th Eventide Prerelease (Standard)
>
> paul-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org



-- 
Paul Smith
DCI Level 2 Judge, Bath FNM Organiser

Upcoming events in Bath - nomadicfun.co.uk
July 12th Eventide Prerelease (Standard)

paul-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

ryanbayona-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
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'')
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On Jul 10, 9:00 pm, "Paul Smith"
<paulsmithena...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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 Smith
<p...-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org> wrote:
> > 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:45 PM,
ryanbay...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org <
> > ryanbay...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> > --
> > Paul Smith
> > DCI Level 2 Judge, Bath FNM Organiser
>
> > Upcoming events in Bath - nomadicfun.co.uk
> > July 12th Eventide Prerelease (Standard)
>
> > p...-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org
>
> --
> Paul Smith
> DCI Level 2 Judge, Bath FNM Organiser
>
> Upcoming events in Bath - nomadicfun.co.uk
> July 12th Eventide Prerelease (Standard)
>
> p...-qDjcQuOzTFkaZ3IpCQCdd7VCufUGDwFn@public.gmane.org
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---