List using a sql statement

Rails
1

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
2

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.
3

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
4

On 29 June 2010 02:56, RailsFan R. [email protected] 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/ for a start. Agile Development with
Rails is an excellent book. The tutorial at
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

5

On 29 June 2010 03:19, RailsFan R. [email protected] 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

6

Colin L. wrote:

On 29 June 2010 03:19, RailsFan R. [email protected] 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
7

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
[email protected]

8

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
[email protected]

9

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. [email protected] 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