Rails - Jan 2006 - find_by_sql question

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

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

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 Vorwaller wrote:
> 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
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


- Bill

Bill Pennington 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 Vorwaller wrote:
> 
>> filters << "crs.deleted_at IS NOT NULL" # THIS DOES NOT
WORK
>>
>> -- 
>> Posted via http://www.ruby-forum.com/.
>> _______________________________________________
>> Rails mailing list
>> Rails@lists.rubyonrails.org
>> 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 :)

-Marcus

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