Rails 5.1 where query on has many model

Question

The context: I have two models:

gin.rb

class Gin < ApplicationRecord
  has_many :gins_botanical
  has_many :botanicals, through: :gins_botanical

botanical.rb

class Botanical < ApplicationRecord
  has_many :gins_botanical
  has_many :gins, through: :gins_botanical

When viewing the show page for a given botanical, I would like to display a list of gins that also contain that specific botanical.

I'm tyring to use a where clause to achieve this, but I can't get it to work.

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "botanicals" LINE 1: SELECT "gins".* FROM "gins" WHERE (botanicals.name LIKE '%an... ^ : SELECT "gins".* FROM "gins" WHERE (botanicals.name LIKE '%angelica root%')

I can see "%#{@botanical.name}%" is fine, as it shows the value of that current botanical, in this case, angelica root, but clearly I'm not pulling through the other gins that also have angelica root as a botanical.

botanicals_controller

  def show
    @similargins = Gin.where("botanicals.name LIKE ?", "%#{@botanical.name}%")
  end

botanical/show.html.erb

<% @similargins.each do |gin| %>
  <%= gin.name%>
<% end %>

Where's the `GinBotanical` model? And why do you say `has_many :gins_botanical` instead of `has_many :gin_botanicals`? — jvillian, Mar 07 '18 at 22:37
`@similargins = Gin.includes(:botanicals).references(:botanicals).where("botanicals.name LIKE ?", "%#{@botanical.name}%")` should do the trick — MrYoshiji, Mar 07 '18 at 23:22
The question is a little confusing because you say you want "a list of gins that also contain that specific botanical" but you use `LIKE`. Depending on your list of `botanicals`, `LIKE` may return gins that do not include, specifically, `@botanical`. And, from a gin drinker's perspective, it is interesting to consider whether botanicals are alike because of their name or because of their flavor characteristics. If the latter, matching on names that are alike may return botanicals that, from a flavor perspective, are not alike at all. — jvillian, Mar 07 '18 at 23:49
hey @jvillian. Yes I did thanks. See my own answer below. Was pretty simple in the end. — Simon Cooper, Mar 08 '18 at 16:28

jvillian · Answer 1 · 2018-03-07T23:34:29.083

2

Assuming you have your associations set up correctly (which it is not at all obvious you do), then I imagine you could do someting like:

class Gin < ApplicationRecord 
  has_many :gin_botanicals 
  has_many :botanicals, through: :gin_botanicals

  class << self 

    def with_botanicals(botanicals)
      joins(:bontanicals).where(botanicals: {id: botanicals})
    end

  end

end

I've said it before and I'll say it again. Some people will prefer a scope to a class method. And some people will prefer self.with_botanicals to class << self. These are, arguably, matters of preference.

Then, let's say you have a @gin variable and a @botanical variable. You should be able to do something like:

Gin.with_botanicals(@botanical).where.not(@gin)

This should give you all the gins that have @botanical but that are not @gin. You could match on all @gin.botanicals by doing something like:

Gin.with_botanicals(@gin.botanicals).where.not(@gin)

If you want to find all gins that have any botanicals in common with @gin, then that's a slightly different kettle of fish (which involves use of an IN clause) and suitable for a new question. Similarly, you could search for all gins that have botanicals with names LIKE @gin.bontanicals.pluck(:name), but, again, a different sort of problem.

edited Mar 07 '18 at 23:34

answered Mar 07 '18 at 22:56

jvillian

19,953
5
31
44

A class method on a Model returning an `ActiveRecord::Relation` object _really_ looks like it could be a `scope` instead (IMO) – MrYoshiji Mar 07 '18 at 23:19
@MrYoshiji - Yessir, indeed (as acknowledged in my answer). I just get hung up on the Guide which states, "Using a class method is the preferred way to accept arguments for scopes." – jvillian Mar 07 '18 at 23:21
1

May I ask you which guide states this? The official Rails Guide? I am surprised with this. – MrYoshiji Mar 07 '18 at 23:23
1

Yessir, the official Rails Guide for the Active Record Query Interface. You can find it in the last paragraph of [section 14.1 - "Passing in arguments"](http://guides.rubyonrails.org/active_record_querying.html#passing-in-arguments). (BTW and apropos of nothing, your profile image was the direct inspiration for an Easter egg I hid in one of my recent projects. Thank you!) – jvillian Mar 07 '18 at 23:25

max · Answer 2 · 2018-03-08T00:25:52.977

When viewing the show page for a given botanical, I would like to display a list of gins that also contain that specific botanical.

Simple. Just do:

@botanical = Botanical.includes(:gins).find(params[:id])
@gins = @botanical.gins

If you have an id already there is no need for a LIKE query. And since you have setup an indirect association you can just use it to fetch the gins.

If what you really want is to get other gins which have botanicals in common to a given gin you would do it something like this:

class Gin < ApplicationRecord
  has_many :gin_botanicals
  has_many :botanicals, through: :gin_botanicals

  def similiar_gins
    Gin.joins(:botanicals)
     .where(botanicals: { id: self.botanical_ids })
     .where.not(id: self.id)
  end
end

.joins creates a left inner join - so any rows that do not have matches in the join table are discarded.

.where(botanicals: { id: self.botanical_ids }) creates a WHERE IN query that requires the joined records to have a least one botanical in common.

You can also set a required level of simularity by using GROUP BY and HAVING:

class Gin < ApplicationRecord
  has_many :gin_botanicals
  has_many :botanicals, through: :gin_botanicals

  def similiar_gins(common_ingredients: 1)
    Gin.joins(:botanicals)
     .where(botanicals: { id: self.botanical_ids })
     .where.not(id: self.id)
     .group("gins.id")
     .having("COUNT(distinct botanicals.id) >= ?", common_ingredients)
  end
end

Given:

irb(main):039:0> Gin.all.pluck(:id, :name)
   (1.1ms)  SELECT "gins"."id", "gins"."name" FROM "gins"
=> [[1, "Beefeater Gin"], [2, "Bombay Sapphire"], [3, "Mockinghamshire"]]
irb(main):040:0> Botanical.all.pluck(:id, :name)
   (1.1ms)  SELECT "botanicals"."id", "botanicals"."name" FROM "botanicals"
=> [[1, "Almond"], [2, "liquorice"], [3, "Foo"]]
irb(main):041:0> GinBotanical.all.pluck(:gin_id, :botanical_id)
   (0.5ms)  SELECT "gin_botanicals"."gin_id", "gin_botanicals"."botanical_id" FROM "gin_botanicals"
=> [[1, 1], [2, 1], [1, 3], [1, 2], [2, 2]]

With 2 common ingredients:

irb(main):036:0> Gin.first.similiar_gins(common_ingredients: 2)
  Gin Load (1.2ms)  SELECT  "gins".* FROM "gins" ORDER BY "gins"."id" ASC LIMIT $1  [["LIMIT", 1]]
   (4.0ms)  SELECT "botanicals".id FROM "botanicals" INNER JOIN "gin_botanicals" ON "botanicals"."id" = "gin_botanicals"."botanical_id" WHERE "gin_botanicals"."gin_id" = $1  [["gin_id", 1]]
  Gin Load (4.3ms)  SELECT "gins".* FROM "gins" INNER JOIN "gin_botanicals" ON "gin_botanicals"."gin_id" = "gins"."id" INNER JOIN "botanicals" ON "botanicals"."id" = "gin_botanicals"."botanical_id" WHERE "botanicals"."id" IN (1, 2, 3) AND ("gins"."id" != $1) GROUP BY gins.id HAVING (COUNT(distinct botanicals.id) >= 2)  [["id", 1]]
=> #<ActiveRecord::Relation [#<Gin id: 2, name: "Bombay Sapphire", created_at: "2018-03-07 23:44:43", updated_at: "2018-03-07 23:44:43">]>

But if we set it to 3 we get an empty set:

irb(main):037:0> Gin.first.similiar_gins(common_ingredients: 3)
  Gin Load (0.7ms)  SELECT  "gins".* FROM "gins" ORDER BY "gins"."id" ASC LIMIT $1  [["LIMIT", 1]]
   (1.8ms)  SELECT "botanicals".id FROM "botanicals" INNER JOIN "gin_botanicals" ON "botanicals"."id" = "gin_botanicals"."botanical_id" WHERE "gin_botanicals"."gin_id" = $1  [["gin_id", 1]]
  Gin Load (5.0ms)  SELECT "gins".* FROM "gins" INNER JOIN "gin_botanicals" ON "gin_botanicals"."gin_id" = "gins"."id" INNER JOIN "botanicals" ON "botanicals"."id" = "gin_botanicals"."botanical_id" WHERE "botanicals"."id" IN (1, 2, 3) AND ("gins"."id" != $1) GROUP BY gins.id HAVING (COUNT(distinct botanicals.id) >= 3)  [["id", 1]]
=> #<ActiveRecord::Relation []>

Interesting! If I understand it, that will return all gins that have (as a default), one botanical in common with `@gin`. Is that correct? I'm curious what the OP meant by "a list of gins that also contain that specific botanical". — jvillian, Mar 07 '18 at 23:54
@jvillian exactly. The "HAVING COUNT(distinct botanicals.id) = x" sets how many botanicals.ids are needed for the row to be returned — max, Mar 08 '18 at 00:00
Edited. Should be `HAVING COUNT(distinct botanicals.id) >= ?` to get the correct result. — max, Mar 08 '18 at 00:05
Well, skip-to-my-lou-my-darlin'! Learn something new every day. (Especially hanging around you lot.) I'm gonna try and stick some of that stuff in the buffer. — jvillian, Mar 08 '18 at 00:45
@jvillian I can't really take all the credit since I got this from the community. https://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more — max, Mar 08 '18 at 01:20
You've also given me some insight into something I also wanted to do, show similar gins, so where they have x number of similar botanicals, and was wondering how I might do that. Useful for some other projects I have too. Which is different to my OP here, where I'm just after all of the gins that contain a single botanical. — Simon Cooper, Mar 08 '18 at 13:10

score 0 · Answer 3 · answered Mar 08 '18 at 11:54

It was quite an easy fix in the end. Someone posted this answer last night but then quickly deleted it.

I required a simple join:

Gin.joins(:botanicals).where("botanicals.name LIKE ?", "%#{@botanical.name}%")

So now, when on the show page of botanical 'angelica root' I have an each loop that shows me any other gins that also have 'angelica root' listed in their botanicals.

Rails 5.1 where query on has many model

3 Answers3