I''ve heard a lot about Ruby on Rails, and decided to give it a go.
I''m
not a big fan of tutorials, since they tend towards providing
simplistic or contrived examples (i.e., reading them is usually
instructive, but working through them often fails to give much
benefit), so instead I decided to attempt porting an ASP (JScript) app
to Rails.
I started to run into issues when I tried bringing in a few tables.
The application in question is for viewing entries from an internal
directory of contact phone numbers and email address. The tables I am
concerned with at present are called "names", "entities",
"positions",
"titles", and "contacts". The relationships between them
are:
entities.name_id = names.id (1:1)
contacts.entity_id = entities.id
positions.entity_id = entities.id
positions.title_id = titles.id
My main view is a list of names that match a user''s search criteria,
along with the associated positions and contact numbers. The most
obvious way of doing this involved using Entity.find_by_sql(...), then
simply getting an_entity.name.surname, an_entity.contact.number, and
an_entity.position.title.description for each entity found; however,
this would result in 4 extra queries per entity, which will be rather
inefficient database use when displaying hundreds of entities in a
page. I wanted to avoid these extra queries, but found that eager
loading would not handle the two-level join from entities to positions
to titles.
How can I get back the data from all these tables in one query, or at
most 4 queries (one for each extra table)?
My second problem came when I tried to implement paging on these
records. I''m using find_by_sql to return the entities, with ordering
by "surname asc, given_names asc", and the database is SQL Server
2000. Due to this, the default paging was not working correctly, and
after much searching, found that things only worked if I did the
following:
@entity_pages = Paginator.new nil, Entity.count, 10,
@params[''page'']
sql = <<-end
select entities.*, names.surname, names.given_names
from entities
inner join names on entities.name_id = names.id
where entities.name_id is not null
order by surname asc, given_names asc
end
connection = ActiveRecord::Base.connection
sql = connection.add_limit_offset! sql, { :limit =>
@entity_pages.items_per_page, :offset => @entity_pages.current.offset,
:order => "surname asc, given_names asc" }
@entities = Entity.find_by_sql sql
This is awkward, and moreover requires me to repeat the ordering
clause in the call to add_limit_offset!, neither of which are
desirable. Is there a better way to achieve paging with a custom SQL
query? Am I approaching the problem entirely the wrong way, or is
there something small that I''ve overlooked?
Thanks for any help you can give!
Andrew