Rails Custom Model Functions

Question

I'm in a databases course and the instructor wants us to develop an e-commerce app. She said we can use any framework we like, and now that we're halfway through the semester she decided that Rails does too much and wants me to explicitly write my SQL queries.

So, what I'd like to do is to write my own functions and add them to the models to essentially duplicate already existing functionality (but with SQL that I wrote myself).

So the questions then become:

1. How do I execute manually created queries inside the model?
2. How do I stuff the results into an empty object that I can then return and work with inside the view?

Also, I'm aware of what terrible practice this is, I just don't want to start all over in PHP at this point.

Answer 1

Lets say you have a purchase

class Purchase < ActiveRecord:Base
    def Purchase.find(id)
        Purchase.find_by_sql(["Select * from purchases where id=?", id])
    end  
end

Maybe you want the products for a particular purchase. You can manually define the purchased_items in your Purchase model.

class Purchase < ActiveRecord:Base
    def purchased_items
        PurchasedItem.find_by_sql(["Select * from purchased_items where purchase_id=?",self.id])
    end
end

So for example, in your controller where you now want to get the purchased items for a particular purchase you can now do this

@purchase = Purchase.find(params[:id])
@purchased_items = @purchase.purchased_items

If you need a more raw connection to the database, you can look into ActiveRecord:Base.connection.execute(sql)

Answer 2

I think, you should know 2-3 really necessary methods, to use it. (assume we have at least 2 models, Order and User(customer for order)) For example, just to run query on your database use this:

Order.connection.execute("DELETE FROM orders WHERE id = '2')

to get number of objects from your database, the best way is use method "count_by_sql", it's scalable. I'm using it in my projects, where table has over 500 thousands records. All work to count application gives to database, and it did it much more efficient than app.

Order.count_by_sql("SELECT COUNT(DISTINCT o.user_id) FROM orders o")

this query gets number of all uniq users who has an order. we can "JOIN ON" tables, order results using "ORDER BY" and group results.

and the most often use method: find_by_sql

Order.find_by_sql("SELECT * FROM orders")

it returns to you an array with ruby objects.