如何用Ransack检查DateTime字段是否为空？遇SQL报错求助

Let's break down what's going on here and fix that PostgreSQL error you're seeing.

The Root Cause #

PostgreSQL takes timestamp types seriously — an empty string '' isn't a valid value for a timestamp column, so when your query tries to compare ends_at != '', PostgreSQL throws that invalid syntax error. The problem is that Rails is translating your ends_at_blank parameter into a string comparison, which doesn't work with datetime fields in PG.

Instead of checking against an empty string, we need to directly check if the column is NULL (since that's how empty datetime fields are stored in the database).

The Fix #

We'll adjust the query logic in your controller to explicitly handle the NULL check, rather than letting Rails auto-generate the wrong SQL.

Step 1: Update Your Controller Query

Replace your existing Activity query code with something like this:

# Start with your base query
@activities = Activity.joins(:user)
                      .where("users.user_type ILIKE ?", "%WPD - SURV/MGR%")

# Handle the ends_at_blank filter
if params[:activity] && params[:activity][:ends_at_blank].present?
  # Note: Form submissions send 'true'/'false' as strings, not booleans
  show_blank_ends_at = params[:activity][:ends_at_blank] == 'true'
  
  if show_blank_ends_at
    # Filter for records where ends_at is NULL
    @activities = @activities.where(ends_at: nil)
  else
    # Filter for records where ends_at is NOT NULL
    @activities = @activities.where.not(ends_at: nil)
  end
end

# Add any pagination or final scopes here
@activities = @activities.order(created_at: :desc).page(params[:page])

Step 2: Keep Your View Form As-Is

Your existing select tag doesn't need changes — it's correctly sending true, false, or an empty value (for "All"):

<%= f.select :ends_at_blank, [true, false], {:include_blank => "All"}, class: "show-tick" %>

Optional: Add a Model Scope for Cleanup

To keep your controller code cleaner, you can add a scope to your Activity model:

# app/models/activity.rb
scope :with_ends_at_blank, ->(blank) { blank ? where(ends_at: nil) : where.not(ends_at: nil) }

Then your controller logic simplifies to:

if params[:activity] && params[:activity][:ends_at_blank].present?
  @activities = @activities.with_ends_at_blank(params[:activity][:ends_at_blank] == 'true')
end

Why This Works #

By using where(ends_at: nil) and where.not(ends_at: nil), we're generating the correct PostgreSQL syntax that checks for NULL values, rather than trying to compare against an empty string. This aligns with how PostgreSQL handles datetime fields and avoids the invalid syntax error.

内容的提问来源于stack exchange，提问作者Will