CODEWITHSUNDEEP
sqlruby-on-railsrubyarrayspostgresql
Mike Fogg
Mike Fogg

Reputation: 589

Joining two separate queries in a postgresql ...query... (possible or not possible)

I got some great help over at Returning the first X records in a postgresql query with a unique field with an issue that I'm having and trying to optimize as much as I can for now (though I'm usually not a big fan of front-loading the optimization, this is kind of a unique situation).

Imagine three entities in an app:

User
Post
Instance # An instance is just a reference to a post

The fields look something like this:

User
  id
Post
  id
  user_id
  name
Instance
  id
  user_id
  post_id
  helped_by_user_id

Requirements:

Return 10 instances where:

  1. user_id does not equal 3
  2. post_id is unique
  3. there is no other instance with that post_id and the helped_by_user_id of 3

EDIT:

I've created an SQLFiddle for this at http://sqlfiddle.com/#!10/7a324/1/0.

For the record, I'm using Ruby 1.9.3, Rails 3.2.13, and Postgresql (Heroku)

Upvotes: 2

Views: 103

Answers (2)

Mike Fogg
Mike Fogg

Reputation: 589

Just so it's easier to read, with 100% of the credit going to @CraigRinger, the final solution that worked great for me was:

SELECT DISTINCT ON (post_id) *
FROM instances i
WHERE i.user_id <> 3
AND i.helped_by_user_id IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM instances ii
  WHERE ii.post_id = i.post_id
  AND ii.helped_by_user_id = 3
)
ORDER BY i.post_id
LIMIT 10;

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324821

Right, so you want posts written by somebody other than $user where no "instance" (appears to mean "reply") for $user appears. From this set you wish to select ten distinct posts.

If that's an accurate framing of the problem, this does what you want:

SELECT *
FROM posts p
WHERE p.user_id <> 3
AND NOT EXISTS (
  SELECT 1
  FROM instances i
  WHERE i.post_id = p.id
  AND i.helped_by_user_id = 3
)
ORDER BY p.id
LIMIT 10;

Overall, it feels like the issue here is that the problem isn't really framed clearly. Usually, if you can say in clear English what you want from a data set, it's then quite easy to translate it into useful SQL.

Upvotes: 3

Related Questions