php - MySQL 查询 - 多对多

Question

我需要为用户找到匹配项。

每个用户都有多个“工作类别”(如会计、银行、金融等)和多个“首选工作类别”(在这些指定类别中查找匹配的个人资料) )

使用 MySQL 查询查找匹配项的有效方法是什么。

MyProfile 表的结构:

User_Id | Work_Id
1       | 5
1       | 7
1       | 8
2       | 7
2       | 9

PreferedWork表的结构

User_Id | Work_Id
1       | 7
1       | 9
2       | 1
2       | 8

我必须检查 100,000 个个人资料并仅返回 10 个个人资料。

编辑:我的原始查询(没有连接):

$getMatchesQry = "
        select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
      YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
        from entity_details ed,entity ent, preferences pr
        where 
        (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
        and
        (" . $pref_sex_str . ")  
        and
        (" . $prefComWord . ") and (" . $prefRelWord . ")
        and
        pr.Orientation  = '" . $prefRow['Orientation'] . "' 
        and             
        '" . $prefRow['min_height'] . "' <= ed.height 
        and 
        ed.height  <= '" . $prefRow['max_height'] . "'                                
        and            
        ed.Entity_Id = ent.Entity_Id and  
        ed.Entity_Id = pr.Entity_Id and              
        ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
        ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
        having 
        age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10";

当我添加加入时，

$getMatchesQry = "
        select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
      YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
        from entity_details ed,entity ent, preferences pr, **pref_occupation ul1, users_occ ul2**
        where 
        (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
        and
        (" . $pref_sex_str . ")  
        and
        (" . $prefComWord . ") and (" . $prefRelWord . ")
        and
        pr.Orientation  = '" . $prefRow['Orientation'] . "' 
        and             
        '" . $prefRow['min_height'] . "' <= ed.height 
        and 
        ed.height  <= '" . $prefRow['max_height'] . "'                    
        and
        **( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id )**
        and            
        ed.Entity_Id = ent.Entity_Id and  
        ed.Entity_Id = pr.Entity_Id and              
        ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
        ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
        having 
        age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 

当我添加 JOIN(在粗体中提到)时，我只得到部分结果，而不是合并原始查询的结果。

我想，我搞砸了 AND 或其他重要的链接运算符。

编辑 2::立即获取正确的值。无论如何要优化它吗？

$getMatchesQry = "select DISTINCT ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
  YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
    from entity_details ed, entity ent, preferences pr, pref_occupation ul1, users_occ ul2, 
    where 
    (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
    and
    (" . $pref_sex_str . ")  
    and
    (" . $prefComWord . ") and (" . $prefRelWord . ")
    and
    pr.Orientation  = '" . $prefRow['Orientation'] . "' 
    and             
    '" . $prefRow['min_height'] . "' <= ed.height 
    and 
    ed.height  <= '" . $prefRow['max_height'] . "'                    
    and
    ( ( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id ))        
    and            
    ed.Entity_Id = ent.Entity_Id and  
    ed.Entity_Id = pr.Entity_Id and              
    ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
    ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))           
    having 
    age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 

Answer 1

你的问题似乎并不完整，但据我了解，你可以尝试这样的事情:

SQL Fiddle

MySQL 5.5.32 架构设置:

CREATE TABLE MyProfile
    (`User_Id` int, `Work_Id` int)
;

INSERT INTO MyProfile
    (`User_Id`, `Work_Id`)
VALUES
    (1, 5),
    (1, 7),
    (1, 8),
    (2, 7),
    (2, 9),
    (3, 10)
;

CREATE TABLE PreferedWork
    (`User_Id` int, `Work_Id` int)
;

INSERT INTO PreferedWork
    (`User_Id`, `Work_Id`)
VALUES
    (1, 7),
    (1, 9),
    (2, 1),
    (2, 8)
;

查询 1:

SELECT mp.User_ID,
       GROUP_CONCAT(DISTINCT mp.Work_Id ORDER BY mp.Work_Id) AS `work category`,
       GROUP_CONCAT(DISTINCT ifnull(pw.Work_Id,'') ORDER BY pw.Work_Id) AS `preferred work category`
FROM MyProfile mp
LEFT OUTER JOIN PreferedWork pw ON mp.User_ID = pw.User_ID
GROUP BY mp.User_ID

<强> Results :

| USER_ID | WORK CATEGORY | PREFERRED WORK CATEGORY |
|---------|---------------|-------------------------|
|       1 |         5,7,8 |                     7,9 |
|       2 |           7,9 |                     1,8 |
|       3 |            10 |                         |

或者您可能正在寻找这样的东西:

SELECT mp.User_ID,
FROM MyProfile mp
LEFT OUTER JOIN PreferedWork pw ON mp.User_ID = pw.User_ID
WHERE (mp.Work_Id = '8' or pw.Work_Id = '8')
LIMIT 10