converting a conditional MySQL query syntax to Postgres in my Rails application

Question

I've been trying to convert a mySQL query in my Rails application to use Postgres Syntax and have been pouring over the Postgres documentation, specifically looking at the differences between conditionals. The query is:

Story.select('*', 'IF (views.id is null, 0, 1) as viewed')
  .join('left join views on(
       views.story_id = stories.id and views.user_id = ?
     )', user.id)
  .group(:id)
  .order(viewed: :asc)
  .order(id: :asc)

or as a pure query:

select stories.*,
  IF (views.id is null, 0, 1) as viewed
from stories
left join views
  on(views.story_id = stories.id and views.user_id = 1)
where 1
group by stories.id
order by viewed, stories.id

It seems postgres uses 'case' instead of IF, so I tired something like this:

Story.select('*', '(case when viewid is null then 0, 1 end) as viewed')
  .join('left join views on(
       views.story_id = stories.id and view.user_id = ?
     )', user.id)
  .group(:id)
  .order(viewed: :asc)
  .order(id: :asc)

but I get a

wrong number of arguments (2 for 0..1)

error...

I've tried several variations but with no luck. I'm planning to study SQL a bit more in the near future but in the mean time, I feel somewhat lost here.

Can anyone point out the finer details here of what the different syntax might be? Thanks!

Answer 1

EXISTS(...) yields a boolean, which solves the NULL case quite elegantly:

select s.*
  , EXISTS (select 42 from views v
            where v.story_id = s.id
            and v.user_id = 1) as viewed
from stories s
where 1=1
  ;

If you want to use the viewed column in an ORDER BY clause, you'll have to push the above query into a subquery.

Answer 2

Based on the error:

wrong number of arguments (2 for 0..1)

Rails (no postgres) is telling you that some method is receiving two arguments but the method only supports or none arguments or only one argument.

Looking into the code, the only two methods where you are sending two arguments are select and join so what if you try:

select('*, (case when viewid is null then 0, 1 end) as viewed') instead of select('*', '(case when viewid is null then 0, 1 end) as viewed')

and (just for test because is a bad practice)

  .join("left join views on(
       views.story_id = stories.id and view.user_id = #{user.id}
     )")

instead of

  .join('left join views on(
       views.story_id = stories.id and view.user_id = ?
     )', user.id)

?

Answer 3

The closest conversion to postgres syntax using a single query with conditional:

   select stories.*,
        CASE WHEN views.id is null THEN 0 ELSE 1 END as viewed
      from stories
      left join views
        on(views.story_id = stories.id and views.user_id = 1)
      where true
      group by stories.id, views.id
      order by viewed, stories.id