mysql - 模型/表别名不起作用(INNER JOIN)

Question

问题描述
我将Users表与Tests表关联两次，一次在别名Maintainers下。当我定义了MaintainersbelongsTo关联时，没有返回任何结果。我需要弄清楚为什么第一个查询没有返回任何结果。
不起作用

SELECT
Tests.id AS `Tests__id`,
Tests.title AS `Tests__title`,
Tests.slug AS `Tests__slug`,
Tests.description AS `Tests__description`,
Tests.user_id AS `Tests__user_id`,
Tests.maintainer_id AS `Tests__maintainer_id`,
Tests.test_question_count_total AS `Tests__test_question_count_total`,
Tests.test_question_count_mature AS `Tests__test_question_count_mature`,
Tests.test_session_count_total AS `Tests__test_session_count_total`,
Tests.test_session_count_mature AS `Tests__test_session_count_mature`,
Tests.photo AS `Tests__photo`,
Tests.photo_dir AS `Tests__photo_dir`,
Tests.photo_size AS `Tests__photo_size`,
Tests.view_count AS `Tests__view_count`,
Tests.published AS `Tests__published`,
Tests.mature AS `Tests__mature`,
Tests.flagged AS `Tests__flagged`,
Tests.deleted AS `Tests__deleted`,
Tests.created AS `Tests__created`,
Tests.modified AS `Tests__modified`,
Users.id AS `Users__id`,
Users.first_name AS `Users__first_name`,
Users.last_name AS `Users__last_name`,
Users.username AS `Users__username`,
Users.email AS `Users__email`,
Users.password AS `Users__password`,
Users.token AS `Users__token`,
Users.photo AS `Users__photo`,
Users.photo_dir AS `Users__photo_dir`,
Users.photo_size AS `Users__photo_size`,
Users.bio AS `Users__bio`,
Users.is_admin AS `Users__is_admin`,
Users.created AS `Users__created`,
Users.modified AS `Users__modified`,
Maintainers.id AS `Maintainers__id`,
Maintainers.first_name AS `Maintainers__first_name`,
Maintainers.last_name AS `Maintainers__last_name`,
Maintainers.username AS `Maintainers__username`,
Maintainers.email AS `Maintainers__email`,
Maintainers.password AS `Maintainers__password`,
Maintainers.token AS `Maintainers__token`,
Maintainers.photo AS `Maintainers__photo`,
Maintainers.photo_dir AS `Maintainers__photo_dir`,
Maintainers.photo_size AS `Maintainers__photo_size`,
Maintainers.bio AS `Maintainers__bio`,
Maintainers.is_admin AS `Maintainers__is_admin`,
Maintainers.created AS `Maintainers__created`,
Maintainers.modified AS `Maintainers__modified`
FROM
tests Tests
INNER JOIN users Users ON Users.id = (Tests.user_id)
INNER JOIN users Maintainers ON Maintainers.id = (Tests.maintainer_id)
WHERE
(
Tests.slug = 'what-word-are-youquestion'
AND Tests.deleted IS NULL
)
LIMIT
1

作品

SELECT
Tests.id AS `Tests__id`,
Tests.title AS `Tests__title`,
Tests.slug AS `Tests__slug`,
Tests.description AS `Tests__description`,
Tests.user_id AS `Tests__user_id`,
Tests.maintainer_id AS `Tests__maintainer_id`,
Tests.test_question_count_total AS `Tests__test_question_count_total`,
Tests.test_question_count_mature AS `Tests__test_question_count_mature`,
Tests.test_session_count_total AS `Tests__test_session_count_total`,
Tests.test_session_count_mature AS `Tests__test_session_count_mature`,
Tests.photo AS `Tests__photo`,
Tests.photo_dir AS `Tests__photo_dir`,
Tests.photo_size AS `Tests__photo_size`,
Tests.view_count AS `Tests__view_count`,
Tests.published AS `Tests__published`,
Tests.mature AS `Tests__mature`,
Tests.flagged AS `Tests__flagged`,
Tests.deleted AS `Tests__deleted`,
Tests.created AS `Tests__created`,
Tests.modified AS `Tests__modified`,
Users.id AS `Users__id`,
Users.first_name AS `Users__first_name`,
Users.last_name AS `Users__last_name`,
Users.username AS `Users__username`,
Users.email AS `Users__email`,
Users.password AS `Users__password`,
Users.token AS `Users__token`,
Users.photo AS `Users__photo`,
Users.photo_dir AS `Users__photo_dir`,
Users.photo_size AS `Users__photo_size`,
Users.bio AS `Users__bio`,
Users.is_admin AS `Users__is_admin`,
Users.created AS `Users__created`,
Users.modified AS `Users__modified`
FROM
tests Tests
INNER JOIN users Users ON Users.id = (Tests.user_id)
WHERE
(
Tests.slug = 'what-word-are-youquestion'
AND Tests.deleted IS NULL
)
LIMIT
1

