next »

[lossendae]

Hello,

i'm trying to convert an existing snippet to revolution and experiencing with xPDO as well.

Here the query that i'm trying to convert:

Code:

$query = "SELECT stv.name,stc.tmplvarid,stc.contentid,stv.type,stv.display,stv.display_params,stc.value";
$query .= " FROM ".$tb1." stc LEFT JOIN ".$tb2." stv ON stv.id=stc.tmplvarid ";
$query .= " LEFT JOIN ".$tb_content." tb_content ON stc.contentid=tb_content.id ";
$query .= " WHERE stv.name='".$tvTags."' AND stc.contentid IN (".implode($cIDs,",").") ";
$query .= " AND tb_content.pub_date >= '".$pub_date."' ";
$query .= " AND tb_content.published = 1 ";
$query .= " ORDER BY stc.contentid ASC;";

I'm trying by doig this:

Code:

$t = $this->modx->newQuery('modTemplateVar');
$t->leftJoin('modTemplateVarResource','tvValues');
$t->leftJoin('modResource','content');
$t->where(array(
'modTemplateVar.name' => $tvTags,
'tvValues.contentid:IN' => '('.implode($cIDs,",").')',
'content.pub_date:>=' => $pub_date,
'content.published' => '1',
));
$t->sortby('tvValues.contentid','ASC');
$tags = $this->modx->getCollection('modTemplateVar',$t);

I believe that the problem is with the IN clause.
Is there another way to use it that i don't know of, or shoul i use xPDOCriteria?

[OpenGeek]

The IN expression is tricky because of quoting in PDO prepared statements.  The easiest way to work around this for now is to make it a literal expression rather than a prepared statement expression:

Code:

"tvValues.contentid IN (" . implode($cIDs,",") . ")"

I'll work on a solution to make IN work as expected, but it may take some time and thought...

[lossendae]

The IN expression is tricky because of quoting in PDO prepared statements.  The easiest way to work around this for now is to make it a literal expression rather than a prepared statement expression:

Code:

"tvValues.contentid IN (" . implode($cIDs,",") . ")"

I'll work on a solution to make IN work as expected, but it may take some time and thought...

I just tested, it does not work with the code i provided.
I have to rewrite it in another way?

Code:

<?php
$t = $this->modx->newQuery('modTemplateVar');
$t->leftJoin('modTemplateVarResource','tvValues');
$t->leftJoin('modResource','content');
$t->where(array(
'modTemplateVar.name' => $tvTags,
"tvValues.contentid IN (" . implode($cIDs,",") . ")",
'content.pub_date:>=' => $pub_date,
'content.published' => '1',
));
$t->sortby('tvValues.contentid','ASC');
$tags = $this->modx->getCollection('modTemplateVar',$t);
?>

I also wonder if there is a simple way to debug the query?
Because actually it just give me an empty array.

[OpenGeek]

Use this code to debug the SQL generated:

Code:

$query->prepare();
$sql = $query->toSQL();

That will give you the actual SQL that would be executed by any xPDOCriteria/xPDOQuery object. Do that before you call getCollection()...

[lossendae]

Thanks Jason, it's really helpful.

However, i've another issue.

I'm trying to use

Code:

<?php
$t = $this->modx->newQuery('modTemplateVar');
$t->select('modTemplateVar.name AS name, tvValues.tmplvarid AS varid'); 
$t->leftJoin('modTemplateVarResource','tvValues','modTemplateVar.id = tvValues.tmplvarid');
$t->leftJoin('modResource','content','tvValues.contentid = content.id');
$t->where(array(
'modTemplateVar.name' => $tvTags,
"tvValues.contentid IN (" . implode($cIDs,",") . ")",
'content.pub_date:>=' => $pub_date,
'content.published' => '1',
));
$t->sortby('tvValues.contentid','ASC');
$t->prepare();
$sql = $t->toSQL();
?>

And the process die (Firefox message : Connection has been reinitialized).

I know that the issue is with the $query->select, since the query work if i comment the line.

Any idea what the problem is?

Ok, i sorted it out by using $query->setClassAlias('tv'); before setting the  $query->select line.

I should have been able to do it without that workaround isn't it?

[OpenGeek]

Try this instead:

Code:

$t->select(array('name' => 'modTemplateVar.name', 'varid' => 'tvValues.tmplvarid'));

[lossendae]

I've tried it (and also by inversing key & value), but the script reinitialize the database connection each time i try to put a select statement with the command.

I don't really get it, because when i use the debug line and past the code in my editor, it works fine. When comment the two line and put back getCollection, the script fail.

During my test, i've take a look at Ditto to try to understand how it get the TV's and replace the function getTVDisplayFormat of the Evo.
I've tried the funtion (line 826) and it works well with my script.

But i would really like to understand why the query failed...

[OpenGeek]

Re-initialization of the database connection should not happen because of the SELECT part of the query; that should simply create a SQLER.  I'm stumped on that TBH.  That points to other problems, possibly with the MySQL client API or drivers compiled with your PHP.  I'll do some more research, but nothing obvious is turning up.

[lossendae]

