Result not as expected, when converting to toSql() and paste in mysql results are good

265 Posts

thapriest86 Reply #1, 12 years, 10 months ago

Hi Guys,

I'm having this strange problem that i can't seem to wrap my head around.

I'm trying to display two random trips wich can be combined.

The first trip (left) is always as it should be, however the second result should NOT be in the same country (Countries.id) and be of same quality(Accomodations.klasse | Star rating).

It keeps fetching trips in the same country and the star rating is never correct. 2.5 stars where they have 5 stars or vice versa.

Fun fact: when i do toSql() on the query that should fetch the second result, and i copy and paste that directly into mySQL, the results are exactly as i expected.

Does anyone have an idea?

Code:

$select = $modx->newQuery('Accomodations');
	$select->select(array(
		'Accomodations.id',
		'Accomodations.naam as Acconaam',
		'Accomodations.vanafPrijs',
		'Accomodations.thumbnail',
		'Accomodations.alias as accoAlias',
		'Accomodations.code',
		'Countries.id as cID', 
		'Countries.naam as landNaam',
		'Countries.alias',
		'Countries.vluchtprijs',
		'Accomodations.klasse as klas',
		));
		
		$select->innerJoin('Countries','Countries','Countries.id = Accomodations.land_id');
		$select->sortby('RAND()');
		
	
	$select->where(array(
		'Accomodations.active' => '1',
		'Countries.alias' => $land,
		
		));
		
	$select->limit('112');
	
$results = $modx->getCollection('Accomodations', $select);
	//echo $select->toSql();
	
	
	foreach($results as $result) 
		{		
		
		
		$cid[] = $result->cID;
		$Acconaam[] = $result->Acconaam;
		$Landnaam[] = $result->landNaam;
		$Accoalias[] = $result->accoAlias;
		$Landalias[] = $result->alias;
		$vluchtprijzen[] = $result->vluchtprijs;
		$vanafprijs[] = $result->vanafPrijs;
		$thumbnail[] = $result->thumbnail;
		$klasse[] = $result->klas;
	    $code[] = $result->code;
 		}
		
//Get star rating from 1st accomodation to match on even star appreciation. 
//$sterren = $klasse[0];

//$waardering = substr($sterren, 0 , 1);


$select2 = $modx->newQuery('Accomodations');
	$select2->select(array(
		'Accomodations.id',
		'Accomodations.naam as Acconaam',
		'Accomodations.vanafPrijs',
		'Accomodations.thumbnail',
		'Accomodations.alias as accoAlias',
		'Accomodations.code as Accocode',
		'Accomodations.klasse as sterWaarde',
		'Countries.id as landId', 
		'Countries.naam as landNaam',
		'Countries.alias',
		'Countries.vluchtprijs',
		
		));
		
		$select2->where(array(
		'Accomodations.active' => '1',
		'Countries.id:!=' => $cid[0],
		'Accomodations.klasse:=' => (round($klasse[0], 0)), 
		));
		
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '9'));
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '34'));
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '35'));
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '38'));
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '40'));
	//	$select2->orCondition(array( 'Accomodations.cID:!=' => '39'));
			
		$select2->innerJoin('Countries','Countries','Countries.id = Accomodations.land_id');
		$select2->sortby('RAND()');
		
	
		
	//$select2->limit('10');
	
$resultaten = $modx->getCollection('Accomodations', $select2);
	echo $select2->toSql();
	
	foreach($resultaten as $resultaat) 
		{		
		//randomize output
		$random = rand(0,9);
		
		$Acconaam2[] = $resultaat->Acconaam;
		$Landnaam2[] = $resultaat->landNaam;
		$Accoalias2[] = $resultaat->accoAlias;
		$Landalias2[] = $resultaat->alias;
		$vluchtprijzen2[] = $resultaat->vluchtprijs;
		$vanafprijs2[] = $resultaat->vanafPrijs;
		$thumbnail2[] = $resultaat->thumbnail;
		$code2[] = $resultaat->Accocode;
		$waardering2[] = $resultaat->sterWaarde;
 		}

	
$prijskaartje = $vanafprijs[0] + $vanafprijs2[1] +$vluchtprijzen[0] + 70 ;

$stars = str_replace(".", "", $klasse[0]) * 1.9;

$stars2 = str_replace(".", "", $waardering2[1]) * 1.9;



	//Accommodatie 1
