Find_by_sql question


#1

I’m no good at SQL and I have a question that will hopefully be fairly
easy to answer.

I’m using acts_as_paranoid which instead of deleting a record adds a
deleted_at column with the datetime the row was deleted. I want to have
a find_by_sql filter out any row where deleted_at is not null.

I have (at least the relevant parts):
#a couple working filters here.
filters << “crs.deleted_at IS NOT NULL” # THIS DOES NOT WORK
filters = ‘WHERE ’ + filters.join(’ AND ') unless filters.empty?
direction = ‘DESC’ unless %w{ASC DESC}.include? direction

find_by_sql %{SELECT crs.*,
status.status AS status_status
FROM crs
LEFT OUTER JOIN status on status.id = crs.status_id
#{filters.to_s unless filters.empty?}
ORDER BY crs.#{order_by} }
end


#2

The proper SQL syntax would be something like this:

Select * from table where deleted_at IS NULL;

In a model you might do something like:

find(:all,

:conditions => “deleted_on IS NULL”
)

Hope that helps!

On Jan 31, 2006, at 8:49 AM, Marcus V. wrote:

filters << “crs.deleted_at IS NOT NULL” # THIS DOES NOT WORK


Posted via http://www.ruby-forum.com/.


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

#3

Bill P. wrote:

The proper SQL syntax would be something like this:

Select * from table where deleted_at IS NULL;

In a model you might do something like:

find(:all,

:conditions => “deleted_on IS NULL”
)

Hope that helps!

On Jan 31, 2006, at 8:49 AM, Marcus V. wrote:

filters << “crs.deleted_at IS NOT NULL” # THIS DOES NOT WORK


Posted via http://www.ruby-forum.com/.


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill

Yeah that helped. I only had to remove one word from my statement:
filters << “crs.deleted_at IS NOT NULL”
became
filters << “crs.deleted_at IS NULL”

I wanted to filter OUT the crs that where deleted_at was null, not the
opposite. Thanks for your help, sometimes the obvious escapes me until
someone smacks me in the head with it :slight_smile:

-Marcus