Jump to content

Custom controller and table join


matiash
 Share

Recommended Posts

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?

Share this post


Link to post
Share on other sites

  • 4 weeks later...

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More