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

Question

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

我能够选择字段并添加适当的 where 子句。但是，我在如何生成作为 JOIN 语句一部分的 ON 子句方面受到挑战。

这是我到目前为止所做的。首先，我像这样定义了 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'

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

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

Answer 1

创建这些表是完全没有必要的，只要你使用外键使用InnoDB存储引擎，mysql就可以为你处理所有这些。

list all tables on current database

SHOW TABLES;

get list of columns on a given table

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

get list of relationships between tables

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;