need to create an export of 2 custom tables with join

Hoping someone can point me right here, have tried many of the old threads and postings (where has all your content gone shawnwilkerson!)
but still not getting anything to work.

Have a snippet to export a custom table to a csv file that does work...

<?php
$path = MODX_CORE_PATH . 'components/vouchers/';
$result = $modx->addPackage('vouchers',$path . 'model/','modx_');
if (! $result) {
return 'failed to add package';
} else {

}
$filename ="excelreport.csv";
$vouchers = $modx->getCollection('RevoVouchers');
$last = "\n";
foreach($vouchers as $voucher) {
$output .= implode(',', str_replace("\r\n","",$voucher->toArray())) . PHP_EOL;
}
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
header("Content-Type: application/force-download");
header("Cache-Control: post-check=0, pre-check=0", false);
return $output;

This gets a csv of the vouchers table, and if I replace vouchers with orders gets a csv of the orders table

But...

I need an export that joins 2 the tables, RevoVouchers and RevoVouchersOrders, where RevoVouchers.order = RevoVouchersOrders.id
and exports all the fields, or at least some of them based on the join

RevoVouchersOrders has id surname (for example)
RevoVouchers has vouchernumber value order

Have tried a few methods, trying to relate other posts to mine, but got nothing working -

Really hoping someone can give me some/any pointers here

(some other time I need to try and add the column headings to the file, but that's for another day!)

You should be able to do a join on the two tables with an xPDO query.

The docs are a little sketchy, but this might get you started:

http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery

If the two objects are related (it looks like they are), you may be able to skip the join and use getCollectionGraph() instead of getCollection().

http://rtfm.modx.com/xpdo/2.x/class-reference/xpdo/xpdo.getcollectiongraph

After reading through the docs I thought I might get away with:

$query = $modx->newQuery('Vouchers');
$query->innerJoin('order','id');
$vouchers = $modx->getCollection('VouchersOrders',$query);

but this get a
Call to a member function newQuery() on a non-object

so not sure I'm on the right track with that

That says that the $modx object doesn't exist at that point. It sounds like you're doing this outside of MODX.

If so, see this: http://bobsguides.com/blog.html/2013/07/13/using-modx-outside-of-modx/

Hi Bob

Sorry, now I've re-traced it I realise I was referencing an earlier snippet failure (have been trying this for days!)

So, using the code

$query = $modx->newQuery('Vouchers');
$query->innerJoin('order','id');
$vouchers = $modx->getCollection('VouchersOrders',$query);

$filename ="excelreport.csv";
$last = "\n";

foreach($vouchers as $voucher) {
$output .= implode(',', str_replace("\r\n","",$voucher->toArray())) . PHP_EOL;
}
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
header("Content-Type: application/force-download");
header("Cache-Control: post-check=0, pre-check=0", false);
return $output;

DID get as far as creating a csv file, but in the file I just get

<b>Warning</b>: Invalid argument supplied for foreach()


Have I missed something there, or is there a way to simply print the results to the page to check i have the data?

It looks like your getCollection() call is failing to find anything. If it's a snippet, you can just echo whatever you like.

You can see the actual SQL of the query in the error log with this:

Adding that error logging gives me this in the error log

[2013-08-20 20:58:53] (ERROR @ /index.php) Could not load class: Vouchers from mysql.vouchers.
[2013-08-20 20:58:53] (ERROR @ /index.php) Could not load class: order from mysql.order.
[2013-08-20 20:58:53] (ERROR @ /index.php) No class specified for loadClass
[2013-08-20 20:58:53] (ERROR @ /index.php) ::getSelectColumns() is not a valid static method.
[2013-08-20 20:58:53] (ERROR @ /index.php) SELECT `` FROM
[2013-08-20 20:58:53] (ERROR @ /index.php) Could not load class: VouchersOrders from mysql.vouchersorders.
[2013-08-20 20:58:53] (ERROR @ /index.php) VouchersOrders::loadCollection() is not a valid static method.

I need any help available trying to understand that...

it looks like it fails from the start, yet the code in the first post works so I'm not sure on the difference

Are you calling addPackage() before doing the newQuery()?

You may also have to call loadClass() on each class before setting up the query.

This may help: http://bobsguides.com/blog.html/2013/05/25/understanding-addpackage(),-loadclass(),-and-getservice()/

OK, finally, finally, finally think I have this now

First problem was I was convinced the Class names were right but they weren't - as someone said always double check
core/components/mycmp/model/mycmp/vouchers.mysql.schema.xml
just to make sure they are listed in there.

Next, I don't know if the example on http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery/xpdoquery.innerjoin is actually wrong (maybe someone could take a look), but after following this thread https://forums.modx.com/thread/?thread=77803 I realised there was no select statement so I re-jigged it and came up with this:

$query = $modx->newQuery('RevoVouchers');
$query -> select('RevoVouchers.*, RevoVouchersOrders.*');
$query -> innerJoin('RevoVouchersOrders','RevoVouchersOrders', array('RevoVouchersOrders.id = RevoVouchers.order'));
//debug $query -> prepare();
//debug return $query->toSql();
//debug $modx->log(MODX::LOG_LEVEL_ERROR, $query->toSql());
$vouchers = $modx->getCollection('RevoVouchers',$query);

Then I started to get some data from both tables. Yippee!!

In my case it also looks like then you can have as issue if you have field names the same in both tables (both have ID for example) so had to use an alias before I got the right number of rows
$query -> select(''RevoVouchers.id AS VoucherID, RevoVouchers.status AS VoucherStatus, RevoVouchersOrders.*');

Hoping this might help someone (or probably the future me)

I'm glad you got it sorted. Thanks for reporting back.

BTW, I don't think you need the select() since the default is to get all fields.