1

I'm not sure how to better ask this question. I have model Item and model Tag, assotiation has_and_belongs_to_many

I need to get result from table items_tags which contains item_id's that have records with 2 tags. Example:

enter image description here

Here I need item_id which tag_id both in 1 and 2. (result: item_id=4)

So if I use WHERE tag_id = 1 AND tag_id = 2 - there are no results. If WHERE tag_id IN (1,2) - too many results (red and green)

I can follow the next steps:

1) first = tag1.items.pluck(:id)

2) second = tag2.items.pluck(:id)

3) what_i_need = (first & second)

Another way is one query: tag1.items.where(id: tag2.products.pluck(:id))

Is there is another (more faster) way to do it with some variant of JOIN?

Николай Агеев
  • 541
  • 6
  • 16
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Also, links die. Insert images/links using edit functions. Make your post self-contained. – philipxy Oct 20 '19 at 18:26
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. PS What langauge/api are you coding this in? – philipxy Oct 20 '19 at 18:34

1 Answers1

1

I hope this solves the problem:

tag_ids = [1, 2]
Item.joins(:tags).where(tags: { id: tag_ids }).group(:id).having("count(*) = ?", tag_ids.size)
demir
  • 4,591
  • 2
  • 22
  • 30
  • How do you think, can I add some indexes for this type of queries to improve performance? – Николай Агеев Oct 20 '19 at 19:34
  • @НиколайАгеев Yes, using index can improve performance. It is good to use if there is a lot of data in the table. – demir Oct 20 '19 at 19:56
  • Can you help - what index can I add just for this problem? I have working indexes for Item table. But if there are many associations item_id -> tag_id it take much time for this (joins,having) query – Николай Агеев Oct 20 '19 at 19:59
  • If you add relationships as a reference, rails automatically adds the index. Check your schema.rb – demir Oct 20 '19 at 20:01
  • @НиколайАгеев If you want to add an index, create a migration file: `rails generate migration AddTagIndexToItems` and use add_index method in change method: `add_index :items, :tag_id` then run `rails db:migrate`. check this link https://api.rubyonrails.org/v6.0.0/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index – demir Oct 20 '19 at 20:24
  • Will it work if it is `has_and_belongs_to_many ` ? Because there is no tag_id key in Item model. It has tag_ids instead of tag_id. And Tag has product_ids method/key – Николай Агеев Oct 20 '19 at 20:46
  • @НиколайАгеев In this case, you must add it to the join table. – demir Oct 20 '19 at 21:03
  • @НиколайАгеев check this links: 1. https://stackoverflow.com/questions/15210639/need-two-indexes-on-a-habtm-join-table 2. https://stackoverflow.com/questions/33946044/how-should-i-use-rails-to-index-and-query-a-join-table 3. https://stackoverflow.com/questions/28075782/how-to-properly-index-fields-on-a-rails-join-table-migration – demir Oct 20 '19 at 21:05
  • 1
    Great! Thank you – Николай Агеев Oct 21 '19 at 20:55