Query Built from xPDO Criteria Adds Error-Producing "( AND" in WHERE Clause

80 Posts

smg6511v2 Reply #1, 10 years, 6 months ago

I'm creating a simple query to get the media source properties based on a tv and am running into mysterious troubles. The code...

$c = $modx->newQuery('modTemplateVar','modTemplateVar');
$c->select('modMediaSource.properties');
$c->leftJoin('modMediaSource','modMediaSource','modMediaSource.id = modTemplateVar.source');
$c->where(array(
	'modTemplateVar.name'=>'item-image'
));
$props = $modx->getCollection('modTemplateVar',$c);

... produces this query when output via toSQL():

SELECT modMediaSource.properties 
FROM `site_tmplvars` AS `modTemplateVar` 
LEFT JOIN `media_sources` `modMediaSource` ON modMediaSource.id = modTemplateVar.source 
WHERE  ( AND `modTemplateVar`.`name` = 'item-image' )

The "AND" is of course causing a sql error. In this case, since there is only one where condition, the parenthesis are not needed (but don't cause a problem). Is this a bug, or is there a trick to doing this that someone knows of?

This question has been answered by multiple community members. See the first response.

[ed. note: smg6511v2 last edited this post 10 years, 6 months ago.]

5,888 Posts

Bruno17 Reply #2, 10 years, 6 months ago

$c = $modx->newQuery('modTemplateVar','modTemplateVar');

the second argument is a XPDO - criteria.
'modTemplateVar' doesn't make much sense as a criteria and confuses the conditions.

should be:

$c = $modx->newQuery('modTemplateVar');

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #3, 10 years, 6 months ago

In addition to what Bruno said, remember that Media Source properties are a JSON string in the database, but when you retrieve them yourself with $modx->get(), they're a PHP associative array.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

80 Posts

smg6511v2 Reply #4, 10 years, 6 months ago

OK, I made a goofy mistake there! However, I'm discovering what I'm trying to do, though simple at first glance, is baffling me. I'm simply trying to retrieve the media source properties of a tv by its name. The working sql is here:

SELECT MediaSource.properties
FROM `media_sources` AS `MediaSource`
LEFT JOIN  `site_tmplvars` AS `modTemplateVar` ON MediaSource.id = modTemplateVar.source
WHERE `modTemplateVar`.`name` = 'item-image'

However, when examining the modx schema, I see that the modTemplateVar object actually doesn't contain the key "source" (although it is present in the db table). The only class with the key "source" is modElement. It seems the relationships are a bit complicated here and I can't come up with how to translate the sql to a pure xpdo query. Any ideas?

discuss.answer

5,888 Posts

Bruno17 Reply #5, 10 years, 6 months ago

what about

$tv->getSource($context,false);

to get the source of the TV?

https://github.com/modxcms/revolution/blob/2.x/core/model/modx/modelement.class.php#L762

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

24,544 Posts

BobRay Reply #6, 10 years, 6 months ago

I left an answer earlier, but it's missing, so ...

modTemplateVar extends modElement, so it inherits the source field (which contains the ID of the Media Source.

Bruno's code will get you the modMediaSource object for that TV. You would need this ahead of it:

$tv = $modx->getObject('modTemplateVar', array('name' => 'item-image'));

This would be a little faster:

$tvId = 12; /* replace with the ID of your item-image TV */
$tv = $modx->getObject('modTemplateVar', $tvId);
$mediaSource = $tv->getSource($context,false);

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

80 Posts

smg6511v2 Reply #7, 10 years, 6 months ago

Thanks so much Bruno & Bob! That took the mystery out of it! Here's my final code for anyone who might find it useful:

if(!function_exists('cfGetImageBaseByTvName')){
	function cfGetImageBaseByTvName($tvname,$rootrelative=false,$property='baseUrl',$context='web') {
		global $modx;
		$imagebase = '';
		$tv = $modx->getObject('modTemplateVar', array('name' => $tvname));
		if(!empty($tv)){
			$imagebase = $tv->getSource($context,false)->get('properties')[$property]['value'];
			if(!empty($imagebase)){
				$imagebase = $rootrelative ? '/'.str_replace('//', '/', $imagebase) : trim($imagebase,'/') ;
			} 
		} else {
			$msg = 'Can not get object for tv named "'.$tvname.'". Look for typos in the tv name passed to this function.';
			$modx->log(modX::LOG_LEVEL_ERROR, $msg,'','cfGetImageBaseByTvName',__FILE__,__LINE__);
		}
		return $imagebase;
	}
}

[ed. note: smg6511v2 last edited this post 10 years, 6 months ago.]

24,544 Posts

BobRay Reply #8, 10 years, 6 months ago

Very nice. wink

I'm glad I could help.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

Answered Query Built from xPDO Criteria Adds Error-Producing "( AND" in WHERE Clause