echo '
<div class="combiwrapper">
		
		<div class="combicontent">
			<div class="photo">
				<img src="http://localhost/devabc/upload/accomodations/'.$thumbnail[0].'"  title="Eilandcombinatie '.$Landalias[0].', '.$Acconaam[0].'" alt="Eilandcombinatie '.$Landalias[0].', '.$Acconaam[0].'" width="200" height="100" />
			</div>
				<div class="infobar">
					<div class="country">
						<a href="vakantie/'.$Landalias[0].'/'.$Accoalias[0].'.html" title="Eilandhoppen '.$Landalias[0].', '.$Acconaam[0].'"><strong>'.$Landnaam[0].'</strong></a>
					</div>
						<div class="star-rating" style="width:'.round($stars, 0).'px;"></div>
				</div>
					<div class="accotitle">
						<a href="vakantie/'.$Landalias[0].'/'.$Accoalias[0].'.html" title="Combinatiereizen '.$Landalias[0].', '.$Acconaam[0].'"><strong>'.$Acconaam[0].'</strong></a>
					</div>
							<div class="zoover-rating">[[ZooverRij? &key=`'.$code[0].'`]] </div>
		</div>';
	
	//De ampersand
	echo '<img src="img/afbeeldingen/enteken.png" class="ampersand">';
			
			
	//Accommodatie 2		
	
echo '
		<div class="combicontent">
			<div class="photo">
				<img src="http://localhost/devabc/upload/accomodations/'.$thumbnail[1].'"  title="Eilandcombinatie '.$Landalias[1].', '.$Acconaam[1].'" alt="Eilandcombinatie '.$Landalias[1].', '.$Acconaam[1].'" width="200" height="100" />
			</div>
				<div class="infobar">
					<div class="country">
						<a href="vakantie/'.$Landalias[1].'/'.$Accoalias[1].'.html" title="Eilandhoppen '.$Landalias[1].', '.$Acconaam[1].'"><strong>'.$Landnaam[1].'</strong></a>
					</div>
						<div class="star-rating" style="width:'.round($stars2, 0).'px;">'.$waardering[1].'</div>
				</div>
					<div class="accotitle">
						<a href="vakantie/'.$Landalias[1].'/'.$Accoalias[1].'.html" title="Combinatiereizen '.$Landalias[1].', '.$Acconaam[1].'"><strong>'.$Acconaam[1].'</strong></a>
					</div>
			<div class="zoover-rating">[[ZooverRij? &key=`'.$code[1].'`]]</div>
		</div>	
		
</div>';

This is what comes out when you do:

echo $select2->toSql();

SELECT Accomodations.id, Accomodations.naam AS Acconaam, Accomodations.vanafPrijs, Accomodations.thumbnail, Accomodations.alias AS accoAlias, Accomodations.code AS Accocode, Accomodations.klasse AS sterWaarde, Countries.id AS landId, Countries.naam AS landNaam, Countries.alias, Countries.vluchtprijs
FROM  `custom_accomodations` AS  `Accomodations` 
JOIN  `custom_countries`  `Countries` ON Countries.id = Accomodations.land_id
WHERE (
`Accomodations`.`active` =1
AND  `Countries`.`id` !=11
AND  `Accomodations`.`klasse` =4
)
ORDER BY RAND( ) ASC

[ed. note: thapriest86 last edited this post 12 years, 10 months ago.]

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #2, 12 years, 10 months ago

        $select2->where(array(
        'Accomodations.active' => '1',
        'Countries.id:!=' => $cid[0],
        'Accomodations.klasse:=' => (round($klasse[0], 0)), 
        ));

When you are specifying specific match methods (the :!= part on Countries.id and := on Accomodations.klasse) you need to also specify the relation between the different conditions, like this:

        $select2->where(array(
        'Accomodations.active' => '1',
        'AND:Countries.id:!=' => $cid[0],
        'AND:Accomodations.klasse:=' => (round($klasse[0], 0)), 
        ));

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

265 Posts

thapriest86 Reply #3, 12 years, 10 months ago

Thanks Mark, this however returns the same result. sad

I thought btw that the AND condition was added automatically, it does when you convert to toSql().

MODX Staff
10,725 Posts

opengeek Reply #4, 12 years, 10 months ago

You can't do summary queries with joins like that and expect to use getCollection(). Why not just work with the result set directly or use the appropriate $modx->getCollectionGraph() or xPDOObject::getGraph() method to get the related data?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #5, 12 years, 10 months ago

