We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Playing with xPDO

  • opengeek Reply #11, 14 years, 8 months ago
    Quote from: lossendae at Aug 26, 2009, 03:25 PM

    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" huh
      Jason Coward
      Chief Architect @ MODX
      http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
    • lossendae Reply #12, 14 years, 8 months ago
      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:

      <?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:

      <?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 Reply #13, 14 years, 8 months ago
        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.
        <?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);
?>
          Studying MODX in the desert - http://sottwell.com
          Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
          Join the Slack Community - http://modx.org
        • lossendae Reply #14, 14 years, 8 months ago
          Then it’s the opposite of a normal SQL where the select is the first to be set.

          Good to know.

          Thanks Susan.
          • opengeek Reply #15, 14 years, 8 months ago
            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().
              Jason Coward
              Chief Architect @ MODX
              http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
            • sottwell Reply #16, 14 years, 8 months ago
              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?
                Studying MODX in the desert - http://sottwell.com
                Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
                Join the Slack Community - http://modx.org
              • sottwell Reply #17, 14 years, 8 months ago
                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!
                  Studying MODX in the desert - http://sottwell.com
                  Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
                  Join the Slack Community - http://modx.org
                • lossendae Reply #18, 14 years, 8 months ago
                  Quote from: OpenGeek at Aug 26, 2009, 07:21 PM

                  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.
                    • 28215
                    • 4,149 Posts
                    splittingred Reply #19, 14 years, 8 months ago
                    FYI:

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

                    It will cause problems.
                      shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
                    • lossendae Reply #20, 14 years, 8 months ago
                      Hello,

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

                      First attempt:
                      <?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:

                      <?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:

                      <?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?