xPDO query help needed!

10 Posts

studiorepublic Reply #1, 15 years, 3 months ago

I can’t figure out what I’m doing wrong here...

I need to get a list of resource id’s whose value of a tv (id = 18) is greater than or equal to 7.5. When I run the code below the resulting array seems to contain the content every possible object in my site. I’m sure it’s obvious but can someone tell me how I’ve screwed this up please? I’m running 2.1.2.

Thanks!

Chris

$c = $modx->newQuery('modTemplateVarResource');
$c->select($modx->getSelectColumns('modTemplateVarResource','','',array('id','value')));
$c->where(array(
	'value:>=' => 7.5,      
	'tmplvarid' => 18,
));

$collection = $modx->getCollection('modTemplateVarResource', $c);

10 Posts

studiorepublic Reply #2, 15 years, 3 months ago

A bit more info:
Adding:

$c->prepare();
print_r($c->toSQL());

Gives the correct sql:

SELECT `id`, `tmplvarid` FROM `wood_site_tmplvar_contentvalues` AS `modTemplateVarResource` WHERE  ( `modTemplateVarResource`.`value` >= '7.5' AND `modTemplateVarResource`.`tmplvarid` = 18 )

MODX Staff
10,725 Posts

opengeek Reply #3, 15 years, 3 months ago

It works as expected for me, but you should not select() those columns if you are retrieving the object. Don’t you want the contentid, not the id column anyway?

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

10 Posts

studiorepublic Reply #4, 15 years, 3 months ago

Thanks for the reply. You’re right, I do just want the contentid, so I’ve changed the code as the below, but doing a print_r on the resultant collection still seems to output everthing in the db...

$c = $modx->newQuery('modTemplateVarResource');
$c->select($modx->getSelectColumns('modTemplateVarResource','','','',array('contentid')));
$c->where(array(
  'tmplvarid:=' => 18
));

$collection = $modx->getCollection('modTemplateVarResource', $c);

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #5, 15 years, 3 months ago

Do you get the expected results when executing the sql in something like phpmyadmin on your server?

I’m going to guess that because it’s trying to compare a string "greater than" that’s the culprit..

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.

MODX Staff
10,725 Posts

opengeek Reply #6, 15 years, 3 months ago

In order to get an instantiate an object, you must at least select the primary key column.

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

10 Posts

studiorepublic Reply #7, 15 years, 3 months ago

Isn’t that what I did in the first place?

$c->select($modx->getSelectColumns('modTemplateVarResource','','',array('id','value')));

Are there any xPDO guides out there? This is the first thing I’ve really struggled with within modx - so far I’ve lost half a day to a simple db query!

10 Posts

studiorepublic Reply #8, 15 years, 3 months ago

Reinstalled and had a fiddle and I’ve got it working. What I don’t understand is why a print_r on the collection seems to output the entire db, but iterating through it in a loop works fine. On to the next question...

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #9, 15 years, 3 months ago

...why a print_r on the collection seems to output the entire db

That’s because you have an array of objects, and objects hold variables, relationships and so on to other objects and that indeed shows you a hefty chunk of data when you output that all. That’s why in your for each loop you use object->toArray() - to get just the fields in an array.

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.

10 Posts

studiorepublic Reply #10, 15 years, 3 months ago

Gotcha! Thanks for that Mark!