Hmm Jason could you clarify what the problem is with the queries he's doing? I'm not seeing anything particularly crazy that I don't use myself regularly, but maybe I'm misunderstanding something too.

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

☆ A M B ☆
30 Posts

Argnist Reply #6, 12 years, 10 months ago

Sometimes $select->select(array( ... )); and $select->select($modx->getSelectColumns(...)) return different result but queries are same. Try to use getSelectColumns. May be it will help...

265 Posts

thapriest86 Reply #7, 12 years, 10 months ago

Sorry for my late reply, extremely busy ATM, so instead of occupying the tech seat i'm currently on the sales seat.

Thanks Argnist, but results are just the same. sad

@ Jason (& Mark) I'm also curious what it is that's causing the problem, i too believe this should work. Why i'm not using $modx->getCollectionGraph()? Simply haven't got the time (yet) to wrap my head around that, the short description in the docs is too little for my knowledge-level to understand it quickly.

MODX Staff
10,725 Posts

opengeek Reply #8, 12 years, 10 months ago

Because you are selecting specific fields with specific aliases, they will not be able to populate the objects properly unless you create field aliases for those column aliases you are selecting. Specifically, the ones with the same name.

You can use getSelectColumns to prefix all column names (see third parameter) with a specific prefix to avoid collisions, which is what xPDO does by default when querying multiple related tables via the *Graph() methods.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

265 Posts

thapriest86 Reply #9, 12 years, 10 months ago

Thanks Jason, that sounds logical.

Added question, are there any usage examples of GetSelectColumns() op getCollectionGraph other and more elaborate than the ones in the docs? (i know you guys are working on that section)

265 Posts

thapriest86 Reply #10, 12 years, 10 months ago

Okay, i read up on a lot of forum posts and this is what i came up with.

<?php
//Pak het huidige land op, indien geklikt op een land anders zet je hem op curacao
$landen = array("aruba", "bonaire", "curacao");
shuffle($landen);
if(!isset($_GET['country'])){ $land = ($landen[0]);} else{$land = $_GET['country'];}

$select = $modx->newQuery('Accomodations');
	$select->select($modx->getSelectColumns('Accomodations','Accomodations','',array(
		'id',
		'naam',
		'vanafPrijs',
		'thumbnail',
		'alias',
		'code',
		'klasse',
		)));
		
	$select->select($modx->getSelectColumns('Countries', 'Countries', 'c', array(
		'id',
		'naam',
		'alias',
		'vluchtprijs',
		)));
		
		$select->innerJoin('Countries','Countries','Countries.id = Accomodations.land_id');
		$select->sortby('RAND()');
		
	
	$select->where(array(
		'Accomodations.active' => '1',
		//'Countries.alias' => $land,
		
		));
		
	$select->limit('112');
	
$results = $modx->getCollection('Accomodations', $select);
	//echo $select->toSql();
	
	
	foreach($results as $result) 
		{		
		
		
		$cid[] = $result->cid;
		$Acconaam[] = $result->naam;
		$Landnaam[] = $result->cnaam;
		$Accoalias[] = $result->alias;
		$Landalias[] = $result->calias;
		$vluchtprijzen[] = $result->cvluchtprijs;
		$vanafprijs[] = $result->vanafPrijs;
		$thumbnail[] = $result->thumbnail;
		$klasse[] = $result->klasse;
	    $code[] = $result->code;
 		}
		
//Get star rating from 1st accomodation to match on even star appreciation. 
//$sterren = $klasse[0];

//$waardering = substr($sterren, 0 , 1);


$select2 = $modx->newQuery('Accomodations');
	$select2->select($modx->getSelectColumns('Accomodations','Accomodations','',array(
		'id',
		'naam',
		'vanafPrijs',
		'thumbnail',
		'alias',
		'code',
		'klasse',
				
		)));
		
		$select2->select($modx->getSelectColumns('Countries', 'Countries', 'c2', array(
		'id',
		'naam',
		'alias',
		'vluchtprijs',
		)));
		
		$select2->where(array(
		'Accomodations.active' => '1',
		'AND:Countries.id:!=' => $cid[0],
		'AND:Accomodations.klasse:=' => (round($klasse[0], 0)), 
		));

			
		$select2->innerJoin('Countries','Countries','Countries.id = Accomodations.land_id');
		$select2->sortby('RAND()');
		
	
		
	//$select2->limit('10');
	
