我目前正在为多个不同的对象存储addresses。所以有jobs、companies等addresses。
我希望能够获得所有addresses.*以及jobs.job_title和companies.company_name的联合，以便为每个addresses记录选择一个object_name。
地址.php (型号(
static $object_type = [
1 => 'Company',
2 => 'Job',
];
static public function getObjectTypes() {
return self::$object_type;
}
static public function getObjectTypeName($key) {
return self::$object_type[$key];
}
static public function getAddresses(){
$addresses = Address::select(
'addresses.*'
)->where('addresses.soft_deleted', 0);
$addresses->limit(20);
return $addresses->get();
}
addresses表的当前架构：
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| id | int(12) unsigned | NO | PRI | NULL | auto_increment |
| object_id | int(12) | YES | MUL | 0 | |
| object_type | tinyint(3) | YES | | 1 | |
| address | varchar(255) | YES | | NULL | |
| soft_deleted | tinyint(3) unsigned | YES | | 0 | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+-------------------+---------------------+------+-----+---------+----------------+
jobs表的当前架构：
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| id | int(12) unsigned | NO | PRI | NULL | auto_increment |
| company_id | int(12) | YES | | 0 | |
| job_title | varchar(99) | YES | | NULL | |
| soft_deleted | tinyint(3) unsigned | YES | | 0 | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL |
+-----------------+---------------------+------+-----+---------+----------------+
companies表的当前架构：
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| id | int(12) unsigned | NO | PRI | NULL | auto_increment |
| company_name | varchar(99) | YES | | NULL | |
| soft_deleted | tinyint(3) unsigned | YES | | 0 | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL |
+-----------------+---------------------+------+-----+---------+----------------+
我的问题是：我将如何创建联合并根据上述结构返回所需的数据？
这是我认为你所追求的SQLFiddle。 这是一个非常简化的架构，用于说明正在执行的操作：
addresses
id | objectId | objectType | address
1 | 1 | 1 | 4657
2 | 1 | 3 | 3465
3 | 2 | 4 | 4536
4 | 1 | 2 | 3098
5 | 2 | 1 | 5647
6 | 2 | 2 | 8989
7 | 1 | 4 | 4563
8 | 2 | 3 | 2345
companies
id | name
1 | 'Acme Anvils'
2 | 'McBarfles'
3 | 'Noke'
4 | 'Stab Mart'
jobs
id | companyId | title
1 | 4 | 'Fly Swatter'
2 | 3 | 'Palm Greaser'
3 | 2 | 'McBarfle Burger Tender'
4 | 1 | 'Shill'
假设 objectType 1 = Company 且 objectType 2 = Job ：
SELECT
'Company' AS objectType,
c.name,
a.address
FROM addresses a
INNER JOIN companies c
ON a.objectId = c.id
WHERE a.objectType = 1
UNION ALL
SELECT
'Job' AS objectType,
j.title,
a.address
FROM addresses a
INNER JOIN jobs j
ON a.objectId = j.id
WHERE a.objectType = 2
这会根据地址表中的 objectType 将连接分解为多个部分，然后将结果UNION ALL在一起，以获得地址和对象名称的最终列表：
objectType | name | address
Company | Acme Anvils | 4657
Company | Noke | 3465
Company | McBarfles | 3098
Company | Stab Mart | 4563
Job | Shill | 4536
Job | Fly Swatter | 5647
Job | Palm Greaser | 8989
Job | McBarfle Burger Tender | 2345
请注意，您必须手动添加要为每个可能的 objectType 值UNION ALL的其他查询。