Help with "find_by_sql" statement

Rails
1

I’m trying to write a “find_by_sql” statement that selects with a
WHERE clause comparing an attribute to an external value. My statement
generates an error on the last line:

def department
# For menu display
@departments = Department.find(:all)
@categories = Category.find_all_by_department_id(params[:id],
:order => “category_id”)

# For "@department.title" and "@department.description" display
@department = Department.find_by_department_id(params[:id])

# Select products on promotion
@products = Product.find_by_sql("SELECT DISTINCT * FROM products "

  • " INNER JOIN product_categories " +
       " ON products.product_id = product_categories.product_id

" +
" INNER JOIN categories " +
" ON product_categories.category_id =
categories.category_id " +
" WHERE categories.department_id = ? ", params[:id])
end

Help please,

Carson

2

On 6 May 2008, at 15:23, Carson wrote:

I’m trying to write a “find_by_sql” statement that selects with a
WHERE clause comparing an attribute to an external value. My statement
generates an error on the last line:

Well if you just say ‘an error’ it’s hard to guess whiich error (and
thus what problem you’re encountering). I’d start byt not using a
horrible find_by_sql.

Product.find :all, :select => ‘distinct products.*’, :joins
=> :categories, :conditions => [‘categories.department_id = ?’,
params[:id]]
does the same thing as your chunk of sql.
(assuming there’s a has many through (or a hatbm) from products to
categories (via product_categories)

Fred

3

Hey Fred:

The error message was: “wrong number of arguments (2 for 1)”.

There are four tables involved: departments, products,
product_categories and categories.
A department has_many categories.
Categories and products have a many-to-many relationship, joined by a
product_categories table containing category_id paired with
product_id.

Selection has two conditions: The product record has an
on_department_promotion attribute = ‘true’
and the category record has a
department_id attribute = prams[:id]

Your familiarity with Rails find statements is well beyond mine; so,
if you’re so inclined, I cound use help in scripting the find
statement.

Thanks,

Carson

On May 6, 9:29 am, Frederick C. [email protected]

4

On 6 May 2008, at 16:52, Carson wrote:

Hey Fred:

The error message was: “wrong number of arguments (2 for 1)”.

Ah yes, you need to say find_by_sql([some_sql, substitution_value1,
substitution_value2])

department_id attribute = prams[:id]

Your familiarity with Rails find statements is well beyond mine; so,
if you’re so inclined, I cound use help in scripting the find
statement.

I think what I gave below is most of the way there, just augment the
conditions a bit:
[‘product_on_department_promotion = ? AND categories.department_id
= ?’, true, params[:id]]

Fred

5

Don’t forget to pluralize the table name (product should be products
above).

On May 6, 12:01 pm, Frederick C. [email protected]