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
	// Proceed if table eixists, and at least one custom field is seleced for showing
	if ($this->customFieldsTableExists && !empty($this->customFieldsTableColumnsForQuery))
	{
		// Since in actual query columns are used as tablealias.columnname,
		// Lets build such array
		$customFieldColumns = array();

		foreach ($this->customFieldsTableColumnsForQuery as $cfc)
		{
			$customFieldColumns[] = $this->customFieldsTableAlias . '.' . $cfc;
		}

		// If at least one custom field is seleced for showing, select record_id column as well
		if (!empty($customFieldColumns))
		{
			$customFieldColumns[] = $this->customFieldsTableAlias . '.record_id';

			$query->select($db->quoteName($customFieldColumns));
			$query->join(
				'LEFT', $db->quoteName($this->customFieldsTable, $this->customFieldsTableAlias) .
				' ON ' . $db->quoteName($this->customFieldsTableAlias . '.record_id') . ' = ' . $db->quoteName('lt.user_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. Don't forget to use the correct column to join on eg. in above case lt.user_id

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.