Ok, if you need infos of my config, let me know!

[lossendae]

I've continued to play with xpdo and the xpdoquery.

In fact, at least on my computers, it woks only if i put the select statement just the line before getCollection.

It it can help...

[OpenGeek]

I've continued to play with xpdo and the xpdoquery.

In fact, at least on my computers, it woks only if i put the select statement just the line before getCollection.

It it can help...

Not sure what you mean by "put select statement just the line before getCollection"

[lossendae]

I'll illustrate with exemple since my english is still not as good as i would like to ^_^'

If i use the following code it does not work:

Code:

<?php
$c = $modx->newQuery('modResource');
$c->leftJoin('modUser','Author','modResource.createdby = Author.id');

//If i put select here, the connection die.
$c->select('modResource.*,Author.username AS username');

$c->where(array(
'type' => 'document',
'parent' => 1,
));
$count = $modx->getCount('modResource',$c);

$c->sortby('publishedon','DESC');
$c->limit(0,20);
$posts = $modx->getCollection('modResource', $c);
?>

But it work with the following one:

Code:

<?php
$c = $modx->newQuery('modResource');
$c->leftJoin('modUser','Author','modResource.createdby = Author.id');
$c->where(array(
'type' => 'document',
'parent' => 1,
));
$count = $modx->getCount('modResource',$c);

$c->sortby('publishedon','DESC');
$c->limit(0,20);

//I've just put the line just above $modx->getCollection
$c->select('modResource.*,Author.username AS username');

$posts = $modx->getCollection('modResource', $c);
?>

[sottwell]

Looks to me like your problem in the first one is that you have the where, sortby and limit parts after the select is done; they should be before the select, since they are part of the query and the query needs them.

Code:

<?php
$c = $modx->newQuery('modResource');
$c->leftJoin('modUser','Author','modResource.createdby = Author.id');
$c->where(array(
'type' => 'document',
'parent' => 1,
));
$c->sortby('publishedon','DESC');
$c->limit(0,20);

$c->select('modResource.*,Author.username AS username');

$count = $modx->getCount('modResource',$c);

$posts = $modx->getCollection('modResource', $c);
?>

[lossendae]

Then it's the opposite of a normal SQL where the select is the first to be set.

Good to know.

Thanks Susan.

[OpenGeek]

Actually, the order in which you call the methods does not affect this at all, since these just build the pieces that are later aggregated in the right order to generate the prepared statements.

However, you don't want the limit call until after you get the total with getCount().

[sottwell]

Did it work? I'm even newer at xPDO than you are; it just looked "wrong" to me. Maybe the select part should be first? Before the leftJoin part?

[sottwell]

However, you don't want the limit call until after you get the total with getCount().

Well, in that case not only am I way off-base, I'm not even in the same county!

[lossendae]

Actually, the order in which you call the methods does not affect this at all, since these just build the pieces that are later aggregated in the right order to generate the prepared statements.

However, you don't want the limit call until after you get the total with getCount().

But in my initial attempt, i did not use getCount, and it did not work either.

Maybe it was another error. I'll re-test it tomorrow and feedback here.

[splittingred]

FYI:

Don't do ->select() _before_ ->getCount().

It will cause problems.

[lossendae]

Hello,

i've tried another statement which is not working even i put $x->select() At the end.

First attempt:

Code:

<?php
$t = $this->modx->newQuery('modTemplateVar');
$t->setClassAlias('tv');
$t->select(array(
'name' => 'tv.name',
'type' => 'tv.type',
'contentid' => 'tvValues.contentid',
'display' => 'tv.display',
'display_params' => 'tv.display_params',
'value' => 'tvValues.value',
));

$t->leftJoin('modTemplateVarResource','tvValues','tv.id = tvValues.tmplvarid');
$t->leftJoin('modResource','content','tvValues.contentid = content.id');

$t->where(array(
'tv.name' => $tvTags,
"tvValues.contentid IN (" . implode($cIDs,",") . ")",
'content.pub_date:>=' => $pub_date,
'content.published' => 1,
));
$t->sortby('tvValues.contentid','ASC');
$tags = $this->modx->getCollection('modTemplateVar',$t);
?>

Second Attempt:

Code:

<?php
$t = $this->modx->newQuery('modTemplateVar');

$t->leftJoin('modTemplateVarResource','tvValues','tv.id = tvValues.tmplvarid');
$t->leftJoin('modResource','content','tvValues.contentid = content.id');

$t->where(array(
'tv.name' => $tvTags,
"tvValues.contentid IN (" . implode($cIDs,",") . ")",
'content.pub_date:>=' => $pub_date,
'content.published' => 1,
));
$t->sortby('tvValues.contentid','ASC');
$t->setClassAlias('tv');
$t->select(array(
'name' => 'tv.name',
'type' => 'tv.type',
'contentid' => 'tvValues.contentid',
'display' => 'tv.display',
'display_params' => 'tv.display_params',
'value' => 'tvValues.value',
));
$tags = $this->modx->getCollection('modTemplateVar',$t);
?>

Both test return in any browser that the connection has been reinitialized.

If i debug both by using:

