ActiveRecord joins condition satisfy all relations

Question

I have a database model like this:

Post
    has_many :votes
    belongs_to :user
User
    has_many :posts
    has_many :votes
Vote
    belongs_to :post
    belongs_to :user

What I want is query all posts for a specific user which he hasn't voted for already.

I tried it like this:

Post.left_outer_joins(:votes).where.not(votes: { user_id: 1 })

where 1 is just an example user id.

The problem is that this query seems to fetch all posts which have at least one vote where the user_id is not 1.
But because more than one user will vote for these posts, once there are more than one vote, all users will receive all Posts right now.

I don't know if joins is the right approach but in English my Query would be:

Give me all Posts where none of the votes have a user_id of 1

Is it possible to fetch just the posts for a user which he hasn't voted for already?

EDIT:

My database schema of the above three tables:

Votes:

CREATE TABLE "votes" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        "post_id" integer,
        "user_id" integer,
        "created_at" datetime NOT NULL,
        "updated_at" datetime NOT NULL,
        "vote_type" integer DEFAULT 0);
CREATE INDEX "index_votes_on_post_id" ON "votes" ("post_id");
CREATE INDEX "index_votes_on_user_id" ON "votes" ("user_id");

Posts:

CREATE TABLE "posts" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        "photo_gcs_key" varchar, "photo_gcs_bucket" varchar,
        "user_id" integer, "campaign_id" integer,
        "created_at" datetime NOT NULL,
        "updated_at" datetime NOT NULL);
CREATE INDEX "index_images_on_user_id" ON "images" ("user_id");
CREATE INDEX "index_images_on_campaign_id" ON "images" ("campaign_id");

Users:

CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        "uid" varchar, "display_name" varchar, "email" varchar,
        "photo_url" varchar, "photo_gcs_key" varchar,
        "photo_gcs_bucket" varchar, "user_name" varchar,
        "created_at" datetime NOT NULL,
        "updated_at" datetime NOT NULL);

Answer 1

So if you don't care about ignoring the posts that are created by the user, it should be something like this

Find out which posts the user voted first, then the not voted ones.

Post.where.not(id: Vote.where(user_id: user_id).select(:post_id))

Answer 2

I set up an example like what you described. I created one user, id '1', and five posts, ids '1' - '5'. Then I created likes for posts '2' and '4'. The syntax in the other answer did not work, it complained about the not. The following works to select the posts (1, 3, 5) that have not been liked by user '1'

irb(main):062:0> Post.where(Sequel.~(id: Vote.select(:post_id).where(user_id: 1)))
=> #<Sequel::MySQL::Dataset: "SELECT * FROM `posts` WHERE (`id` NOT IN (SELECT `post_id` FROM `votes` WHERE (`user_id` = 1)))">

irb(main):063:0> Post.where(Sequel.~(id: Vote.select(:post_id).where(user_id: 1))).all
=> [#<Post @values={:id=>1, :photo_gcs_key=>nil, :photo_gcs_bucket=>nil, :user_id=>1, :campaign_id=>nil, :created_at=>2016-08-12 17:23:53 -0700, :updated_at=>2016-08-12 17:23:53 -0700}>, #<Post @values={:id=>3, :photo_gcs_key=>nil, :photo_gcs_bucket=>nil, :user_id=>1, :campaign_id=>nil, :created_at=>2016-08-12 17:23:56 -0700, :updated_at=>2016-08-12 17:23:56 -0700}>, #<Post @values={:id=>5, :photo_gcs_key=>nil, :photo_gcs_bucket=>nil, :user_id=>1, :campaign_id=>nil, :created_at=>2016-08-12 17:23:57 -0700, :updated_at=>2016-08-12 17:23:57 -0700}>]