[SOLVED] Admin List View doesn't display fields from joined tables

[JuanTomas]

(Using PS 1.6.1.2)

 

I've developed a module which creates Job Cards for tracking manufacture of ordered items.  Here's a snippet from from the admin controller for the module (method __construct()), showing how I join and filter on a couple of tables other than the job_card table:

        $this->_join .= ' JOIN `'._DB_PREFIX_.'orders` AS orders ON (a.`id_order` = orders.`id_order`)
                          JOIN `'._DB_PREFIX_.'job_card_tracking` AS jct ON (a.`id_job_card` = jct.`id_job_card`)' ;

        $this->fields_list = array(
            'id_job_card' => array(
                'title' => 'Job Card ID',
                'width' => 50,
                'filter_key' => 'id_job_card',
            ),
            'id_order' => array(
                'title' => 'Order ID',
                'width' => 50,
                'filter_key' => 'id_order',
            ),
            'job_card_reference' => array(
                'title' => $this->module->l('Job Card Reference'),
                'width' => 130,
                'filter_key' => 'job_card_reference',
            ),
            'order_reference' => array(
                'title' => $this->module->l('Order Reference'),
                'width' => 130,
                'filter_key' => 'orders!reference',
            ),
            'department' => array(
                'title' => $this->module->l('Department'),
                'width' => 130,
                'filter_key' => 'jct!department',
            ),
            'delivery_date' => array(
                'title' => $this->module->l('Delivery Date'),
                'width' => 130,
                'filter_key' => 'delivery_date'
            )
        );

As you can see, I'm grabbing orders.reference and job_card_tracking.department.  The list view shows these fields in the search bar.  All search, filtering, and ordering works correctly.

 

But the columns which correspond to the joined tables do not show the contents of those fields, just the double-hyphen placeholder --.   I know for sure those fields aren't empty in the DB, and PS is using the data from those fields to filter and order results.  Why are the contents of these columns not displayed in list view?

 

Edited October 13, 2016 by JuanTomas (see edit history)

[razaro]

Have you added 

jct.department

to select statement ?

[JuanTomas]

Thanks razaro, that was the problem!  Here's the updated code with two corrections commented:

        // ADDED THIS LINE SO FIELDS WILL BE SELECTED
        $this->_select .= ' orders.reference, jct.department ' ;
        $this->_join .= ' JOIN `'._DB_PREFIX_.'orders` AS orders ON (a.`id_order` = orders.`id_order`)
                          JOIN `'._DB_PREFIX_.'job_card_tracking` AS jct ON (a.`id_job_card` = jct.`id_job_card`)' ;

        $this->fields_list = array(
			'id_job_card' => array(
				'title' => 'Job Card ID',
				'width' => 50,
				'filter_key' => 'id_job_card',
			),
			'id_order' => array(
				'title' => 'Order ID',
				'width' => 50,
				'filter_key' => 'id_order',
			),
			'job_card_reference' => array(
				'title' => $this->module->l('Job Card Reference'),
				'width' => 130,
				'filter_key' => 'job_card_reference',
			),
                        // CHANGED THIS ARRAY KEY TO reference SO IT CORRESPONDS TO THE FIELD NAME IN DB
			'reference' => array(
				'title' => $this->module->l('Order Reference'),
				'width' => 130,
				'filter_key' => 'orders!reference',
			),
			'department' => array(
				'title' => $this->module->l('Department'),
				'width' => 130,
				'filter_key' => 'jct!department',
			),
            'delivery_date' => array(
                'title' => $this->module->l('Delivery Date'),
				'width' => 130,
                'filter_key' => 'delivery_date'
            )
		);

Edited October 13, 2016 by JuanTomas (see edit history)