CODEWITHSUNDEEP
ruby-on-railswhere-clause
Difender
Difender

Reputation: 525

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 ?

Upvotes: 1

Views: 47

Answers (2)

Sebastián Palma
Sebastián Palma

Reputation: 33420

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)

Upvotes: 1

faul
faul

Reputation: 35

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

Upvotes: 1

Related Questions