Symfony 3 - [KNP Pagination and sorting] Sorting for native query issue

Question

In my application I am using two databases, One is application database and another one is used to fetch data for the application [Using normal sql statements to fetch data]

I want to display 2nd database's data in my application, everything is working fine, even pagination but I don't know how to implement sorting. Using Knp-pagination bundle.

Pagination and sorting are working fine for the application database's data.

Here is the code

Controller

$db = $this->getDoctrine()->getConnection('2ndDatabase');
        $sql = "select * from table1, table2 where somevariable_id = ? and table1.id = table2.id and rownum<=50";
        $statement = $db->prepare($sql);
        $statement->bindParam('1', $somevariableidvalue);
        $statement->execute();
        $data = $statement->fetchall();

    /**
     * @var $paginator \Knp\Component\Pager\Paginator
     */
    $paginator = $this->get('knp_paginator');
    $result = $paginator->paginate(
        $data,
        $request->query->getInt('page',1),
        $request->query->getInt('limit', 10)
    )

View

            <th>{{ knp_pagination_sortable(data, 'ID', 'whattowritehere') }}</th>
            <th>{{ knp_pagination_sortable(data, 'Title', 'whattowritehere') }}</th>
            <th>{{ knp_pagination_sortable(data, 'Name', 'whattowritehere') }}</th>

In place of whattowritehere tried different things trying to access table, But I don't think I can access it without model/entity. I dont have any model/entity defined for 2nd Database, so finding it hard to create a entity manager to make sql into dql statement.

• Can I make this sql into dql (That will solve my problem! I don't have entity manager defined for this database, and there is no model/entity to the database)

• Is there a way to manipulate the $data since it working for pagination, I am thinking there might be a way for sorting as well

• Am I missing something ?

• Is it even possible to do ?

whattowritehere - tried column name (doesn't work, no error) - data.variablename (data release is array, expecting object) - array reference (same error)