|
|
|
|
Include First Item from Sorted DESC Table
By: Bruce Bahlmann - Contributing Author (your
feedback
is important to us!)
In rails development, you may have an occasion where you need include within
a given query, a field from another table. Unfortunately, simply using rails
joins or includes functions may not work for you in this case particularly
if the resulting data has limited filtering capability that is either very
complicated or impossible. The following is an example where we needed to
include a column from a table that was sorted DESC by date and all we
required was a column from the first row of the resulting data.
If you attempt to solve the problem described above using either joins or
includes, Rails will return a huge list of results that need extensive
filtering. But what if you can't filter - what if the only filtering you can
do is sort the table by date DESC and select the top row? Rails has no easy
answer to this problem - so you must defer the problem to SQL. In SQL, you
would use something called a sub-query to accomplish this. The following
provides working examples of using sub-query with Rails.
[apps/controllers/timeinout_controller.rb]
@timecards = Timecard.
select("
TIME.TOUT,TIME.TIN,TIME.CEMPID,TIME.DWORKDATE,
(SELECT TOP 1 (NCARRY + NACC_HRS) FROM ACC_POST WHERE EMPLOYEE.CEMPID = ACC_POST.CEMPID
AND ACC_POST.NACC_HRS > 0 ORDER BY DDATE DESC) as PTO
).
where("TIME.CGROUP4 = '09'").
where("TIME.DWORKDATE = '#{Time.new.strftime("%Y-%m-%d")}'").
order(:TIN).all
In the above example, we selected a number of columns (TOUT, TIN, CEMPID,
etc.) and then we come to the need to show an employee's current PTO. Their
PTO can be determined from finding the most recent PTO entry from the
ACC_POST table and then sum the last known entry (NCARRY) with the most
recent addition (NACC_HRS). To achieve this result, we need to perform a sub-query
which is embedded within the rails select statement. Sub-queries are very
limited and generally only return a single column. In our case, it would
have been nice to return multiple columns, however a simple sum of two
columns allowed us to only return a single column representing a simple
summation of two columns which are returned as a made up column named PTO.
Note that in the above example we used the up to date activerecord chaining
format for our queries.
Can Birds-Eye.Net help you or your Company?
Receive your Birds-Eye.Net articles and white
papers hot off
the presses by adding our RSS feed to your reader.
|
|
|
(C) Copyright Birds-Eye.Net, All rights reserved.
It is against the law to reproduce this content or any portion of it in any form without the explicit written permission of Birds-Eye Network Services, LLC. Federal copyright law (17 USC 504) makes it illegal, punishable with fines up to $100,000 per violation plus attorney's fees.
|