CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
bradleygh25
bradleygh25

Reputation: 21

Rails: SQLite3::SQLException: near "from": syntax error

I have an issue with a sort by year filter on my rails app; when i attempt to select a year from my view, the app crashes with this error: SQLite3::SQLException: near "from": syntax error

The code(passed down from the previous dev who has moved on) for the filter in my control is this:

    @posts = Post.where('extract(year from sort_date) = ?', params[:year])
                 .paginate(page: params[:page], per_page: 5)
                 .order('sort_date DESC')
    render 'index'
  end

i might be wrong but i am assuming it's the 'extract(year from sort_date) = ?' in the code? but i am unsure, also for reference the reference parameter is: {"year"=>"2017"}

I attempted to make it look more like a postgres query("EXTRACT(year FROM sort_date) = ?" but still it fails, with the exact same SQL error(Rails: SQLite3::SQLException: near "from")

Upvotes: 0

Views: 101

Answers (1)

Beartech
Beartech

Reputation: 6411

SQLITE3 does not support the extract() function. You have to use strftime().

@posts = Post.where("strftime('%Y', sort_date) = ?", params[:year])
                 .paginate(page: params[:page], per_page: 5)
                 .order('sort_date DESC')
    render 'index'
  end

Upvotes: 2

Related Questions