Hi all,
I have a couple of questions about Rails that I hope someone can shed
some light on.
My first question is this....
let''s say i have 3 tables
companies
id
name
desc
reports
id
company_id
source_id
status
sources
id
name
i want to render a paginated page, that lists all companies that have a
report that has status == 1
my pagination call looks like:
@company_pages, @companies = paginate(:company,
:per_page => 25,
:select => ''distinct
c.*'',
:joins => "as c inner join reports
as r on c.id = r.company_id",
:conditions => query,
:order => "c.name")
so my sql statement would look something like
select distinct c.* from companies as c inner join reports as r on c.id
= r.id where r.status == 1 order by c.name
simple enough to do and it worked fine in mysql, which is the DB i
develop on.
Unfortunately, we have to move this to SQLServer, and the problem is
that it doesn''t have a LIMIT function. so their pagination query looks
like:
-- count the number of records....
SELECT count(*) as TotalRows from (SELECT TOP 1000000000 c.* FROM
companies as c inner join reports as r on c.id = r.company_id WHERE
(r.status = 0) ORDER BY c.name ) tally
-- retrieve the records
SELECT * FROM (SELECT TOP 25 * FROM (SELECT TOP 25 c.* FROM companies
as c inner join reports as r on c.id = r.company_id WHERE (r.status = 0)
ORDER BY c.name ) AS tmp1 ORDER BY c.name DESC) AS tmp2 ORDER BY c.name
the problem here is that the inner join causes problems since there are
now 2 id fields. company.id and report.id. what needs to happen is
that the c.* should apply outside the innermost parenthesis as well to
make sure that there''s no amibguity.
anyone come across this problem and have any clues as to how to use
pagination with inner joined queries?
i guess i have two lesser urgent questions, and thought I might throw
them out there to see if anyone has any answers.
is there any way to do an :include for queries with :select defined (as
above) and is there a way to :include one off tables? meaning tables
that should be joined to my join tables? it would greatly cut down on
the number of sql queries if i could do that.
my last question is this. As i''ve been struggling with these DB
specific-syntaxes, I also came across a need to do a case-insensitive
LIKE query. of course, I''m used to doing something like:
"where UPPER(column_name) like ''KEYWORD_IN_CAPS''"
which would take care
of my case sensitivity issues but i know most databases do this upcase
differently.
Any ideas on these three questions?
Thanks in advance for any help.
s.park
--
Posted via http://www.ruby-forum.com/.