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

trouble with DB query

    • 21884
    • 57 Posts
    ntech Reply #1, 16 years, 3 months ago
    Hi,

    I’ve got a query i’m running on one of modx’s core tables, i first constructed my query and tested in phpmyadmin on the database, and now i’m trying to convert it to work with the xPDO query function, i’m obviously doing something wrong as the query i see when i do prepare and toSQL isn’t right.

    my query is:

    SELECT DISTINCT price.contentid
 FROM modx_site_tmplvar_contentvalues beds
 JOIN modx_site_tmplvar_contentvalues price
 ON price.tmplvarid = 3 AND price.value = "300-400"
 JOIN modx_site_tmplvar_contentvalues location
 ON location.tmplvarid = 5 AND location.value LIKE "%bletchley%"
 WHERE beds.tmplvarid =1 AND beds.value >=2


    on my modx database this returns 3 contentids that i would then use for getCollection etc...

    i put this in my snippet:

    $type = 3; //parent
$price = "300-400"; //price-range
$beds = 2; //number of beds
$location = "bletchley"; //location

$c = $modx->newQuery('modTemplateVarResource','Beds');
$c->join('modTemplateVarResource','Price');
$c->join('modTemplateVarResource','Location');
$c->where(array(
	'Price.tmplvarid' => $type,
	'Price.value' => "$price",
	'Location.tmplvarid' => 5,
	'Location.value:LIKE' => "%$location%",
	'Beds.tmplvarid' => 1,
	'Beds.value:>=' =>  $beds,
));


    but what i get when i do:

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


    to test it, is this:
    SELECT `modTemplateVarResource`.`id` AS `modTemplateVarResource_id`, `modTemplateVarResource`.`tmplvarid` AS `modTemplateVarResource_tmplvarid`, `modTemplateVarResource`.`contentid` AS `modTemplateVarResource_contentid`, `modTemplateVarResource`.`value` AS `modTemplateVarResource_value` FROM `modx_site_tmplvar_contentvalues` AS `modTemplateVarResource` JOIN `modx_site_tmplvar_contentvalues` `Price` ON JOIN `modx_site_tmplvar_contentvalues` `Location` ON WHERE ( AND ( `Price`.`tmplvarid` = '3' AND `Price`.`value` = '300-400' AND `Location`.`tmplvarid` = '5' AND `Location`.`value` LIKE '%bletchley%' AND `Beds`.`tmplvarid` = '1' AND `Beds`.`value` >= '2' ) )


    how do i order the query in my snippet to join the way my SQL query works?


      • 21884
      • 57 Posts
      ntech Reply #2, 16 years, 3 months ago
      ok so i figured it out. thought i’d read enough documentation but obviously not.

      my code is like this:
      $type = 3; //parent
$price = "300-400"; //price-range
$beds = 2; //number of beds
$location = "bletchley"; //location

$c = $modx->newQuery('modTemplateVarResource');
$c->select ('DISTINCT Price.contentid');
$c->leftJoin("modTemplateVarResource","Price","Price.tmplvarid = $type AND Price.value = ".'"'.$price.'"');
$c->leftJoin("modTemplateVarResource","Location","Location.tmplvarid = 5 AND Location.value LIKE ".'"%'.$location.'%"');
$c->where(array(
	'modTemplateVarResource.tmplvarid' => 1,
	'modTemplateVarResource.value:>=' =>  $beds,
));

        • 21884
        • 57 Posts
        ntech Reply #3, 16 years, 3 months ago
        alright i’m totally confused, that query above, when run in the database returns a list of contentIds

        doing modx->newQuery doesn’t.

        how do i get the contentIds so i can then get a collection of those resources and output them?

          • 22303 MODX Staff
          • 10,725 Posts
          opengeek Reply #4, 16 years, 3 months ago
          How are you proceeding after creating the xPDOQuery, a.k.a. $c?
            Jason Coward
            Chief Architect @ MODX
            http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
            • 21884
            • 57 Posts
            ntech Reply #5, 16 years, 3 months ago
            i didn’t get very far there, i tried this:

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

foreach ($collection as $resourceId => $resource) {

}


            and i haven’t gotten a whole lot further as trying to see what the contents are isn’t turning up anything.
            from the various dumps/prints i’ve tried i’m seeing an array, one of the items is my query, but i don’t actually see any results of the query.
              • 21884
              • 57 Posts
              ntech Reply #6, 16 years, 3 months ago
              to give some background,

              i need to filter documents based on values in TVs associated with those documents, initially i was using getResources and the tvFilters which was working quite nicely until my client requested that one of the values filtered against be changed to "greater than or equal to".

              so i made the query thinking i could get that collection of document ids and then figure something out from there.

              essentially i’m trying to do this:

              SELECT * FROM `modx_site_content` AS `content` WHERE `content`.`id` IN (SELECT DISTINCT Price.contentid FROM `modx_site_tmplvar_contentvalues` `Beds` JOIN `modx_site_tmplvar_contentvalues` `Price` ON Price.tmplvarid = 3 AND Price.value = "300-400" JOIN `modx_site_tmplvar_contentvalues` `Location` ON Location.tmplvarid = 5 AND Location.value LIKE "%bletchley%" WHERE Beds.tmplvarid = 1 AND Beds.value >= 2)


              and that currently (phpmyadmin) on my setup returns 3 documents from the modx_site_content table.

              i assumed there’s a way to do this using modx/xPDO but I’m a bit stumped.
                • 28215
                • 4,149 Posts
                splittingred Reply #7, 16 years, 3 months ago
                For this: 

                SELECT * FROM `modx_site_content` AS `content` WHERE `content`.`id` IN (SELECT DISTINCT Price.contentid FROM `modx_site_tmplvar_contentvalues` `Beds` JOIN `modx_site_tmplvar_contentvalues` `Price` ON Price.tmplvarid = 3 AND Price.value = "300-400" JOIN `modx_site_tmplvar_contentvalues` `Location` ON Location.tmplvarid = 5 AND Location.value LIKE "%bletchley%" WHERE Beds.tmplvarid = 1 AND Beds.value >= 2)


                Try this:

                $subc = $modx->newQuery('modTemplateVarResource');
$subc->setClassAlias('Beds');
$subc->innerJoin('modTemplateVarResource','Price','`Price`.`tmplvarid` = 3 AND `Price`.`value` = "300-400"');
$subc->innerJoin('modTemplateVarResource','Location','`Location`.`tmplvarid` = 5 AND `Location`.`value` LIKE "%bletchley%"');
$subc->select('DISTINCT `Price`.`contentid`');
$subc->where(array(
   'Beds.tmplvarid' => 1,
   'Beds.value:>=' => 2,
));
$subc->prepare();
$sql = $subc->toSql();

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


                Not 100% sure if that’ll work; just typed it ad-hoc. But it should in theory.
                  shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
                  • 21884
                  • 57 Posts
                  ntech Reply #8, 16 years, 3 months ago
                  that’s working, thanks!!