$resultaten = $modx->getcollection('Accomodations', $select2);
	//echo $select2->toSql();
	
	foreach($resultaten as $resultaat) 
		{		
		//randomize output
		$random = rand(0,9);
		
		$Acconaam2[] = $resultaat->naam;
		$Landnaam2[] = $resultaat->c2naaam;
		$Accoalias2[] = $resultaat->alias;
		$Landalias2[] = $resultaat->c2alias;
		$vluchtprijzen2[] = $resultaat->c2vluchtprijs;
		$vanafprijs2[] = $resultaat->vanafPrijs;
		$thumbnail2[] = $resultaat->thumbnail;
		$code2[] = $resultaat->code;
		$waardering2[] = $resultaat->klasse;
 		}

	
$prijskaartje = $vanafprijs[0] + $vanafprijs2[1] +$vluchtprijzen[0] + 70 ;

$stars = str_replace(",", ".", $klasse[0]) * 1.9;

$stars2 = str_replace(",", "", $waardering2[1]) * 1.9;



	//Accommodatie 1
echo '
<div class="combiwrapper">
		
		<div class="combicontent">
			<div class="photo">
				<img src="http://localhost/devabc/upload/accomodations/'.$thumbnail[0].'"  title="Eilandcombinatie '.$Landalias[0].', '.$Acconaam[0].'" alt="Eilandcombinatie '.$Landalias[0].', '.$Acconaam[0].'" width="194" height="104" />
			</div>
				<div class="infobar">
					<div class="country">
						<a href="vakantie/'.$Landalias[0].'/'.$Accoalias[0].'.html" title="Eilandhoppen '.$Landalias[0].', '.$Acconaam[0].'"><strong>'.$Landnaam[0].'</strong></a>
					</div>
						<div class="star-rating" style=" margin-top:15px; width:'.round($stars, 0).'px;">'.$stars.'</div>
				</div>
					<div class="accotitle">
						<a href="vakantie/'.$Landalias[0].'/'.$Accoalias[0].'.html" title="Combinatiereizen '.$Landalias[0].', '.$Acconaam[0].'"><strong>'.$Acconaam[0].'</strong></a>
					</div>
							<div class="zoover-rating">[[ZooverRij? &key=`'.$code[0].'`]] </div>
		</div>';
	
	//De ampersand
	echo '<img src="img/afbeeldingen/enteken.png" class="ampersand">';
			
			
	//Accommodatie 2		
	
echo '
		<div class="combicontent">
			<div class="photo">
				<img src="http://localhost/devabc/upload/accomodations/'.$thumbnail[1].'"  title="Eilandcombinatie '.$Landalias[1].', '.$Acconaam[1].'" alt="Eilandcombinatie '.$Landalias[1].', '.$Acconaam[1].'" width="194" height="104" />
			</div>
				<div class="infobar">
					<div class="country">
						<a href="vakantie/'.$Landalias[1].'/'.$Accoalias[1].'.html" title="Eilandhoppen '.$Landalias[1].', '.$Acconaam[1].'"><strong>'.$Landnaam[1].'</strong></a>
					</div>
						<div class="star-rating" style="margin-top:15px; width:'.round($stars2, 0).'px;">'.$stars2.'</div>
				</div>
					<div class="accotitle">
						<a href="vakantie/'.$Landalias[1].'/'.$Accoalias[1].'.html" title="Combinatiereizen '.$Landalias[1].', '.$Acconaam[1].'"><strong>'.$Acconaam[1].'</strong></a>
					</div>
			<div class="zoover-rating">[[ZooverRij? &key=`'.$code[1].'`]]</div>
		</div>	
		
</div>';
	
	
	
	//Prijskaartje
	
echo '<div id="combi_price">Al mogelijk vanaf: €' .$prijskaartje. ',-</div>';


?>

Weird things though,

In both

$waardering2[] = $resultaat->klasse;

//AND

$klasse[] = $result->klasse;

//returns back with a comma, while in the dtabase they are with a point, therefore 

$stars = str_replace(",", ".", $klasse[0]) * 1.9;

$stars2 = str_replace(",", "", $waardering2[1]) * 1.9;

//does not work anymore.

And still, sometimes, even when the sql is put in correctly, it selects an accommodation that it cannot select based on country.

		$select2->where(array(
		'Accomodations.active' => '1',
		'AND:Countries.id:!=' => $cid[0],
		'AND:Accomodations.klasse:=' => (round($klasse[0], 0)), 
		));

What am i missing or doing wrong here?

Cheers,

Robin