如何使用PHP确定动态查询的ON子句

我正在尝试编写一个脚本，允许用户选择要从数据库中的不同列/表显示的字段列表。这个脚本需要能够生成完整的查询并执行它。

我能够选择字段并添加适当的where子句。然而，我正在挑战如何生成ON子句，这是JOIN语句的一部分。

以下是我到目前为止所做的。首先，我像这样定义了3个表

-- list of all tables available in the database
CREATE TABLE `entity_objects` (
  `object_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `object_name` varchar(60) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `object_description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`object_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- list of all tables available in the database
CREATE TABLE `entity_definitions` (
  `entity_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `display_name` varchar(255) NOT NULL,
  `entity_key` varchar(60) NOT NULL,
  `entity_type` enum('lookup','Integer','text','datetime','date') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `object_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `object_id` (`object_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
-- a list of the fields that are related to each other. For example entity 12 is a foreign key to entity 11.
CREATE TABLE `entity_relations` (
  `relation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `entity_a` int(11) unsigned NOT NULL,
  `entity_b` int(11) unsigned NOT NULL,
  `relation_type` enum('1:1','1:N') NOT NULL DEFAULT '1:1',
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `entity_a` (`entity_a`,`entity_b`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

要获得可用关系的列表，我运行这个查询

SELECT 
   CONCAT(oa.object_name, '.', ta.entity_key) AS entityA
,  CONCAT(ob.object_name, '.', tb.entity_key) AS entityB
FROM entity_relations as r
INNER JOIN entity_definitions AS ta ON ta.entity_id = r.entity_a
INNER JOIN entity_definitions AS tb ON tb.entity_id = r.entity_b
INNER JOIN entity_objects AS oa ON oa.object_id = ta.object_id
INNER JOIN entity_objects AS ob ON ob.object_id = tb.object_id

我很难弄清楚如何生成查询的JOIN语句。我能够生成SELECT .....和WHERE...，但需要帮助尝试生成查询的ON....部分。

我的最后一个查询应该看起来像这样

SELECT 
  accounts.account_name
, accounts.industry_id
, accounts.primary_number_id
, accounts.person_id
, industries.industry_id
, industries.name
, contact_personal.first_name
, contact_personal.person_id
, account_phone_number.number_id
FROM accounts 
LEFT JOIN industries ON industries.industry_id = accounts.industry_id
LEFT JOIN contact_personal ON contact_personal.person_id = accounts.person_id
LEFT JOIN account_phone_number ON account_phone_number.number_id = accounts.primary_number_id
WHERE industries.name = 'Marketing'

我用MySQL代码创建了一个SQL Fiddle。

如何正确定义连接语句的ON子句?

SHOW TABLES;

SELECT
    *
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = :schema
    AND TABLE_NAME = :table;

SELECT 
    *
FROM
    information_schema.TABLE_CONSTRAINTS tc
INNER JOIN
    information_schema.INNODB_SYS_FOREIGN isf ON
        isf.ID = concat(tc.CONSTRAINT_SCHEMA, '/', tc.CONSTRAINT_NAME)
INNER JOIN
    information_schema.INNODB_SYS_FOREIGN_COLS isfc ON
        isfc.ID = isf.ID
WHERE
    tc.CONSTRAINT_SCHEMA = :schema
    AND tc.TABLE_NAME = :table;