[Solved] getCollection returns fields not in query

25 Posts

Qwarble Reply #1, 14 years, 1 month ago

Hello! I'm trying to get a collection of objects with this snippet

$ids = $modx->getChildIds($modx->resource->id);
$query=$modx->newQuery('modResource');
$query->setClassAlias('mR');
$query->leftJoin('modTemplateVarResource','mTVR','mR.id=mTVR.contentid AND mTVR.tmplvarid=1');
$query->select(array('mR.id','mR.pagetitle','mTVR.value price'));
if(count($ids)>0){
    $query->where(array("mR.id:IN"=>$ids));
}
else{
    $query->where(array("mR.id"=>$modx->resource->id));
}
$col = $modx->getCollection('modResource',$query);
$modx->log(1,$query->toSQL());
foreach($col as $c){
    $params = $c->toArray();
    $modx->log(1,print_r($params,true));
    $output.=$modx->getChunk($tpl,$params);
}
return $output;

SQL query in log is:

SELECT mR.id, mR.pagetitle, mTVR.value price FROM `modx_site_content` AS `mR` LEFT JOIN `modx_site_tmplvar_contentvalues` `mTVR` ON mR.id=mTVR.contentid AND mTVR.tmplvarid=1 WHERE `mR`.`id` = 213

So, every thing is fine but when I see logged objects ( $modx->log(1,print_r($params,true)) ) they have all thair fields as I would get all fields from DB. But my query contains only id, pagetitle and price. Can anybody help me? I was debugging sources of xPdo, acessibleSimpleObject and modResource, but there is no any extra fields, only fields from my query. But when I see log my objects have all their fields filled with data of that object. If I had had the object with fields pagetitle='p',alias='a',content='c' and executed my query I would get the same object (pagetitle='p',alias='a',content='c'). I thought that not presenting in query fields should be blank or default. I guess that answer should be rather simple, and digging sources isnt a right way. I'm stumped and will be glad for any help. [ed. note: Qwarble last edited this post 14 years, 1 month ago.]

483 Posts

Fuzzical Logic Reply #2, 14 years, 1 month ago

Your SQL Statement is actually not correct for some reason and this may be confusing the database. The part of the statement that is incorrect is:

LEFT JOIN `modx_site_tmplvar_contentvalues` `mTVR`

It should be:

LEFT JOIN `modx_site_tmplvar_contentvalues` AS `mTVR`

This might be the cause. I was having problems with aliasing in joins previuosly, so I simply got rid of the alias by changing the call to:

$query->leftJoin('modTemplateVarResource',null,'mR.id=mTVR.contentid AND mTVR.tmplvarid=1');

You might try this instead:

$ids = $modx->getChildIds($modx->resource->id);
$query=$modx->newQuery('modResource');
$query->setClassAlias('mR');
$query->leftJoin('modTemplateVarResource',null,'mR.id=modTemplateVarResource.contentid AND modTemplateVarResource.tmplvarid=1');
$query->select(array('mR.id','mR.pagetitle','modTemplateVarResource.value price'));
if(count($ids)>0){
    $query->where(array("mR.id:IN"=>$ids));
}
else{
    $query->where(array("mR.id"=>$modx->resource->id));
}
$col = $modx->getCollection('modResource',$query);
$modx->log(1,$query->toSQL());
foreach($col as $c){
    $params = $c->toArray();
    $modx->log(1,print_r($params,true));
    $output.=$modx->getChunk($tpl,$params);
}
return $output;

The other alias is working correctly, so you shouldn't have to change it at all.

Website: Extended Dialog Development Blog: on Extended Dialog
Add-ons: AJAX Revolution, RO.IDEs Editor & Framework (in works) Utilities: Plugin Compatibility List
Tutorials: Create Cross-Context Resources, Cross-Context AJAX Login, Template-Based Actions, Remove Extensions from URLs

Failure is just another word for saying you didn't want to try. "It can't be done" means "I don't know how".

25 Posts

Qwarble Reply #3, 14 years, 1 month ago

Thanks for suggestion, I rewrite snippet and get rid of all aliases.
Now my query is

SELECT modResource.id, modResource.pagetitle, modTemplateVarResource.value FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `modTemplateVarResource` ON modResource.id=modTemplateVarResource.contentid AND modTemplateVarResource.tmplvarid=1 WHERE `modResource`.`id` = 213

And snippet is

<?php
$ids = $modx->getChildIds($modx->resource->id);
$query=$modx->newQuery('modResource');
$query->select(array('modResource.id','modResource.pagetitle','modTemplateVarResource.value'));
$query->leftJoin('modTemplateVarResource',null,'modResource.id=modTemplateVarResource.contentid AND modTemplateVarResource.tmplvarid=1');
if(count($ids)>0){
    $query->where(array("modResource.id:IN"=>$ids));
}
else{
    $query->where(array("modResource.id"=>$modx->resource->id));
}
$col = $modx->getCollection('modResource',$query);
$modx->log(1,$query->toSQL());
foreach($col as $c){
    $params = $c->toArray();
    $modx->log(1,print_r($params,true));
    $output.=$modx->getChunk($tpl,$params);
}
return $output;

But output has not changed. It contains all the fields of resource with id 213 plus my joined field (The snippet starts on the page of 213 resource. I check else condition). I dont understand what I am doing wrong. There are no errors in modx log.

483 Posts

Fuzzical Logic Reply #4, 14 years, 1 month ago

There wouldn't be... One other I noticed about the SQL statement is that it is not quoting multi-word fields. In MySQL and SQL Server, this is necessary for a lot of queries. Have you tried changing "value price" to "value_price" or "valueprice"? It's not a sure thing, as the SQL String returned might not accurately reflect what is being sent to the database. But that is all I can see...

Website: Extended Dialog Development Blog: on Extended Dialog
Add-ons: AJAX Revolution, RO.IDEs Editor & Framework (in works) Utilities: Plugin Compatibility List
Tutorials: Create Cross-Context Resources, Cross-Context AJAX Login, Template-Based Actions, Remove Extensions from URLs

Failure is just another word for saying you didn't want to try. "It can't be done" means "I don't know how".

25 Posts

Qwarble Reply #5, 14 years, 1 month ago

Fuzzical Logic, thank you for help. I found answer on my question. It was not getCollection, but toArray function. Its description in manual is:

array toArray(
   [string $keyPrefix = ''],
   [boolean $rawValues = false],
   [boolean $excludeLazy = false],
   [boolean|integer|string|array $inludeRelated = false])
)

So, when I send array to chunk, "toArray" function gets Lazy fields for me and send them too. If you dont want to get lazy fields (they are the fields that not in query), you will use toArray params:

$params = $c->toArray('',false,true);

When I used this, output becomes as it should be.

483 Posts

Fuzzical Logic Reply #6, 14 years, 1 month ago

Awesome! That's great to know! Thanks for sending that back, as I would never have guessed that!

Of course this means that ->toSQL() is not a reliable way to debug queries, as it may lead to poking and prodding it like my previous responses. I think I'm going to do some testing on it and see if a bug needs to be submitted. [ed. note: fuzzicallogic last edited this post 14 years, 1 month ago.]

Website: Extended Dialog Development Blog: on Extended Dialog
Add-ons: AJAX Revolution, RO.IDEs Editor & Framework (in works) Utilities: Plugin Compatibility List
Tutorials: Create Cross-Context Resources, Cross-Context AJAX Login, Template-Based Actions, Remove Extensions from URLs

Failure is just another word for saying you didn't want to try. "It can't be done" means "I don't know how".