getCollection with multiple template variable parameters

108 Posts

lukemcd Reply #1, 14 years, 6 months ago

I am using a getResources call that works but is straining my system so I want to see if a custom snippet makes more sense. The getResources call is:

[[getResources?   
	&parents=`51`
	&tpl=`company_deal_maker`
	&sortby=`pagetitle`
	&sortdir=`ASC`
	&includeTVs=`1`
	&processTVs=`1`
	&tvPrefix=``
	&showHidden=`1`
	&tvFilters=`membership_status>=100,deal_name!=''`
	&limit=``
]]

I am having trouble getting the snippet to pull the same resources. Here is what I have so far:

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->innerJoin('modTemplateVar','TemplateVar','`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`');
$c->where(array(
	array(
		'parent' => 51,
		'published' => true,
		'deleted' => false, 
	),
	array(
		array(
			'TemplateVar.name' => 'membership_status',  
			'TemplateVarResources.value:>=' => 100,
		),
		array(
			'TemplateVar.name' => 'deal_name',
			'TemplateVarResources.value:!=' => ''
		)
	)
));
$c->sortby('pagetitle');
$collection = $modx->getCollection('modResource',$c);

foreach ($collection as $o) {
	$output .= '<br />' . $o->get('pagetitle') . ' (' . $o->get('id') . ')';
}

return $output;

I think I'm close. I'd appreciate any help.

108 Posts

lukemcd Reply #2, 14 years, 6 months ago

I have also tried tried using the code below. If I remove one or the other TV arrays it works. And I have triple-checked that there are resources that meet both criteria.

<?php
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->innerJoin('modTemplateVar','TemplateVar','`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`');
$c->where(array(
    'parent' => 51,
    'published' => true,
	'deleted' => false,
	array(
		array(
			'TemplateVar.name' => 'membership_status',
			'TemplateVarResources.value:>=' => 100
		),
		array(
			'TemplateVar.name' => 'deal_name',
			'TemplateVarResources.value:!=' => ''
		),
	)
));

$c->sortby('pagetitle');
$collection = $modx->getIterator('modResource',$c);

foreach ($collection as $o) {
	$output .= ',' . $o->get('pagetitle') . ' (' . $o->get('id') . ')';
}

return $output;

I have scoured the forums but can't find anything about how to handle this particular issue.

88 Posts

lazylegs Reply #3, 14 years, 6 months ago

Hello, you are lacking bit of stuff, main reason why the query does not work is that you try to get 2 values from same table to same row, which is directly impossible without subquery or more joining.

This query should work, note should as I just wrote it with the mood and don't have directly similar data to use but just wrote small section about this to latest article. So kind of tested

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource', 'tv_member', array('tv_member.name' => 'membership_status'));
$c->innerJoin('modTemplateVar', 'tv_member_val', array('tv_member_val.tmplvarid = tv_member.id', 
    'tv_member_val.content_id = modResource.id'));

$c->innerJoin('modTemplateVarResource', 'tv_deal', array('tv_deal.name' => 'deal_name'));
$c->innerJoin('modTemplateVar', 'tv_deal_val', array('tv_deal_val.tmplvarid = tv_deal.id', 
    'tv_deal_val.content_id = modResource.id'));
    
$c->where(array(
    'parent' => 51,
    'published' => 1,
    'deleted' => 0,
    'tv_member_val.value:>=' => 100,
    'tv_deal_val:!=' => '' 
));
$c->sortby('pagetitle');
$collection = $modx->getCollection('modResource',$c);
 
foreach ($collection as $o) {
    $output .= '' . $o->get('pagetitle') . ' (' . $o->get('id') . ')';
}
 
return $output;

Hopefully that works like it should, if you want to process the TV's I do recommend checking other methods as this just returns raw data and only real objects are the modResource objects.

Oh yeah, if this works (hopefully it does) thank Jay Gilmore for retweeting your message

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

108 Posts

lukemcd Reply #4, 14 years, 6 months ago

Thanks orangeline. I tested your code and it's not working. But I see what you're saying about more joining, maybe if I tweak the syntax some more I can figure it out.

88 Posts

lazylegs Reply #5, 14 years, 6 months ago

Already closed computer and on through phone so this will be quick. You need to select the required columns for the template variables also or the SQL throws unknown column name if I recall right.

Check the syntax from http://lazylegs.info and the latest articlet there go to page that explains how to use the aeticle stuff on modResource. Sorry for bad link etc.. wrote the thing for ages, published and already forgot what I wrote.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

88 Posts

lazylegs Reply #6, 14 years, 6 months ago

After good night sleep, noticed that I missed .value for second TV in the where() clause

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

108 Posts

lukemcd Reply #7, 14 years, 6 months ago

I tried after making that correction but still no luck. Even when I remove one of the TV conditions, so we are just looking for resources with membership_status >= 100, it's not coming up with resources.

Your article looks very informative so I am going through that to see how I might alter the statement.

289 Posts

ReSpawN Reply #8, 14 years, 6 months ago

Figured it out yet? Try convering it to a RAW SQL by inserting the following before the getCollection statement. I'd also advise you do use getIterator instead.

$c->prepare();
echo $c->toSQL();
exit();

Slap that piece of SQL into your phpMyAdmin and see if there is an error in that SQL. I've looked it over but and I've noticed that you're fetching a complete record on the base of, amoungst others, "tv_deal_val".

Later on, it states 'tv_deal_val:!=' => ''. This is where I kinda loose track of it. In my opinion and experience, this should be tv_deal_val.value since you nowhere select that only row or rename it in your SQL. Then again, I haven't read it in completely.

When using 'modTemplateVar', 'tv_deal_val' in the innerJoin or leftJoin (rightJoin not withstanding), you're basically telling SQL to rename and prefix each modTemplateVar column with and as tv_deal_val. Thus, your columns would change from modTemplateVar.id to tv_deal_val.id. Keep that in mind. ;-)

If you haven't figured it out by tonight, I'd be more than willing to help you out, granted you've got the time as well.

@MarkGHErnst

Developer at Adwise Internetmarketing, the Netherlands.

88 Posts

lazylegs Reply #9, 14 years, 6 months ago

Actually the second TV is useless as if TV never has value set there wont be a database row for it.

Maybe use of left join would be more useful in the case as it would return empty rows if none is set. And just in case, set $c->distinct()

Also if this is snippet, do not echo anything out, return $c->toSQL(); after $c->prepare() is fine. No need to kill the page in my opinion.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

108 Posts

lukemcd Reply #10, 14 years, 6 months ago

Thanks for the tips. ReSpawN, I did switch over to getIterator, I have heard that is supposed to be faster. And I will change the second TV so it's not looking for an empty value, I see where that is just making things more difficult. Now to run some tests.