按“最佳匹配”排序基于关系表值 [英] Order by "best match" based on relational tables values

问题描述

我有以下实体：

Project

拥有多个相关表格：

Project >-< ProjectAttribute >-< AttributeType
Project >-< ProjectAttribute >-< AttributeValue
Project >-< ProjectTask >------< Task
Project >-< ProjectTask >------< Employee
... 

涉及约15个表，包括子连接。

Involving about 15 tables including subjoins.

现在，我需要通过比较值并计算出现次数，为单个项目找到最符合需求的项目。例如AttributeType和AttributeValue的匹配将项目的最佳匹配指标提高1。

Now I'm required to find the best matching Projects for a single Project given, by comparing the values and counting the occurrences. e.g. a match of AttributeType and AttributeValue increases the "best match" indicator of a Project by 1.

如何实现？

推荐答案

我想我发现如何查询相似之处：

I think I found out how to query similarities:

SELECT 
    sp.*,
    ((
        SELECT COUNT(spa.id) FROM project_attribute AS spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attribute_value = pa.attribute_value
    ) * 1)
    + 
    ((
        SELECT COUNT(spt.id) FROM project_task AS spt WHERE spt.project = sp.id AND spt.address = pt.address    
    ) * .25)
    + 
    ((
        SELECT COUNT(spc.id) FROM project_campaign AS spc WHERE spc.project = sp.id AND spc.campaign = pc.campaign  
    ) * 2)
    AS similarity
FROM project AS p
LEFT JOIN project_attribute AS pa ON (p.id = pa.project)
LEFT JOIN project_task AS pt ON (p.id = pt.project)
LEFT JOIN project_campaign AS pc ON (p.id = pc.project)
LEFT JOIN project AS sp ON (p.id != sp.id)
WHERE 
    p.id = <PROJECT-ID>
GROUP BY sp.id
ORDER BY 
    similarity DESC

我还添加了一个乘数来控制类似行的影响。

I also added a multiplicator to control the impact of similar rows.

性能不是最好的（235项目约为200ms），但符合我目前的需求。

The performance isn't the best (~200ms for 235 projects), but it fits my current needs.

这篇关于按“最佳匹配”排序基于关系表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！