Multiple TV searching via xPDO?

4 Posts

kmoser Reply #11, 14 years, 3 months ago

Hi opengeek, thanks for your posts. Sadly I'm less experienced with SQL and ModX-queries, and didn't understand yet, how joins work at all. I tried this:

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where( array
(
	'published' => 1,
	'deleted' => 0,
	'parent' => '220',
	'TemplateVarResources.tmplvarid' => 21,
	'TemplateVarResources.value:LIKE' => '%'.$lang.'%',
	'TemplateVarResources.tmplvarid' => 20,
	'TemplateVarResources.value:LIKE' => '%'.$context.'%',
));

what makes the $lang condition be ignored, just like you explained. Then, following your suggestion, I tried that:

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where( array
(
	array
	(
		'published' => 1,
		'deleted' => 0,
		'parent' => '220',
	),
	array
	(
		array
		(
			'TemplateVarResources.tmplvarid' => 21,
			'TemplateVarResources.value:LIKE' => '%'.$lang.'%',
		),
		array
		(
			'TemplateVarResources.tmplvarid' => 20,
			'TemplateVarResources.value:LIKE' => '%'.$context.'%',
		),
	),
));

and ended up with no results at all. Could you give me a hint, how to make it work? Or would it be more efficient, to forget about joins, just grab a whole bunch of data and sort those TV-things out by a PHP loop?

5,888 Posts

Bruno17 Reply #12, 14 years, 3 months ago

this is the part of getResources put in a class-function, how I use it in MIGXdb for filtering resources by TV-values:

    function tvFilters($tvFilters = '', &$criteria)
    {
        $tvFilters = !empty($tvFilters) ? explode('||', $tvFilters) : array();
        if (!empty($tvFilters)) {
            $tmplVarTbl = $this->modx->getTableName('modTemplateVar');
            $tmplVarResourceTbl = $this->modx->getTableName('modTemplateVarResource');
            $conditions = array();
            $operators = array(
                '<=>' => '<=>',
                '===' => '=',
                '!==' => '!=',
                '<>' => '<>',
                '==' => 'LIKE',
                '!=' => 'NOT LIKE',
                '<<' => '<',
                '<=' => '<=',
                '=<' => '=<',
                '>>' => '>',
                '>=' => '>=',
                '=>' => '=>');
            foreach ($tvFilters as $fGroup => $tvFilter) {
                $filterGroup = array();
                $filters = explode(',', $tvFilter);
                $multiple = count($filters) > 0;
                foreach ($filters as $filter) {
                    $operator = '==';
                    $sqlOperator = 'LIKE';
                    foreach ($operators as $op => $opSymbol) {
                        if (strpos($filter, $op, 1) !== false) {
                            $operator = $op;
                            $sqlOperator = $opSymbol;
                            break;
                        }
                    }
                    $tvValueField = 'tvr.value';
                    $tvDefaultField = 'tv.default_text';
                    $f = explode($operator, $filter);
                    if (count($f) == 2) {
                        $tvName = $this->modx->quote($f[0]);
                        if (is_numeric($f[1]) && !in_array($sqlOperator, array('LIKE', 'NOT LIKE'))) {
                            $tvValue = $f[1];
                            if ($f[1] == (integer)$f[1]) {
                                $tvValueField = "CAST({$tvValueField} AS SIGNED INTEGER)";
                                $tvDefaultField = "CAST({$tvDefaultField} AS SIGNED INTEGER)";
                            } else {
                                $tvValueField = "CAST({$tvValueField} AS DECIMAL)";
                                $tvDefaultField = "CAST({$tvDefaultField} AS DECIMAL)";
                            }
                        } else {
                            $tvValue = $this->modx->quote($f[1]);
                        }
                        if ($multiple) {
                            $filterGroup[] = "(EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) " .
                                "OR EXISTS (SELECT 1 FROM {$tmplVarTbl} tv WHERE tv.name = {$tvName} AND {$tvDefaultField} {$sqlOperator} {$tvValue} AND tv.id NOT IN (SELECT tmplvarid FROM {$tmplVarResourceTbl} WHERE contentid = modResource.id)) " .
                                ")";
                        } else {
                            $filterGroup = "(EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.name = {$tvName} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) " .
                                "OR EXISTS (SELECT 1 FROM {$tmplVarTbl} tv WHERE tv.name = {$tvName} AND {$tvDefaultField} {$sqlOperator} {$tvValue} AND tv.id NOT IN (SELECT tmplvarid FROM {$tmplVarResourceTbl} WHERE contentid = modResource.id)) " .
                                ")";
                        }
                    } elseif (count($f) == 1) {
                        $tvValue = $this->modx->quote($f[0]);
                        if ($multiple) {
                            $filterGroup[] = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)";
                        } else {
                            $filterGroup = "EXISTS (SELECT 1 FROM {$tmplVarResourceTbl} tvr JOIN {$tmplVarTbl} tv ON {$tvValueField} {$sqlOperator} {$tvValue} AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id)";
                        }
                    }
                }
                $conditions[] = $filterGroup;
            }
            if (!empty($conditions)) {
                $firstGroup = true;
                foreach ($conditions as $cGroup => $c) {
                    if (is_array($c)) {
                        $first = true;
                        foreach ($c as $cond) {
                            if ($first && !$firstGroup) {
                                $criteria->condition($criteria->query['where'][0][1], $cond, xPDOQuery::SQL_OR, null, $cGroup);
                            } else {
                                $criteria->condition($criteria->query['where'][0][1], $cond, xPDOQuery::SQL_AND, null, $cGroup);
                            }
                            $first = false;
                        }
                    } else {
                        $criteria->condition($criteria->query['where'][0][1], $c, $firstGroup ? xPDOQuery::SQL_AND : xPDOQuery::SQL_OR, null, $cGroup);
                    }
                    $firstGroup = false;
                }
            }

            return true;

        }

    }

