php - 将 3 个数据库表连接在一起

Question

我的 Joomla 数据库中有三个表，我想要查询合并它们，然后按日期排序，但我不完全理解如何使用 join function in joomla .

Table 1: __content
id , created, title, state, catid
'1'  , '2010-01-01 00:00:00' , 'ContentTitle' , '1' , '23'
'2'  , '2014-03-01 00:00:00' , 'ContentTitle2' , '1' , '20'

Table 2: __webcasts
id, eventdate, title, state
'1'  , '2015-03-01' , 'WebcastTitle' , '1'

Table 3: __conferences
id, eventdate, title, state
'1'  , '2012-05-01' , 'ConferenceTitle' , '1'

我想从内容表中选择具有 catid 23 的所有文章，并与网络广播和 session 表结合起来，然后按日期对结果进行排序，以便我有一个如下表:

id, created-eventdate, title, state
'1' , '2015-03-01' , 'WebcastTitle' , '1'
'1' , '2012-05-01' , 'ConferenceTitle' , '1'
'1' , '2010-01-01 00:00:00' , 'ContentTitle' , '1' 

(我认为)让事情变得更加复杂的是表之间日期格式的差异。我如何使用 Jdatabase 形成一个查询来组合和查询我的 3 个表？

Answer 1

使用下面的查询你会得到结果

$q2
    ->select('id , event_date As date, title, status')
    ->from('webcasts')
    ;
$q1
    ->select('id , event_date As date, title, status')
    ->from('conferences')
    ;
$query
    ->select('id , created_date As date, title, status')
    ->from('content')
    ->where('catid = 23')
    ->union($q1)
    ->union($q2)
    ->order('id ASC, date desc')
    ;
$result = $db->setQuery($query)->loadObjectList();

下面是获取结果的原始 mysql 查询

SELECT a.id AS id, a.created_date AS date, a.title, a.state FROM (SELECT id, created_date, title, state FROM content WHERE catid = 23) AS a
UNION ALL
SELECT id, event_date As date, title, state FROM webcasts
UNION ALL
SELECT id, event_date As date, title, state FROM conferences
order by id asc, date desc