CODEWITHSUNDEEP
sqlruby-on-railspostgresql
BrainLikeADullPencil
BrainLikeADullPencil

Reputation: 11653

How do I include a time condition in this sql query

I have a Rails app that uses the custom sql string (with a postgres database) shown below. That string works fine. I'm trying to add a time condition into the query so that I only retrieve records from answer_votes, best_answers and contributions that were created within the last 7 days. Therefore, I got today's date and subtracted 7 days

date = Date.today - 7

and substituted the statements in the sql string below for something like this

(select Coalesce(sum(value),0) from answer_votes  where (answer_votes.user_id = users.id) AND (answer_votes.created_at <= #{date}))

However, it's giving me several errors

PG::Error: ERROR: operator does not exist: timestamp without time zone <= integer

Can you show me the correct way to do this. Note, although I only showed my attempt for answer_votes, I'll be trying to do the same for best_answers and contributions, however, the solution will be the same (I assume).

sql_string = "with cte_scoring as (
  select
    users.id, users.name, users.email,
    (select Coalesce(sum(value),0) from answer_votes  where answer_votes.user_id = users.id) +
    (select Coalesce(sum(value),0) from best_answers  where best_answers.user_id = users.id) +
    (select Coalesce(sum(value),0) from contributions where contributions.user_id = users.id) total_score
  from
    users join
    contacts on (contacts.user_id = users.id)
  where
    users.lawyer         = 'true')   

select   id,
         name,
         email,
         total_score
from     cte_scoring
order by total_score desc
limit    2 "

Upvotes: 0

Views: 292

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use the built-in Postgres functions for this purpose:

where . . . created_at <= Current_Date - interval '7 day'

Something is probably going wrong with the variable substitution. You probably need single quotes around the date variable. But, doing this inside the database is simpler.

Upvotes: 1

Related Questions