How to add user fields to any report?

TJReport has plugins that let you index the custom fields data created using Joomla's com_fields component.

Below are steps for adding those user fields data columns into an existing report. Eg: For a plugin named mytjreportplugin, open plugin entry file mytjreportplugin.php

Steps

1. Setup custom fields columns
2. Setup custom fields filters
3. Change getListQuery to add join on the customFieldsTable

Step 1: Setup custom fields columns

Change constructor from

public function __construct($config = array())
{			
	$this->columns = array(
		// Columns setup here
	);
	
	parent::__construct($config);
}

to below

public function __construct($config = array())
{
	// Joomla fields integration - Define custom fields table, alias
	$this->customFieldsTable      = '#__tjreports_user_fields';
	$this->customFieldsTableAlias = 'tuf';
	$this->customFieldsTableExists = $this->tableExists();
					
	$this->columns = array(
		// Columns setup here
	);

	// Joomla fields integration - Call parent function to set custom fields columns
	$this->setCustomFieldsColumns();
	
	parent::__construct($config);
}

In step 1, we define

• customFieldsTable,
• customFieldsTableAlias,
• we set if customFieldsTableExists

We also call the parent method setCustomFieldsColumns() which sets up columns from custom fields to be displayed on the report.

Step 2: Setup custom fields filters

Change code from

public function displayFilters()
{
	// Set filters code here
	$dispFilters = array(
		array(
			// Set filters code here
		),
		array(
			// Set filters code here
		)
	);
	
	return $dispFilters;
}

to

public function displayFilters()
{
	// Set filters code here
	$dispFilters = array(
		array(
			// Set filters code here
		),
		array(
			// Set filters code here
		)
	);

	// Joomla fields integration - Call parent function to set filters for custom fields
	$this->setCustomFieldsDisplayFilters($dispFilters);
	
	return $dispFilters;
}

In step 2, we call the parent method setCustomFieldsDisplayFilters(), which sets up filters for columns from custom fields to be displayed on the report.

Step 3: Change getListQuery to add join on the customFieldsTable

Change code from

protected function getListQuery()
{
	$db    = $this->_db;
	$query = parent::getListQuery();

	$query->select('c.title, a.params,a.element_url,a.parent_id');
	$query->from('`#__tjlms_activities` AS a');
	
	// Query code goes here .....

	return $query;
}

to

protected function getListQuery()
{
	$db    = $this->_db;
	$query = parent::getListQuery();

	$query->select('c.title, a.params,a.element_url,a.parent_id');
	$query->from('`#__tjlms_activities` AS a');
	
	// Query code goes here .....

	// Joomla fields integration - Get custom fields data
	if ($this->customFieldsTableExists)
	{
		$query->select(array($this->customFieldsTableAlias . '.*'));
		$query->join(
			'LEFT', $db->quoteName($this->customFieldsTable, $this->customFieldsTableAlias) .
			' ON ' . $db->quoteName($this->customFieldsTableAlias . '.record_id') . ' = ' . $db->quoteName('a.actor_id')
		);	
	}
	
	return $query;
}

In step 3, we do query join on the custom fields table's record_id column and with the other DB table plugin mainly runs the report on.

Conclusion

That's it, with step 1 - 3, you will be able to easily add columns, filters, sorting on the columns form the Joomla user's custom fields.