# list using a sql statement **URL:** \ **Category:** rubyonrails-talk\ **Created:** [June 29, 2010, 2:19am UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637 "2010-06-29T02:19:15Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [June 29, 2010, 2:19am UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/1 "2010-06-29T02:19:15Z")
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. ---
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [June 29, 2010, 2:21am UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/2 "2010-06-29T02:21:06Z")
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 ---
**Author:** ![Colin\_Law1](https://avatars.discourse-cdn.com/v4/letter/c/91b2a8/32.png) [@Colin\_Law1](https://discuss.rubyonrails.org/u/Colin_Law1)\ **Post date:** [June 29, 2010, 8:34am UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/3 "2010-06-29T08:34:15Z")
> 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 ---
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [July 1, 2010, 3:40pm UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/4 "2010-07-01T15:40:22Z")
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 ---
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [July 1, 2010, 3:48pm UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/5 "2010-07-01T15:48:39Z")
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: ---
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [July 1, 2010, 3:49pm UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/6 "2010-07-01T15:49:25Z")
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, ---
**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\ **Post date:** [July 1, 2010, 11:10pm UTC](https://discuss.rubyonrails.org/t/list-using-a-sql-statement/52637/7 "2010-07-01T23:10:22Z")
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: