Where with a condition

I have a table like this:

Calendar
 - date
 - description
 - userid

And I would like to get all the events of a given month of a user by it's userid.

So I know that to get all events for a userid is :

Calendar.where(userid: params[:id])

And I tried (as I saw in some post already in here)

Calendar.where(userid: params[:id]).where("date ~= ?", '%2016-12%')

So this should give me all event from the 12th month of 2016 but instead I have :

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: date ~= unknown
LINE 1: ...endars" WHERE "calendars"."userid" = $1 AND (date ~= '%2016-...
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

What did I do wrong ?

2 answers

  • answered 2017-06-17 19:26 faul

    I think your solution is here

    def index
      @events = Calendar.where(userid: params[:id]).all
      @event_months = @events.group_by { |t| t.date.month } // month is a given name
    end
    

    Please see the below link

    https://stackoverflow.com/a/18862762/8175208

    I think will help you

  • answered 2017-06-17 19:26 Sebastian Palma

    You could try "parsing" your column date to char using the to_char Postgresql function passing the column and the format you want:

    SELECT * 
    FROM calendars
    WHERE to_char(date, 'YYYY-MM-DD') LIKE '%2016-12%'
    

    So, you can use ActiveRecord#find_by_sql:

    query = "SELECT * FROM calendars WHERE to_char(date, 'YYYY-MM-DD') LIKE '%2016-12%'"
    Calendar.find_by_sql(query)