query on TV radio with default value

6 Posts

krillso Reply #1, 14 years, 1 month ago

Hi all.

I was doing a simple query on a TV radio value that had a default value.
All the resources who didn't have the default value showed up fine in the results, but it seems that resources with default value aren't stored at all in the database.

Doing this would give me 0 results as there are no actual entries in the DB:

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array('parent' => $parent,
'published' => $getPublished,
'deleted' => 0),
'TemplateVarResources.tmplvarid' => 21,
'TemplateVarResources.value' => $TV_VALUE;

If i tried the version below, it still wouldn't give me any results because those rows that i want don't even have a blank value in the db, it seems the tmplvar row for the resource is deleted if default value.

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array('parent' => $parent,
'published' => $getPublished,
'deleted' => 0),
'TemplateVarResources.tmplvarid' => 21,
'TemplateVarResources.value:!=' => $THE_TV_VALUE_I_DONT_WANT;

I ended up working around this by pulling all resources and filtering out those with the tv_value i don't want..

Does anyone have a better solution to this, or have i misunderstood something?

Best regards,
Kristian

Web developer at http://kaigan.se

5,888 Posts

Bruno17 Reply #2, 14 years, 1 month ago

you can try something like that:

<?php

$tvName = 'yourTVname';
$tvValue = $TV_VALUE;

$tmplVarTbl = $this->modx->getTableName('modTemplateVar');
$tmplVarResourceTbl = $this->modx->getTableName('modTemplateVarResource');
$tvValueField = 'tvr.value';
$tvDefaultField = 'tv.default_text';
$sqlOperator = '=';
$TVfilter = "(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)) " .
    ")";


$c = $modx->newQuery('modResource');
$c->where(array('parent' => $parent,
'published' => $getPublished,
'deleted' => 0));

$c->where($TVfilter,xPDOQuery::SQL_AND);

or use a functon like that:

https://github.com/Bruno17/MIGX/blob/master/core/components/migx/model/migx/migx.class.php#L1168

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

6 Posts

krillso Reply #3, 14 years, 1 month ago

Thanks for the reply, i'll keep this in mind if i stumble across the problem again =)

I think its a bit odd though, that the default value behaves in this way in the database. Feels like the value should be saved no matter what. Makes what i feel should be simple queries very advanced like the one you presented. Could also cause bigger problems if i wanted to export the data in some way to another application and relied on having the values as expected.

Web developer at http://kaigan.se

5,888 Posts

Bruno17 Reply #4, 14 years, 1 month ago

The default-value shouldn't be saved as tmplVarValue. This way, when you change the default-value sometimes, all records, which have the default-value are updated with the new default-value automatically and so on.
You only need to do TV-queries the right way (also, when exporting TV-values somehow) and all is ok.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!