SugarCRM: Read data from external table

I'm trying to display data from a non-Sugar table 开发者_开发知识库in a custom listview in SugarCRM. Currently I'm running an SQL query in a custom view.list.php file, but this is displaying the data below the list rather than replacing the default query in the listview.

How can I replace the default query in a listview with custom SQL?

You don't have to go through all of that.

When you create a custom module in ModuleBuilder. Deploy the package when edit the vardefs.php and Module_sugar.php and change the table_name to point to the new table. Then you don't actually have to write any special code and custom fields will work and complete the join for you.

class CustomModule_sugar extends SugarBean {
var $table_name = 'external_table';

I've managed to solve this by overriding the create_new_list_query() method in the module base class:

class CustomModule extends CustomModule_sugar {

    function CustomModule(){    
        parent::CustomModule_sugar();
    }


    // this is the method which constructs the default SQL query
    function create_new_list_query($order_by, $where, $filter, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect){ 
        // call the parent method to populate all params - will cause errors/problems elsewhere otherwise
        $ret_array = parent::create_new_list_query($order_by, $where,$filter,$params, $show_deleted,$join_type, $return_array,$parentbean, $singleSelect); 

        // override module sql with custom query
        // alias external field names so they match the fields set up in Sugar
        $ret_array['select'] = 'SELECT primary_id as id, date_added as date_entered, field_name as name, external_notes as notes';
        $ret_array['from'] = ' FROM external_table';

        // update these with appropriate SQL
        $ret_array['where'] = '';
        $ret_array['order_by'] = '';

        return $ret_array; 
    }
}

This method creates an SQL statement which is used in /includes/ListView/ListViewData.php. I've aliased the field names selected from the external table to match the names of the fields set up in Sugar (easier than creating or renaming every single field).