improve my xpdo!

228 Posts

christianhanvey Reply #1, 13 years, 10 months ago

I'm trying to optimise some code to fetch all products by a particular supplier.
I'm using simplecart, which uses resources as products. On my supplier page, I want to list all products by that supplier.
Each product resource has a tv storing the supplier (another resource id), and here's my snippet trying to get all resources/products by that supplier:

// get the current supplier id
$supplier_id = $modx->resource->id;

// get all tv values (resource ids) for product.supplier tv, where it is this supplier
$tv = $modx->getObject('modTemplateVar',43);
$tvVals = $tv->getMany('TemplateVarResources', array('value'=>$supplier_id));
// create an array of the resource ids for this supplier
$resources = array();
foreach($tvVals as $t) {
    $resources[] = $t->get('contentid');
}
// get all resources (which are products) by this supplier
$query = $modx->newQuery('modResource');
$query->where(array(
   'id:IN' => $resources,
   'published'=>1,
   'deleted'=>0
));
$products = $modx->getCollection('modResource', $query);

Any suggestions to improve this code? I'm sure it could be simpler.

5,888 Posts

Bruno17 Reply #2, 13 years, 10 months ago

does this work?

<?php

// get the current supplier id
$supplier_id = $modx->resource->id;

// get all resources (which are products) by this supplier
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'tvr', array("tvr.contentid = modResource.id", "tvr.tmplvarid = 43"));
$c->select($modx->getSelectColumns('modResource','modResource'));
$c->where(array(
    'published' => 1,
    'deleted' => 0,
    'tvr.value' => $supplier_id
    ));
$products = $modx->getCollection('modResource', $c);

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

228 Posts

christianhanvey Reply #3, 13 years, 10 months ago

Thanks Bruno! It works, indeed.
A join, now why did I not try that? I was thinking there was some other api method that might be made for fetching resources via tv values.
That's great.