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

Sorting a Resource query result set by the value of a template variable

    • 37059
    • 368 Posts
    polrbear Reply #1, 15 years, 8 months ago
    I’m going to make this as clear as I can, understanding only dimly what I’m doing smiley

    I have a snippet that displays a list of resources in groups - let’s call them fruits and vegetables. Now, ’type’ (fruit or vegetable) is one template variable; there are also other template variables, one of which is "calories per serving" (cps). I want to group the resources by type, then sort the individual resources by cps.

    Here’s the code that grabs all the resources for one group:

    $c = $modx->newQuery('modResource');
$c->where('`modResource`.`id` IN ('.$query.')');
$resources = $modx->getCollection('modResource',$c);


    This code is in a function that runs once for each group and accepts a variable called $query. Here’s the code where $query is set:

    $query = 'SELECT DISTINCT `contentid` FROM `modx_site_tmplvar_contentvalues` WHERE `tmplvarid` = "5" AND `value` = '.$type;


    In a misguided attempt to accomplish my goal, I concocted a much more complicated version of the above query, involving an inner join and an ORDER BY statement referencing the calories, which produced the result set in the desired order - the problem being, of course, that the IN statement in the xPDO query ignored the order of the contentids in the result set.

    So, I understand how to select resources based on TV’s using an IN statement, but what about sorting those resources based on the values of other TV’s? Surely this is not beyond MODx/xPDO? It’s definitely beyond me smiley

    huh
      Jason
      • 9207 ☆ A M B ☆
      • 2,475 Posts
      Everett Reply #2, 15 years, 8 months ago
      You can specify sort using the query object’s "sortby" method. In your example, you’d use it after the where method, e.g.
      $c->sortby('name_of_one_of_your_columns','ASC');


      And I think your use of the where method is off... have a look at http://rtfm.modx.com/display/xPDO20/xPDOQuery.where

      Maybe it’s easier to do this if you write the entire MySQL query yourself so it makes sense (sanitizing your user inputs of course), or use xPDO’s methods entirely to construct the query. You’re a little bit in-between here, and that can be a frustrating area to be in.

      Here’s an example to get you started using xPDO’s prepare/execute methods (this is for if you want to write your own SQL query):

      // The mostly raw query, using Oracle style :placeholders
$query = 'SELECT col1,col2,col3 FROM some_table WHERE col1 = :some_value AND col2 = :other_value';

// Filters, corresponding to the placeholders in your query
$filters[':some_value'] 	= 'sanitized input';
$filters[':other_value'] 	= 'some other sanitized input';

$sth = $modx->prepare($query);
$sth->setFetchMode(PDO::FETCH_ASSOC); // optional mode set here
$sth->execute($filters);

$results = $sth->fetchAll();


      Hope that helps.
        Hosting stuff: http://fireproofsocks.com/
        Coding stuff: http://craftsmancoding.com/
        Blog stuff: http://tipsfor.us/
        • 37059
        • 368 Posts
        polrbear Reply #3, 15 years, 8 months ago
        Quote from: Everett at Jan 21, 2011, 12:45 AM

        Maybe it’s easier to do this if you write the entire MySQL query yourself so it makes sense (sanitizing your user inputs of course), or use xPDO’s methods entirely to construct the query. You’re a little bit in-between here, and that can be a frustrating area to be in.

        Thanks, Everett, for another quick and helpful reply. I think you hit the nail on the head with the above statement. In trying to do things the "right" way without really understanding xPDO, I simply got stuck. I appreciate your xPDO to help me get started, but the easiest way forward for me was simply to rewrite the script without xPDO at all, using the deprecated $modx->db functions for this part that I had used elsewhere. What I did was simply to reconstruct the loop so that it would cycle through the "more complex" (preordered) query to which I referred earlier, using $modx->getObject to pull each record rather than using getCollection to grab them all at once.

        Again, thank you. Just talking about the issue on the forum often helps me understand and solve it, even if I don’t end up doing it in the "recommended" way. I do plan to move to xPDO in the future. Sometime. I know that’s where everything is going.
          Jason
          • 9207 ☆ A M B ☆
          • 2,475 Posts
          Everett Reply #4, 15 years, 8 months ago
          Glad you got a solution figured out -- that’s by far the most important thing. I’m learning a lot about xPDO as I work though a couple projects, and I’m really liking it. It’s a bit of a steep learning curve to be sure, but I’m trying to chip in on the documentation as best I can in the hopes that those little tidbits will pave the way for other people to get things figured out.

          For future reference, the most helpful resource I can point you to is this:
          http://rtfm.modx.com/display/xPDO20/More+Examples+of+xPDO+XML+Schema+Files

          That page compares the xPDO XML schema side by side with raw MySQL queries and xPDO PHP code so you can get an idea of how to interact with the objects programmatically. Writing that page was what really accelerated my understanding of the underlying xPDO framework.
            Hosting stuff: http://fireproofsocks.com/
            Coding stuff: http://craftsmancoding.com/
            Blog stuff: http://tipsfor.us/
            • 37059
            • 368 Posts
            polrbear Reply #5, 15 years, 8 months ago
            Looks interesting - thanks smiley
              Jason