php - get_records_sql 在 moodle 中使用 inner join 只返回一个结果

Question

我使用以下代码在 MOODLE 中获取类别和相应的类(class)详细信息

global $DB;

$catlist = $DB->get_records_sql(
'SELECT ca.id,ca.name,ca.coursecount, c.id as course_id , c.category,
c.fullname, c.shortname , c.summary , c.format, c.startdate , c.timecreated 
FROM {course_categories} as ca inner join {course} as c on ca.id = c.category 
WHERE ca.parent > ? and ca.visible = ? and c.visible = ? ', array('0','1','1'));

echo "<pre>";print_r($catlist); echo "</pre>";  

当我执行这个查询时，我得到的结果数组只有一个结果行，而在 mysql 数据库中执行相同的 sql 直接返回许多行。

表 course_categories 有 2 个类别 'account' 和 'business' 具有使用 visible =1 的事件条件并且还包含父类别。表格类(class)有 4 个类(class)，每个类(class) 2 个与类别“帐户”和“业务”相关

结果是这样的:

Array
(
    [1] => stdClass Object
        (
            [id] => 1
            [name] => Accounts
            [coursecount] => 2
            [course_id] => 4
            [category] => 1
            [fullname] => Finance
            [shortname] => Finance
            [summary] => 

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

            [format] => weeks
            [startdate] => 1461695400
            [timecreated] => 1461653620
        )

    [2] => stdClass Object
        (
            [id] => 2
            [name] => Business
            [coursecount] => 2
            [course_id] => 5
            [category] => 2
            [fullname] => Animal Health Honours (BSc(Hons))
            [shortname] => Animal Health Honours
            [summary] => 

Sl/NO, Course Name, Duration. HARDWARE & NETWORKING. 1, Advacnce Diploma in Computer Hardware Maintanance & Networking(ADCHMN), 12 Months.

            [format] => weeks
            [startdate] => 1461781800
            [timecreated] => 1461760598
        )

)

任何人都可以帮助解决这个问题。

Answer 1

在 Moodle 中调用任何 get_records* 函数的结果作为一个数组返回，该数组由结果中的第一个字段索引(这非常有用，例如，如果您获得一个用户记录数组，然后想跳转直接到一条记录，基于用户 ID)。

当您的查询返回类别 ID 作为第一个字段时，每个类别只会返回 1 个结果(如果您将 debugging 设置为开发人员，您将收到有关此的警告)。

要解决此问题，请使用不同的字段作为返回的第一个值(在本例中，c.id 是一个不错的选择)，或者改用其中一个 get_recordset* 函数并使用 foreach 遍历结果。