Code:

<?php
$t->prepare();
$debug = $t->toSQL();
echo $debug;
?>

And past the result in SQLYog it work for both.

As i said above, i've find another way to achieve a similar result with less code, but it would be interesting to know why it does not work that way too?

[OpenGeek]

Again, this sounds like a problem in your PHP compile/configuration; there are bugs in many releases/configurations of the PDO drivers, and this sounds like one of them. The connection being reinitialized is a tell-tale sign of these and is likely being accompanied by an Apache segfault.

[lossendae]

I understand your point, and it is a really weird behaviour.

Statement written like below work:

Code:

<?php
$c->select('modResource.*,Author.username AS username');
?>

But fail if if try to get less column:

Code:

<?php
$c->select('modResource.pagetitle AS title, Author.username AS username');
?>

I Will dig on google to see if i can found something related.

[lossendae]

I've changed my wamp version for the last one.

Connection is not reinitialized, but instead i have another error:

Code:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 261900 bytes) in D:\TAFF\core\xpdo\om\xpdoobject.class.php on line 71

[OpenGeek]

I've changed my wamp version for the last one.

Connection is not reinitialized, but instead i have another error:

Code:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 261900 bytes) in D:\TAFF\core\xpdo\om\xpdoobject.class.php on line 71

Well, that one's easy, you are pulling more data than is allowed by your memory_limit.

[lossendae]

That's what i thought also, but as you can there are 512M allowed which was for testing and also very high.

Seems like a memory leak or something else to me, no?

I've read several forums where people encounter the same kind of issue with ORM or xmlppc.
I'll keep searching since i really need to play with select statement.

edit:

It's really confusing.
I've tested with Wamp -> Apache 2.2.10 - 2.2.11 - 2.2.12 -> PHP 5.2.9 - 5.2.9-1 - 5.2.9-2 - 5.3
and now with xampp -> Apache 2.2.12 -> PHP 5.3.0

And it's always the same result.
How could the implementation of PDO can be wrong with all those configurations?


second edit:

I'm making progress, even if i can't select specific field from the master class, i can do it on subclasses like that:

Code:

<?php
//modResource don't accept restrain/custom field for the query
//modUser which is join in the query can however support any specific field selection even with alias.
$c->select('modResource.*, CreatedBy.username AS username');
?>

It will requier filtering on the output array if necessary.

[OpenGeek]

That's what i thought also, but as you can there are 512M allowed which was for testing and also very high.

Seems like a memory leak or something else to me, no?

I've read several forums where people encounter the same kind of issue with ORM or xmlppc.
I'll keep searching since i really need to play with select statement.

edit:

It's really confusing.
I've tested with Wamp -> Apache 2.2.10 - 2.2.11 - 2.2.12 -> PHP 5.2.9 - 5.2.9-1 - 5.2.9-2 - 5.3
and now with xampp -> Apache 2.2.12 -> PHP 5.3.0

And it's always the same result.
How could the implementation of PDO can be wrong with all those configurations?

Ok, let's back up.

The connection resetting is the only indicator that there is a problem with the PDO driver for MySQL. This can be wrong in all those configurations because the problems are in the MySQL client version that are compiled against PHP, and with all the controversy with MySQL and PDO in the last 2 years, the more recent configurations are the ones that are turning out buggy. BTW, what versions of MySQL server and client are being used in those MAMP/XAMPP versions?

That said, I now see that you have some problems with what you are attempting to do.

First, remember that xPDOObjects are a bit heavier in memory usage than just an associative array result set. When working with xPDO, you need to carefully decide if you want to return a collection of objects or just a result set to work with directly. You can manually instantiate objects from that result set as needed using the xPDOObject->fromArray() method, and that works well in some cases. It's generally never a good idea to select all the rows with all the columns from the modResource (site_content) table; I don't know how many rows, or how much content you have stored in the content field of those rows, but that could easily come to over 512Mb of data, no matter if you return objects or not.

Next and regardless of the above, you can achieve what you are trying to do in the second select() call (which is known as lazy loading), BUT YOU MUST INCLUDE ALL PRIMARY KEY COLUMNS, and you cannot rename columns that will be used to load the object, e.g.

Code:

<?php
$c->select('modResource.id, modResource.pagetitle, Author.username');
?>

That should work fine, and reduce the memory requirements of your objects since they will only include the data for the id and pagetitle fields.

But, with great power comes great responsibility; beware with lazy loading, because if you try to get() a field that you didn't include (or all fields with toArray()) on a lazy object, a query will be executed to retrieve each additional column's data to populate the field values.

[lossendae]

Thank you for the explanations Jason,

i didn't understand that i had to include the id column if i wanted to select specific columns.

It was confusing, since the tutorial to create schema for xPDO tells us that we don't have to precise the id column since the builder does it for us.
I thought that it was the same when querying the database (and did not take attention when i debugged the result in sqlyog)

Since i can't use toArray(), i imagine that i have to precise each column in my loop when i want to populate an array from the result or is there another method to do it automatically?

For Wamp, the MYSQL version is 5.1.36 and for Xampp it's 5.1.37