Fetch all entries exept those with empty column in Rails

Question

How do I fetch all 'link' entries exept those with an 'url' column that is blank? My controller looks like this:

  def show
    @user = User.find_by_username(params[:username])
    @links = @user.links.all

    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @links }

    end
  end

Thanks in advance!

Edit #1

If I do:

@links = @user.all(:conditions => 'url != NULL')

I get a error:

NoMethodError in UsersController#show 
undefined method `all' for #<User:0x2254f98>

And if I do:

@links = @user.links.all(:conditions => 'url != NULL')

I still get all the links, even those with empty url fields...

Edit #2

If I do

@links = User.all(:conditions => 'url != NULL')

I get

ActiveRecord::StatementInvalid in UsersController#show
SQLite3::SQLException: no such column: url: SELECT * FROM "users" WHERE (url != NULL) 

And if I do

@links = Link.all(:conditions => 'url != NULL')

I still get all the links, even those with empty url fields...

I'm wondering if it's a difference between NULL and the field beeing empty?

Edit #3

Now my code looks like this:

  def show
    @user = User.find_by_username(params[:username])
    @links = @user.links.all(:conditions => "url <> ''")

    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @links }

    end
  end

Solution

#controller
@links = @user.links.not_null_url

#model
named_scope :not_null_url, :conditions => "url <> ''"

This works! Just be sure access the links with:

#view
<% @links.each do |link| %>

And not:

#view
<% @user.links.each do |link| %>

Answer 1

General Advice: If your ORM is forcing you to jump through confusing hoops just to execute a simple WHERE clause in SQL, try just using SQL instead.

Somewhere in Rails, there's a simple way to hand it ("select * from users where username like '@username' and url not null", username) as a parameterized query and have it hand you a recordset.

It's trivial in the real world, but your framework is making it hard. Step outside for a second...

Answer 2

@user.all(:conditions => 'url != NULL')

Answer 3

its not @user, its

User.all(:conditions => 'url != NULL')

EDIT:

Actually i have a doubt. Is "links" a column ins users table or is it a model?

EDIT #2

Thy this: @user.links.all(:conditions => {:url => !nil})

EDIT #3

Found it! url IS NOT NULL or URL != NULL or anything using NULL won't solve your problem because, as you said, there's a difference between a null and an empty field. The code below worked for me:

@user.links.all(:conditions => "url <> ''")

And as @klew suggested, it would be nicer if you use a named_scope:

#model
named_scope :not_null_url, :conditions => "url <> ''"

#controller
@user.links.not_null_url

Answer 4

Try:

@links = @user.links.all(:conditions => "url IS NOT NULL")

It would be nicer if you will add it as named_scope in Link model:

named_scope :not_null_url, :conditions => "url IS NOT NULL"

And then in controller:

@links = @user.links.not_null_url