list using a sql statement

RailsFan Radha wrote:

I have a table called category and i want to show only the records where "status = A" in my list.erb

currently, my list.erb shows all records from category table. but, i would like to show only the records where status = 'A'

for example: select * frm category where status = 'A'

the idea behind this is, i have this status column to show only the active records. This status flag accepts, "A" for active, "I" for inative and "R" for "Requested newly, but not yet processed". .. and may be others as per the futurre needs.

so how can i apply this filter of where status = 'A' in my controller.

  def list       @categories=Category.find_all_categories         end

- thanks, radha

Added another method to the model,

def self.find_active_categories       find_by_sql("SELECT * from category            where status = 'A')       order by category_id ") end

And changed the controller, list action to call this new method.

   def list        @categories=Category.find_active_categories    end

And this seems to be working. Let me know if i have missed any or please add any additional info which this implies too.

- thanks, radha.

Does this mean, that always create a method and call that in the controller?

Even for find(:all) ?

Is this the best practice?

- thanks, radha

RailsFan Radha wrote:

I have a table called category and i want to show only the records where "status = A" in my list.erb

currently, my list.erb shows all records from category table. but, i would like to show only the records where status = 'A'

for example: select * frm category where status = 'A'

the idea behind this is, i have this status column to show only the active records. This status flag accepts, "A" for active, "I" for inative and "R" for "Requested newly, but not yet processed". .. and may be others as per the futurre needs.

so how can i apply this filter of where status = 'A' in my controller.

def list @categories=Category.find_all_categories end

- thanks, radha

Added another method to the model,

def self.find_active_categories find_by_sql("SELECT * from category where status = 'A') order by category_id ") end

Don't use find_by_sql unless absolutely necessary. The above can be done by using the :conditions and :order options in find. Also as I suggested previously, I would use a named scope (with default_scope for the order if you will always sort by the same thing).

And changed the controller, list action to call this new method.

def list @categories=Category.find_active_categories end

And this seems to be working. Let me know if i have missed any or please add any additional info which this implies too.

Do you always want to just show active categories on the index? If so then that concept is ok (subject to comments above).

Colin

Colin Law wrote:

� def list

�def self.find_active_categories � � �find_by_sql("SELECT * from category � � � � � where status = 'A') � � �order by category_id ") �end

Don't use find_by_sql unless absolutely necessary. The above can be done by using the :conditions and :order options in find. Also as I suggested previously, I would use a named scope (with default_scope for the order if you will always sort by the same thing).

And changed the controller, list action to call this new method.

� def list � � � @categories=Category.find_active_categories � end

And this seems to be working. Let me know if i have missed any or please add any additional info which this implies too.

Do you always want to just show active categories on the index? If so then that concept is ok (subject to comments above).

Colin

Thanks for ur response Colin. (I have earlier posted a solution using find_by_sql for this problem)

What is the bext practice in this case? (Yes, I always want to show the active records only ). Using find_by_sql or using a condition in the find :all ? ( I like SQLs, but as far as the performance goes which approach is better? )

Can someone throw light in this please?

- thanks

An update to this post:

Here, i'm trying to figure out which is the better approach or best practice to show only active categories records from the category table.

Also, i want to prevent any SQL injection.

Do u think, using find_by_sql approach could protect the page from sql injection to view all categories?

thanks, radha

RailsFan Radha wrote:

RailsFan Radha wrote: [...]

What is the bext practice in this case? (Yes, I always want to show the active records only ). Using find_by_sql or using a condition in the find :all ? ( I like SQLs, but as far as the performance goes which approach is better? )

The performance is probably equivalent. The maintainability, however, is not. find_by_sql locks your code to one DB server and makes it less maintainable. *Do not* use find_by_sql unless you have no other choice -- write your code with ActiveRecord find syntax whenever possible.

Can someone throw light in this please?

- thanks

Best,

thanks Marnen. this is exactly what i as looking for. Your answer is informative and it explains why i should avoid writing sqls in it. thanks again.

-radha.

Marnen Laibow-Koser wrote: