SQL problems when migrating from MySQL to PostgreSQL

Going the "pure SQL" route opened up a can of worms for me, so I tried keeping the will_paginate gem and tweak the Spot.paginate parameters instead. The :joins parameter turned out to be very helpful.

This is currently working for me:

spots = Spot.paginate(:all, :include => [:thing, {:thing => :tags}, {:thing => :brand}], :joins => [:user, :store, :thing], :group => 'thing_id,things.id,users.id,spots.id', :order => order, :conditions => conditions, :page => page, :per_page => per_page)

Question 1:

...Is there a better way?

Yes. Since PostgreSQL 9.1 the primary key of a table logically covers all columns of a table in the GROUP BY clause. I quote the release notes for version 9.1:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

Question 2:

The following statement ... produces the error

PG::Error: ERROR: missing FROM-clause entry for table "users"

How can I solve this?

First (as always!), I formatted your query to make it easier to understand. The culprit has bold emphasis:

SELECT *
FROM  (
   SELECT DISTINCT ON (spots.id)
          spots.id, spots.created_at AS alias_0
   FROM   spots  
   LEFT   JOIN things ON things.id = spots.thing_id 
   WHERE (spots.recommended_to_user_id = 1 OR
          spots.user_id IN (1) OR
          things.is_featured = 't')  
   GROUP  BY thing_id, things.id, users.id, spots.id
   ) id_list 
ORDER  BY id_list.alias_0 DESC
LIMIT  16
OFFSET 0;

It's all obvious now, right?
Well, not all of it. There is a lot more. DISTINCT ON and GROUP BY in the same query for one, which has its uses, but not here. Radically simplify to:

SELECT s.id, s.created_at AS alias_0
FROM   spots s
WHERE  s.recommended_to_user_id = 1 OR
       s.user_id = 1 OR
       EXISTS (
          SELECT 1 FROM things t
          WHERE  t.id = s.thing_id
          AND    t.is_featured = 't')
ORDER  BY s.created_at DESC
LIMIT  16;

The EXISTS semi-join avoids the later need to GROUP BY a priori. This should be much faster (besides being correct) - if my assumptions about the missing table definitions hold.