php - 如何在 joomla 中编写此查询

Question

我想获取子类别的所有父类别标题所以在 mysql 中我可以用这段代码来做到这一点

SELECT T2.id, T2.title
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM `up8te_categories` WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 8, @l := 0) vars,
        `up8te_categories` h
    WHERE @r <> 0) T1
JOIN `up8te_categories` T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

但在 joomla 中我们有查询的 API我想知道如何在 joomla 中使用它？

我知道是这样的:

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('title')))
      ->from(......??????......)
      ->join('INNER', $db->quoteName('#__categories', 'T2') . ' ON (T1._id  =  T2.id)')
      ->order('T1.lvl DESC');
$db->setQuery($query);
$rows = $db->loadObjectList();

但是我不知道如何设置从我想再次选择时..

我能做什么？

亲切的问候。

Answer 1

我更喜欢将 $query 作为字符串使用，如下所示:

    $db = JFactory::getDbo();        
    $query = 'SELECT T2.id, T2.title
             FROM (
                 SELECT
                     @r AS _id,
                     (SELECT @r := parent_id FROM #__categories WHERE id = _id) AS parent_id,
                     @l := @l + 1 AS lvl
                 FROM
                     (SELECT @r := 8, @l := 0) vars,
                     #__categories h
                 WHERE @r <> 0) T1
             JOIN #__categories T2
             ON T1._id = T2.id
             ORDER BY T1.lvl DESC';

    $db->setQuery($query);