首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL -基于多态表值的条件查询结果?

问MySQL -基于多态表值的条件查询结果?
EN

Stack Overflow用户
提问于 2019-05-21 04:09:02
回答 1查看 587关注 0票数 2

我有一个多态表creationables,无法根据creationables字段值(human或robot),根据是否从各自的表(humans、robots以及robots和robot_upgrades组合)中删除它们,为条件查询找出一个关键部分。下面是4个表,后面跟着伪查询:

1.可创建的

代码语言:javascript
复制
| id | creationable_type | creationable_id | robot_upgrade_id | is_activated
----------------------------------------------------------------------------
| 1  |      human        |       1         |         0        |      1
| 2  |      robot        |       1         |         0        |      1
| 3  |      robot        |       2         |         1        |      1

2.人类

代码语言:javascript
复制
| id | name | deleted
---------------------
| 1  | Adam |  NULL

3.机器人

代码语言:javascript
复制
| id |  name   | deleted
------------------------
| 1  | Droid X |  NULL
| 2  | Droid Y |  NULL

4. robot_upgrades

代码语言:javascript
复制
| id |  upgrade_name   | deleted
--------------------------------
| 1  |    Patch V4     |  NOW()

伪查询:

代码语言:javascript
复制
SELECT *
FROM creationables

 // If creationable_type == 'human'
 // we want to get non deleted humans
    JOIN humans ON humans.id=creationable_id WHERE humans.deleted=NULL

 // If creationable_type == 'robot' and robot_upgrade_id == '0'
 // we want to get non deleted robots
    JOIN robots ON robots.id=creationable_id WHERE robots.deleted=NULL

 // If creationable_type == 'robot' and robot_upgrade_id != '0'
 // we want to check both robots and robot_upgrades tables
 // and if either of them was deleted we do not want to return them
 // we want to get non deleted robots/robot_upgrades combo
    JOIN robots ON robots.id=creationable_id WHERE robots.deleted=NULL
    JOIN robot_upgrades ON robot_upgrades.id=robot_upgrade_id WHERE robot_upgrades.deleted=NULL

WHERE creationables.is_activated = '1'

知道基于伪查询中的注释正确的条件查询是什么吗?

UPDATE这里是一个小提琴

预期结果应如下所示:

代码语言:javascript
复制
| id | creationable_type | creationable_id | robot_upgrade_id | is_activated
----------------------------------------------------------------------------
| 1  |      human        |       1         |         0        |      1
| 2  |      robot        |       1         |         0        |      1

不应返回带有id 3的creationables行,因为即使其机器人未从robots中删除,其相关的robot_upgrade_id 1仍在robot_upgrades中被删除。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-05-21 07:19:01

没有直接的方法可以做到这一点。内部联接将在第一个联接之后排除前面的类型,因此您可以使用左联接来执行如下操作:

代码语言:javascript
复制
SELECT *
FROM creationables A
    LEFT JOIN humans ON humans.id= A.creationable_id AND A.creationable_type = 'human' AND humans.deleted is NULL
    LEFT JOIN robots ON robots.id= A.creationable_id AND A.creationable_type = 'robot' AND robots.deleted is NULL
    LEFT JOIN robot_upgrades ON robot_upgrades.id=A.robot_upgrade_id AND A.creationable_type = 'robot' and robot_upgrade_id != '0' AND robot_upgrades.deleted is NULL
WHERE creationables.is_activated = '1';

或者你可以尝试一下这样的联盟方法:

代码语言:javascript
复制
SELECT * FROM creationables A JOIN humans ON humans.id= A.creationable_id AND A.creationable_type = 'human' AND humans.deleted is NULL
  UNION
SELECT * FROM creationables A JOIN robots ON robots.id= A.creationable_id AND A.creationable_type = 'robot' AND robots.deleted is NULL
  UNION
SELECT * FROM creationables A JOIN robot_upgrades ON robot_upgrades.id=A.robot_upgrade_id AND A.creationable_type = 'robot' and robot_upgrade_id != '0' AND robot_upgrades.deleted is NULL
WHERE creationables.is_activated = '1';

如果删除了机器人更新,它也不应该返回机器人,那么您可以使用以下查询:

代码语言:javascript
复制
SELECT A.creationable_type, humans.name, null as "update" 
FROM creationables A 
JOIN humans ON humans.id= A.creationable_id AND A.creationable_type = 'human' AND humans.deleted is NULL
  UNION
SELECT A.creationable_type, robots.name, robot_upgrades.upgrade_name as "update"
FROM creationables A 
JOIN robots ON robots.id= A.creationable_id AND A.creationable_type = 'robot' AND robots.deleted is NULL
LEFT JOIN robot_upgrades ON robot_upgrades.id=A.robot_upgrade_id AND A.creationable_type = 'robot' and robot_upgrade_id != '0' AND robot_upgrades.deleted is NULL
WHERE A.is_activated = '1' AND robots.id NOT IN
(SELECT DISTINCT A.creationable_id FROM creationables A
JOIN robot_upgrades ON robot_upgrades.id=A.robot_upgrade_id AND A.creationable_type = 'robot' and robot_upgrade_id != '0' AND robot_upgrades.deleted is NOT NULL);

这些可能不是最好的方法,但会完成工作,希望这有帮助!

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56230943

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档