# List using a sql statement

**URL:** <https://www.ruby-forum.com/t/list-using-a-sql-statement/190841>\
**Category:** Rails\
**Created:** [June 29, 2010, 3:56am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841 "2010-06-29T03:56:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [June 29, 2010, 3:56am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/1 "2010-06-29T03:56:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [June 29, 2010, 4:19am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/2 "2010-06-29T04:19:15Z")

</div>

RailsFan R. 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.

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [June 29, 2010, 4:21am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/3 "2010-06-29T04:21:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [June 29, 2010, 10:29am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/4 "2010-06-29T10:29:16Z")

</div>

On 29 June 2010 02:56, RailsFan R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> the idea behind this is, i have this status column to show only the  
> @categories=Category.find\_all\_categories  
> end

I think it would be worth your while working through some tutorials  
and guides. Have a look at the guides at  
[http://guides.rubyonrails.org/](http://guides.rubyonrails.org/) for a start. Agile Development with  
Rails is an excellent book. The tutorial at  
[http://www.railstutorial.org/](http://www.railstutorial.org/) is good. Make sure that any tutorial  
you try is for rails 2.3.

For your particular problem I would suggest using named\_scope.

Colin

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [June 29, 2010, 10:35am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/5 "2010-06-29T10:35:43Z")

</div>

On 29 June 2010 03:19, RailsFan R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [July 1, 2010, 5:40pm UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/6 "2010-07-01T17:40:22Z")

</div>

Colin L. wrote:

> On 29 June 2010 03:19, RailsFan R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [July 1, 2010, 5:49pm UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/7 "2010-07-01T17:49:25Z")

</div>

RailsFan R. 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,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [July 2, 2010, 1:10am UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/8 "2010-07-02T01:10:22Z")

</div>

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:

> RailsFan R. 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,
> 
> Marnen Laibow-Koser  
> [http://www.marnen.org](http://www.marnen.org)  
> removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![railsfan\_radha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/41988e/32.png) [@railsfan\_radha](https://www.ruby-forum.com/u/railsfan_radha)\
**Post date:** [July 1, 2010, 5:48pm UTC](https://www.ruby-forum.com/t/list-using-a-sql-statement/190841/9 "2010-07-01T17:48:39Z")

</div>

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 R. wrote:

> Colin L. wrote:
> 
> > On 29 June 2010 03:19, RailsFan R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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