Custom controller and table join

[matiash]

Hello,

 

I wrote my custom controller in backoffice which extends AdminController. But when I want to render table list with fields I would like to join one more table. I need to display some additional fields from other table. Is it possible?

[jimmychang]

Alright, so you posted this nearly a month ago, but I'm putting the answer here so people can see it - yes, you can join your list with fields pulled from another table.

 

AdminController has a number of protected variables relating to the SQL queries it runs to generate lists:

 

$this->_select
$this->_join
$this->_where
$this->_group
$this->_having

 

These string variables are ultimately appended together, in order, to generate the SQL that the controller runs. So let's say you're generating a list of User models, and you want to pull in a field from Subscription for each User. Assuming each User is associated with one Subscription, and that each User entry has a corresponding id_subscription column, you'd want to append to $this->_join and $this->_select in your controller as follows:

 

// Assuming you're only pulling one field (plan_name) from the Subscription table:
$this->_select .= 's.plan_name ';
$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'subscription` s ON s.`id_subscription` = a.`id_subscription`';

 

Two notes: 'a' is what your controller's table is automatically aliased to; in this case, it would be the ps_user table. Secondly, if you run into SQL syntax errors, take a look at the query and make sure you don't have any errant commas or improper spacing in your SELECT statement, especially if you're appending to the end of the _select string.

 

You can look at AdminProductsController.php for more examples of how those protected SQL strings are used.