Function failing and no error was given

175 Posts

stalemate resolution associate Reply #11, 13 years, 1 month ago

I'm no XPDO expert, but when I see the SQL, I can definitely see a problem: you are selecting a bunch of fields, but you are only grouping one field, namely Offers.sorteer. From a SQL perspective, as soon as you include a GROUP BY clause, all fields being selected must either be included in the GROUP BY or have an aggregate function.

Try removing this line:

$select->groupby('Offers.sorteer');

and see what happens.

Other than using aggregates (like SUM, AVG, MIN, MAX, etc.), GROUP BY is also used to get a distinct list of values, which has better performance on certain databases. Since you're selecting what is most likely the key field for the table (i.e. ORDER.id), you don't need the distinct values.

Hope this helps.

265 Posts

thapriest86 Reply #12, 13 years, 1 month ago

Thanks Bob, that clarifies a lot.

And thanks stalemate resolution associate, that shouldn't have been a groupby but a sortby. (that's what you get when you can't get your head around things ;-) )

I've changed that, but i'm still not getting "it".

When i look at the sql that comes out, it is selecting on the Object name.
For instance

Accomodations.intro should translate to custom_accomodations.intro (custom_accomodations is what the table in the dtatabase is called.

175 Posts

stalemate resolution associate Reply #13, 13 years, 1 month ago

With that error, it seems rather odd that they aren't telling you something is wrong, but rather that something should.

Just as a complete guess, are the class file and the underlying table in sync?

265 Posts

thapriest86 Reply #14, 13 years, 1 month ago

As far as i know they are and should be in sync, i'm using them all over the place in simpler queries.

For instance i'm using this in the same class file, wich works normally:

function getHotOffers(){
	global $modx;
		$select = $modx->newQuery('Countries');
		//$select->innerJoin('Countries', 'Accomodations', 'Countries.id = Accomodations.land_id');
		$select->where(array(
			'showInMenu' => '1',
			));
			
			$select->limit('11');
	$select->sortby('naam',  'ASC');
		
		$result = $modx->getIterator('Countries', $select);
		
		return $result;
		}

Update, thought it might be something with the $select->select(array), so i tested that for above example(wanted to do that anyways to speed up things, does that speed up btw? ) that works, like i expected.

	function getHotOffers(){
	global $modx;
		$select = $modx->newQuery('Countries');
		//$select->innerJoin('Countries', 'Accomodations', 'Countries.id = Accomodations.land_id');
		$select->where(array(
			'showInMenu' => '1',
			));
			
		$select->limit('11');
		$select->sortby('naam',  'ASC');
		$select->select(array(
			'id',
			'naam',
			'vluchtprijs',
			));
			
		$result = $modx->getCollection('Countries', $select);
		
		
		return $result;
		}

[ed. note: thapriest86 last edited this post 13 years, 1 month ago.]

265 Posts

thapriest86 Reply #15, 13 years, 1 month ago

Got it!
(for future seekers see below)
Turns out there were 2 things still wrong with the query wich i overlooked constantly.

First:

     $select->leftJoin('Offers','Accomodations', 'Offers.idAccomodation = Accomodations.id');
           $select->leftJoin('Countries', 'Accomodations', 'countries.id = accomodations.land_id');

That's just wrong ( see: http://rtfm.modx.com/xpdo/1.x/class-reference/xpdoquery/xpdoquery.leftjoin )
Changed that to:

     $select->leftJoin('Accomodations','Accomodations', 'Offers.idAccomodation = Accomodations.id');
           $select->leftJoin('Countries', 'Countries', 'countries.id = accomodations.land_id');

Secondly: (with this one i really feel stupid)

 $select->where(array(
            'active' => 1,
             ));

That just doesn't work when the active column is not in the Offers table but in the Accomodations table.
Changed that to:

 $select->where(array(
            'Accomodations.active' => 1,
             ));

And now, it works the way i want.

Thank you all for bearing with me on this one, i, again, learned alot.

Cheers