Hi
I have two custom tables with a defined relationship. In the first table, called Categories, I store categories for running competitions. In the second table, called Results, I store the acutal competitors. The field that links the tables together is called categoryid.
We have a voucher system combined with credit card payments. To sort out the compeitors who has completed their payments to the race I want to use this criteria:
WHERE `Results`.`voucher` != ’’ AND `Results`.`voucher` NOT LIKE ’INVALID’ OR `Results`.`paymentref` != ’’
To clarify, I want to retrieve the competitors who either has a voucher or a paymentref. I want to keep track of the people who tries to enter an invalid voucher and keeps track of them by enter INVALID in the voucher field.
To count all competitors I use this query:
$c = $modx->newQuery('Results');
$c->where(array(
'voucher:!=' => ''
));
$c->andCondition(array(
'voucher:!=' => 'INVALID'
));
$c->orCondition(array(
'paymentref:!=' => ''
));
$numEntrants = $modx->getCount('Results',$c);
This works and I get the correct results.
Now to my problem. When I want to iterate over all my categories to output a list over all competitors per category the above criteria doesn’t work. Here is my code:
$c = $modx->newQuery('Results');
$c->where(array(
'voucher:!=' => ''
));
$c->andCondition(array(
'voucher:!=' => 'INVALID'
));
$c->orCondition(array(
'paymentref:!=' => ''
));
$categories = $modx->getCollection('Categories');
foreach ($categories as $category) {
$competitors = $category->getMany('Entries',$c);
foreach ($entrants as $entrant) {
...
This code seems to destroy the relationsship in some way.
I have tried different solutions, for example this one, which doesn’t work either:
$c = $modx->newQuery('Results');
$c->where(array(
'voucher:!=' => '',
'AND:voucher:!=' => 'INVALID',
'OR:paymentref:!=' => '',
));
In this case only one of the conditions for the ticket field is used. The above solutions doesn’t work whit getCount either.
Can anyone see what I’m doing wrong?
Best regards
Kristoffer
