KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

MySQL and MSSQL datasource to separate datastores and query exclude or whereNotIn #552

Open Andrew Borell opened this topic on on Dec 7, 2018 - 1 comments

Andrew Borell commented on Dec 7, 2018

Title says it all. the gist is I am trying to run a report to gather a list from one database and compare to another so I may identify errors in transactions.
e.g.) ` require_once ('\libs\koolreport\autoload.php'); use \koolreport\querybuilder\DB;

class missing_bills extends \koolreport\KoolReport {

function settings(){
	return array(
			"dataSources"=>array(
				"my_ds"=>array(
					'host' => '...',
					'username' => '...',
					'password' => '...',
					'dbname' => '...',
					'class' => '\koolreport\datasources\MySQLDataSource' 
				),
				"ms_ds"=>array(
					'host' => '...',
					'username' => '...',
					'password' => '...',
					'dbname' => '...',
					'class' => "\koolreport\datasources\SQLSRVDataSource" 
				),
			),
		);
	}

	function setup() {
		$this->src('ms_ds')->query(
			"select field1, field2, field3 from table where field3 = 'xyz'";
			)->pipe($this->dataStore("ms_ds_data"));

		$this->src('my_ds')->query(
			"select field1 from table where field3 = 'xyz'"
			)->pipe($this->dataStore("my_ds_data"));
			
		}
}

	
I looked at using ->data() on the pipe for my_ds_data but it doesnt flatten the array so I cannot see any way this could be used with whereNotIn. 

I could array_flip and flatten the array with a simple php function but I wanted to use built-in methods if they exist. Is it possible to select records that do not exist in another dataStore?
Andrew Borell commented on Dec 8, 2018

until I can get some official response on why the Custom and Filter processes simply did not display any data to the DataTable, my solution is to process the array with a php foreach.

In my code above for the setup the columns should have different names, so assume field1_a and field1_b for my_ds and ms_ds respectively.

I could not do a leftJoin-> in the class, not just in the setup, but in any custom function or otherwise in the class because of an error on line 1166 invalid argument, so the leftJoin is done in my view, as is the code that follows

$joins = $report->dataStore("my_ds")->leftJoin($report->dataStore("ms_ds"),array("field1_a"=>"field1_b"));

$d = array();
	foreach( $joins as $join){
		if($join['field1_a']!=$join['field1_b']){
			array_push($d,$join);
		}
	}
		
DataTables::create(array(
	"dataSource"=>$report->dataStore($d)
		,"columns"=>array(
			"field1_a"=>array(
				"label"=>"Friendly Name Here",
				"type"=>"string",
				)
...

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None