MySQL多个兴趣匹配问题 [英] MySQL Multiple interests matching problem

问题描述

我有一个数据库，用户输入他们的兴趣。我想找到有兴趣的人。
$ b $利息表的结构是

$ p $ c> interestid |用户名|爱好|位置| level | matchinginterestids

让我们带两位用户来简单介绍一下。

• 用户Joe可能有10个不同的兴趣记录


• 用户greg可能有10个不同的兴趣记录。

我想要做以下算法

$ ul
<李>把乔的兴趣记录1，并从兴趣数据库中寻找匹配的兴趣爱好和地点。把任何匹配的兴趣ID在匹配字段中。然后去乔的兴趣记录2等。

我想我需要的是某种循环，将遍历所有joe's intersts，然后每次在兴趣数据库中找到一个匹配时进行更新。这在MySQL中甚至是可能的吗？

---

另外的例子：

我是丹。我有3个兴趣。每个兴趣是由3个主题组成：

• 丹猫，营养，头发丹b超， dna，显微镜


• Dan film，slowmotion，fightscenes

其他人可能有其他兴趣

乔：

• 乔猫，营养，力量
  
• Joe superlens，dna，显微镜
  $ b $ mo $ lt; b
  
  
  
  
  • Moe mysql，查询，php
  
  
  • Moe film，specialfx，cameras
  
  
  • Moe superlens，dna，microscopes
  >
  
  
  
  

  现在我希望查询以丹的身份登录时返回以下内容：
  
  

  这里是你的兴趣匹配：

  
  
  

  ---对猫营养头发感兴趣
  
  Joe对猫和营养感兴趣
  
  Joe和Moe对超级镜头，dna，显微镜感兴趣
  
  Moe对电影感兴趣
  
  

  查询需要遍历所有Dan's in并且比较3,2,1个主题比赛。

  
  
  

  我可以在循环中使用php来做这件事，但是它会一直调用数据库来获得结果。我想知道是否有一个狡猾的方式来做到这一点使用单个查询也许3个单独的查询，一个寻找3个匹配，一个为2，一个为1.
  

  解决方案

  这对于MySQL来说绝对是可能的，但我认为你可能会以一种尴尬的方式来解决这个问题。
  $ b $ pre $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'$ $ $ $' interestId，hobby）
  TABLE UserInterests（userId，interestId，level）
  

  当用户添加一个兴趣，如果它之前没有被添加，你将它添加到 Interests 表中，然后将它添加到 UserInterests 表。当你想检查附近其他人有相似的兴趣，你可以简单地查询 UserInterests 表为其他人有相似的兴趣，其中已经有你的所有信息：
  $ b $ pre $ SELECT DISTINCT userId
FROM UserInterests
WHERE interestId IN（
SELECT interestId
FROM UserInterests
WHERE userId = $ JoesID
）


  没有子查询以更优雅的方式完成，但这是我现在想到的。

  
  

  I have a database where users enter their interests. I want to find people with matching interests.

  The structure of the interest table is

  interestid | username | hobby | location | level | matchinginterestids

  Let's take two users to keep it simple.

  • User Joe may have 10 different interest records
  • User greg may have 10 different interest records.

  I want to do the following algorithm

  • Take Joe's interest record 1 and look for matching hobbies and locations from the interest database. Put any matching interest id's in the matches field. Then go to joe's interest record 2 etc..

  I guess what I need is some sort of for loop that will loop through all of joe's intersts and then do an update each time it finds a match in the interest database. Is that even possible in MySQL?

  ---

  Further example:

  I am Dan. I have 3 interests. Each interest is composed of 3 subjects:

  • Dan cats,nutrition,hair
  • Dan superlens,dna,microscopes
  • Dan film,slowmotion,fightscenes

  Other people may have other interests

  Joe:

  • Joe cats,nutrition,strength
  • Joe superlens,dna,microscopes

  Moe

  • Moe mysql,queries,php
  • Moe film,specialfx,cameras
  • Moe superlens,dna,microscopes

  Now I want the query to return the following when I log in as Dan:

  Here are your interest matches:

  --- is interested in cats nutrition hair
  Joe is interested in cats and nutrition
  Joe and Moe are interested in superlens, dna, microscopes
  Moe is interested in film

  The query needs to iterate through all Dan's interests, and compare 3,2,1 subject matches.

  I could do this in php from a loop but it would be calling the database all the time to get the results. I was wondering if there's a crafty way to do it using a single query Or maybe 3 separate queries one looking for 3 matches, one for 2 and one for 1.

  解决方案

  This is definitely possible with MySQL, but I think you may be going about it in an awkward way. I would begin by structuring the tables as follows:

  TABLE Users ( userId, username, location )
  TABLE Interests( interestId, hobby )
  TABLE UserInterests( userId, interestId, level )
  

  When a user adds an interest, if it hasn't been added before, you add it to the Interests table, and then add it to the UserInterests table. When you want to check for other nearby folks with similar interests, you can simply query the UserInterests table for other people who have similar interests, which has all that information for you already:

  SELECT DISTINCT userId
    FROM UserInterests
    WHERE interestId IN (
       SELECT interestId
         FROM UserInterests
         WHERE userId = $JoesID
       )
  

  This can probably be done in a more elegant fashion without subqueries, but it's what I thought of now.

  这篇关于MySQL多个兴趣匹配问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！