CODEWITHSUNDEEP
sqlruby-on-railsactiverecordactive-record-query
kittyminky
kittyminky

Reputation: 485

Rails ActiveRecord query where relationship does not exist based on third attribute

I have an Adventure model, which is a join table between a Destination and a User (and has additional attributes such as zipcode and time_limit). I want to create a query that will return me all the Destinations where an Adventure between that Destination and the User currently trying to create an Adventure does not exist.

The way the app works when a User clicks to start a new Adventure it will create that Adventure with the user_id being that User's id and then runs a method to provide a random Destination, ex:

Adventure.create(user_id: current_user.id) (it is actually doing current_user.adventures.new ) but same thing

I have tried a few things from writing raw SQL queries to using .joins. Here are a few examples:

Destination.joins(:adventures).where.not('adventures.user_id != ?'), user.id)

Destination.joins('LEFT OUTER JOIN adventure ON destination.id = adventure.destination_id').where('adventure.user_id != ?', user.id)

Upvotes: 1

Views: 6166

Answers (5)

Beder Acosta Borges
Beder Acosta Borges

Reputation: 5368

I solved this problem with a mix of this answer and this other answer and came out with:

destination = Destination.where
                         .not(id: Adventure.where(user: user)
                                           .pluck(:destination_id)
                         )
                         .sample

The .not(id: Adventure.where(user: user).pluck(:destination_id)) part excludes destinations present in previous adventures of the user.

The .sample part will pick a random destination from the results.

Upvotes: 0

Andrew Hacking
Andrew Hacking

Reputation: 6366

ActiveRecord doesn't do join conditions but you can use your User destinations relation (eg a has_many :destinations, through: adventures) as a sub select which results in a WHERE NOT IN (SELECT...)

The query is pretty simple to express and doesn't require using sql string shenanigans, multiple queries or pulling back temporary sets of ids:

Destination.where.not(id: user.destinations)

If you want you can also chain the above realation with additional where terms, ordering and grouping clauses.

Upvotes: 0

apeniche
apeniche

Reputation: 669

No need for joins, this should do:

Destination.where(['id not in ?', user.adventures.pluck(:destination_id)])

Upvotes: 2

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

The below should return all destinations that user has not yet visited in any of his adventures:

destinations = Destination.where('id NOT IN (SELECT destination_id FROM adventures WHERE user_id = ?)', user.id)

To select a random one append one of:

.all.sample
# or
.pluck(:id).sample

Depending on whether you want a full record or just id.

Upvotes: 8

vee
vee

Reputation: 38645

In your first attempt, I see the problem to be in the usage of equality operator with where.not. In your first attempt: 

Destination.joins(:adventures).where.not('adventures.user_id != ?'), user.id)

you're doing where.not('adventures.user_id != ?'), user.id). I understand this is just the opposite of what you want, isn't it? Shouldn't you be calling it as where.not('adventures.user_id = ?', user.id), i.e. with an equals =?

I think the following query would work for the requirement:

Destination.joins(:adventures).where.not(adventures: { user_id: user.id })

The only problem I see in your second method is the usage of destinations and adventures table in both join and where conditions. The table names should be plural. The query should have been: 

Destination
.joins('LEFT OUTER JOIN adventures on destinations.id = adventures.destination_id')
.where('adventures.user_id != ?', user.id)

Upvotes: 1

Related Questions