Not a simple SELECT * FROM, right?

Question

MySQL

Help with the request, I’ve already broken my head, if it’s difficult - where to look, where to read the article, I absolutely don’t understand how to make such a request:

There are models:

User {
  ID uint
  Name string
}

Fruit {
  ID uint
  Name string
  Similar []*Fruit // many2many
}

RateFruit {
  UserID uint
  User User // has one
  FruitID uint
  Fruit Fruit // has one
  IsLiked bool
}

i need to get all similar user's fruits which are not in the list of rated fruits

In simple terms:

user rated - apple[similar: orange, banana], banana[mango, melon]

need result:

orange, mango, melon (there is no banana, it has already been rated )

Answer 1

for many-to-many relationships, one more table is needed.
The edge of the graph of connectivity A, B (key1, key2)
here or in the base to put as two vectors AB and BA
requests for sampling is easier
or once. the base is smaller, but then jogging queries key1 and key2 Join the
rating table (A) with the table of analogues by key1=A.fruit_id
left outer join with the rating table (B) by key2 = B.fruit_id
where A.user rated and B.user null
From the query above, the id of the frks will be known. Now connect with the table of frks itself to reveal the names.