编辑：我被告知INNER JOIN需要两个表中的数据，这可能是失败的原因，因为在tests表中，maintainer_id是null。

Answer 1

我解决了这个问题，我使用的是CakePHP框架。我最终从关联定义中删除了joinType关联，并让框架决定连接类型。
编辑：我验证了有人告诉我的一个提示（请参阅操作编辑），这是因为我正在加载的特定记录中的maintainer_id在NULL上设置为LEFT JOIN。ALEFT JOIN要求两个表中都有数据。一旦我将一个maintainer_id设置为一个已有的记录，一切都会有效。更好的选择是使用maintainer_id，因为LEFT JOIN并不总是会被填充。
不起作用

$this->belongsTo('Users', [
    'foreignKey' => 'user_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('Maintainers', [
    'className' => 'Users',
    'foreignKey' => 'maintainer_id',
    'joinType' => 'INNER'
]);

作品

$this->belongsTo('Users', [
    'foreignKey' => 'user_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('Maintainers', [
    'className' => 'Users',
    'foreignKey' => 'maintainer_id'
]);

结果查询

SELECT 
  Tests.id AS `Tests__id`, 
  Tests.title AS `Tests__title`, 
  Tests.slug AS `Tests__slug`, 
  Tests.description AS `Tests__description`, 
  Tests.user_id AS `Tests__user_id`, 
  Tests.maintainer_id AS `Tests__maintainer_id`, 
  Tests.test_question_count_total AS `Tests__test_question_count_total`, 
  Tests.test_question_count_mature AS `Tests__test_question_count_mature`, 
  Tests.test_session_count_total AS `Tests__test_session_count_total`, 
  Tests.test_session_count_mature AS `Tests__test_session_count_mature`, 
  Tests.photo AS `Tests__photo`, 
  Tests.photo_dir AS `Tests__photo_dir`, 
  Tests.photo_size AS `Tests__photo_size`, 
  Tests.view_count AS `Tests__view_count`, 
  Tests.published AS `Tests__published`, 
  Tests.mature AS `Tests__mature`, 
  Tests.flagged AS `Tests__flagged`, 
  Tests.deleted AS `Tests__deleted`, 
  Tests.created AS `Tests__created`, 
  Tests.modified AS `Tests__modified`, 
  Users.id AS `Users__id`, 
  Users.first_name AS `Users__first_name`, 
  Users.last_name AS `Users__last_name`, 
  Users.username AS `Users__username`, 
  Users.email AS `Users__email`, 
  Users.password AS `Users__password`, 
  Users.token AS `Users__token`, 
  Users.photo AS `Users__photo`, 
  Users.photo_dir AS `Users__photo_dir`, 
  Users.photo_size AS `Users__photo_size`, 
  Users.bio AS `Users__bio`, 
  Users.is_admin AS `Users__is_admin`, 
  Users.created AS `Users__created`, 
  Users.modified AS `Users__modified`, 
  Maintainers.id AS `Maintainers__id`, 
  Maintainers.first_name AS `Maintainers__first_name`, 
  Maintainers.last_name AS `Maintainers__last_name`, 
  Maintainers.username AS `Maintainers__username`, 
  Maintainers.email AS `Maintainers__email`, 
  Maintainers.password AS `Maintainers__password`, 
  Maintainers.token AS `Maintainers__token`, 
  Maintainers.photo AS `Maintainers__photo`, 
  Maintainers.photo_dir AS `Maintainers__photo_dir`, 
  Maintainers.photo_size AS `Maintainers__photo_size`, 
  Maintainers.bio AS `Maintainers__bio`, 
  Maintainers.is_admin AS `Maintainers__is_admin`, 
  Maintainers.created AS `Maintainers__created`, 
  Maintainers.modified AS `Maintainers__modified` 
FROM 
  tests Tests 
  INNER JOIN users Users ON Users.id = (Tests.user_id) 
  LEFT JOIN users Maintainers ON Maintainers.id = (Tests.maintainer_id) 
WHERE 
  (
    Tests.slug = 'what-word-are-youquestion' 
    AND Tests.deleted IS NULL
  ) 
LIMIT 
  1