Doctrine ORM - 自连接,没有任何实际关系
我试图通过匹配对象标签来查找与一个对象相关的对象。我构建了一个 mysql 查询,它将通过计算匹配标签来返回最匹配的对象。
我是学说 (1.2) 的新手,所以我想知道是否有人可以帮助我走上正确的道路,修改我的架构并创建 DQL 查询?我猜最大的问题是这两个标记集在我的 schema.yml 中并不相互关联。
Schema.yml:
Object:
columns:
name:
relations:
Tags: { foreignAlias: Objects, class: Tag, refClass: Tagset}
Tagset:
columns:
object_id: {type: integer, primary: true, notnull: true}
tag_id: { type: integer, primary: true, notnull: true }
relations:
Object: { foreignAlias: Tagsets }
Tag: { foreignAlias: Tagsets }
Tag:
columns:
name: { type: string(255), notnull: true }
Object: { foreignAlias: Tags, class: Object, refClass: Tagset}
这是使用上面的模式运行的 mysql 查询:
SELECT object.name, COUNT(*) AS tag_count
FROM tagset T1
INNER JOIN tagset T2
ON T1.tag_id = T2.tag_id AND T1.object_id != T2.object_id
INNER JOIN object
ON T2.object_id = object.id
WHERE T1.object_id = 2
GROUP BY T2.object_id
ORDER BY COUNT(*) DESC
I'm trying to find related objects to one object by matching the objects tags. I've constructed a mysql query which will return the objects that match the most by counting the matching tags.
I'm new to doctrine (1.2) so I'm wondering if someone could help me to get on the right track modifying my schema and creating a DQL query? The big problem is that the two tagset doesn't relate to each others in my schema.yml I would guess.
Schema.yml:
Object:
columns:
name:
relations:
Tags: { foreignAlias: Objects, class: Tag, refClass: Tagset}
Tagset:
columns:
object_id: {type: integer, primary: true, notnull: true}
tag_id: { type: integer, primary: true, notnull: true }
relations:
Object: { foreignAlias: Tagsets }
Tag: { foreignAlias: Tagsets }
Tag:
columns:
name: { type: string(255), notnull: true }
Object: { foreignAlias: Tags, class: Object, refClass: Tagset}
Here is the mysql query which works using the schema above:
SELECT object.name, COUNT(*) AS tag_count
FROM tagset T1
INNER JOIN tagset T2
ON T1.tag_id = T2.tag_id AND T1.object_id != T2.object_id
INNER JOIN object
ON T2.object_id = object.id
WHERE T1.object_id = 2
GROUP BY T2.object_id
ORDER BY COUNT(*) DESC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您也可以使用子查询。像这样的事情:
You can use subqueries as well. Something like this:
解决方案:
Solution: