英文:
Join and derive relation when more than single column has foreign key reference to another table
问题
SELECT r1.name, r2.name FROM role_relations rr
JOIN roles r1 ON rr.id = r1.id
JOIN roles r2 ON rr.parent_id = r2.id
WHERE rr.role_relation_id = :rrId;
英文:
I have table role_relations where columns are referencing same another table called 'roles'. Currently i'm joining twice. Is there better way where join once and derive 'name' ?
SELECT r1.name, r2.name FROM role_relations rr
JOIN roles r1 ON rr.id = r1.id
JOIN roles r2 ON rr.parent_id = r2.id
WHERE rr.role_relation_id = :rrId;
答案1
得分: 1
如果您有层次数据需要两次加入定义，这是正常的。我怀疑您是否存在性能问题，因为对于安全层次（角色、组）执行此类查询是常见的，基本上数据规模不会很大（您具有一定数量的角色，而不是数十亿个）。
如果您担心数据被双重读取，可以只读取role_relations和roles一次，然后在应用程序中构建输出。
英文:
It is normal if you have hierarchy data to need to join the definitions twice. I doubt you have performance issues, as it's common for security hierarchy (roles, groups) to perform such queries and basically the data is not huge (you have certain number of roles, not billions of them).
If you are concern about the double read of the data you can read the role_relations and roles once and build the output in the application.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论