Union of two tables as source?

[fknorn]

Hi

Is there any way to use this to query data that is the result of a UNION of two different tables?

Thanks!

[fknorn]

Any suggestion here, thanks?

[poetrasapoetra]

1. Use CI4 Builder

From CI4 documentation $builder->union you can write table union builder

$builder = $db->table('users')->select('id, name')->limit(10);
$union   = $db->table('groups')->select('id, name');
$builder->union($union);

Then use $buillder->fromSubquery

$newBuilder =  $db->newQuery()->fromSubquery($builder 't'); // $builder is from union part
$newBuilder->select("id, name");  // required

// then the rest of DataTable init
$datatable = DataTable::of($newBuilder);

Don't forget to use select, in normal builder get result it will produce correct sql query, but if used with this library it will throw an DatabaseException.
Also do not use * in select, it will throw an error in

CodeIgniter4-DataTables/src/DataTableColumnDefs.php

Lines 275 to 286 in cdd5c2b

	if(strpos($selectParsed['base_expr'], '*') !== FALSE)
	{
	$fieldData = $builder->db()->getFieldData($selectParsed['no_quotes']['parts'][0]);
	foreach ($fieldData as $field)
	{
	$key = $selectParsed['no_quotes']['parts'][0].'.'.$field->name;
	$alias = $field->name;
	}
	}

2. Use raw query with Builder

$query = "SELECT * FROM ...."; // your raw query, check security yourself
$builder = $db->newQuery()->from("{$query} AS t");
$builder->select("col1, col2"); // also dont forget this

// then the rest of DataTable init
$datatable = DataTable::of($builder);