if you have that function in a class you should be able to use it this way:

$filter = 'tvname1==%'.$lang.'%,tvname2==%'.$context.'%';

$c = $modx->newQuery('modResource');
$yourclassInstance->tvFilters($filter,$c);
$c->where(array
    (
        'published' => 1,
        'deleted' => 0,
        'parent' => '220',
    ));

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

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

4 Posts

kmoser Reply #13, 14 years, 3 months ago

Dear Bruno17, thank you very much!

Wow... far more complex than I thought it would be... If someone else reads this, I had to add

global $modx;

on top of the tvFilters function, to make it work.

Because I wanted to understand what's going on, I made the function output, what it does with my two-TV-filter. So the fully handwritten query would look like that:

$c = $modx->newQuery('modResource');
$c->condition( $c->query["where"][0][1], "(EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '%luponatural%' AND tv.name = 'Site' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modx_site_tmplvars` tv WHERE tv.name = 'Site' AND tv.default_text LIKE '%luponatural%' AND tv.id NOT IN (SELECT tmplvarid FROM `modx_site_tmplvar_contentvalues` WHERE contentid = modResource.id)) )", xPDOQuery::SQL_AND, null, "0"); 
$c->where( array
(
	'published' => 1,
	'deleted' => 0,
	'parent' => '220',
));
$children = $modx->getCollection('modResource',$c);

Definitely easier to just use the function, Bruno17 took from getResources, I think ;-) So, thanks a lot again!

Does anyone know, if a query like this is more efficient than a PHP loop sorting resources out by TVs?

4 Posts

kmoser Reply #14, 14 years, 3 months ago

To complete it for everyone else reading this, here's a reusable function for sorting by TemplateVars, also taken from getResources:

function sortbyTV( $sortbyTV, $criteria, $sortdirTV='ASC', $sortbyTVType='string')
{
	global $modx;
	
	$columns = $modx->getSelectColumns('modResource', 'modResource');
	$criteria->select($columns);
	
	if (!empty($sortbyTV)) {
		$criteria->leftJoin('modTemplateVar', 'tvDefault', array(
			"tvDefault.name" => $sortbyTV
		));
		$criteria->leftJoin('modTemplateVarResource', 'tvSort', array(
			"tvSort.contentid = modResource.id",
			"tvSort.tmplvarid = tvDefault.id"
		));
		if (empty($sortbyTVType)) $sortbyTVType = 'string';
		if ($modx->getOption('dbtype') === 'mysql') {
			switch ($sortbyTVType) {
				case 'integer':
					$criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS SIGNED INTEGER) AS sortTV");
					break;
				case 'decimal':
					$criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS DECIMAL) AS sortTV");
					break;
				case 'datetime':
					$criteria->select("CAST(IFNULL(tvSort.value, tvDefault.default_text) AS DATETIME) AS sortTV");
					break;
				case 'string':
				default:
					$criteria->select("IFNULL(tvSort.value, tvDefault.default_text) AS sortTV");
					break;
			}
		} elseif ($modx->getOption('dbtype') === 'sqlsrv') {
			switch ($sortbyTVType) {
				case 'integer':
					$criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS BIGINT) AS sortTV");
					break;
				case 'decimal':
					$criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS DECIMAL) AS sortTV");
					break;
				case 'datetime':
					$criteria->select("CAST(ISNULL(tvSort.value, tvDefault.default_text) AS DATETIME) AS sortTV");
					break;
				case 'string':
				default:
					$criteria->select("ISNULL(tvSort.value, tvDefault.default_text) AS sortTV");
					break;
			}
		}
		$criteria->sortby("sortTV", $sortdirTV);
	}
}

It may be used like that:

sortbyTV( 'myTVName', $c, 'ASC');

5,888 Posts

Bruno17 Reply #15, 14 years, 3 months ago

here are both functions as I have them in MIGX:
https://github.com/Bruno17/MIGX/blob/master/core/components/migx/model/migx/migx.class.php#L945

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!