Rails - May 2006 - find by sql

Hi

I have the following code, which makes use of an sql statment to get the 
results required.

However this just doesnt seem right for rails, as ive managed to develop 
the whole  project without using any other pure sql.

Is there a cleaner way of doing this, but gaining the same results.

thanks
scott

    #sql to find all invoices
    pSql = "SELECT DISTINCT invoices.id, invoices.invoice_number, 
invoices.approve_date, invoices.sent_date, invoices.paid_date
            FROM projects, payments, invoices
            WHERE projects.id = payments.project_id
            AND payments.invoice_id = invoices.id
            AND projects.id = " + params[:id] + "
            ORDER BY invoices.id"

    @invoices = Invoice.find_by_sql(pSql);

-- 
Posted via http://www.ruby-forum.com/.

> Hi
>     #sql to find all invoices
>     pSql = "SELECT DISTINCT invoices.id, 
> invoices.invoice_number, invoices.approve_date, 
> invoices.sent_date, invoices.paid_date
>             FROM projects, payments, invoices
>             WHERE projects.id = payments.project_id
>             AND payments.invoice_id = invoices.id
>             AND projects.id = " + params[:id] + "
>             ORDER BY invoices.id"
> 
>     @invoices = Invoice.find_by_sql(pSql);

Try @invoices = Invoice.find(:all, :select => "Distinct  invoices.id,
invoices.invoice_number, invoices.approve_date,invoices.sent_date,
invoices.paid_date",
				       : join => " ,projects, payments",

				       :Conditions =>["projects.id payments.project_id AND
payments.invoice_id = invoices.id AND
projects.id = ?",params[:id]])

Although I have not tried it, it should work. If not take a look at the
log file to see what is the sql being generated and customize the above
statement accordingly. 

Bharat