Count items with specific TV value

270 Posts

lokust Reply #1, 17 years, 9 months ago

I have a TV (offer-category) that outputs radiobuttons in the manager with options: Retail, Leisure or Food To Go
How can I count the number of items that have a TV value of "Retail" ?

I’ve created a snippet for this purpose though not working :<

<?php
$query = 'SELECT COUNT(offer-category) FROM `site_content` WHERE offer-category='Leisure' AND WHERE parent='15';
$result = mysql_query($query) or die('Sorry, we could not count the number of results: ' . mysql_error());
$numberofresults = mysql_result($result, 0);
echo $numberofresults;
?>

Cheers,

Al

107 Posts

Kleist Reply #2, 17 years, 9 months ago

The template variables are in their own table in the database. (Actually multiple tables, so you need a more advanced query.)

Hint: Match the id in site_content with contentid in other tables etc. (Same goes for templatevar_id, or what it’s called.)

MODx snippet-glossary 101:
Ditto = Content Lister -- Wayfinder == Menu Builder -- Jot = Comment Control

2,310 Posts

bunk58 Reply #3, 17 years, 9 months ago

$query = 'SELECT COUNT(value) FROM `modx_site_tmplvar_contentvalues` WHERE `value` = \'Leisure\' AND `contentid` =15';
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$total = ''.$row[0].'';
echo $total;

Something like that should get you going.

270 Posts

lokust Reply #4, 17 years, 9 months ago

Thanks chaps.

Bunk that code chunk works well though only for the specific item id (contentid). Any idea on how to specify a parent id and then loop through the child documents looking for the TV value ’Leisure" ?

2,542 Posts

heliotrope Reply #5, 17 years, 9 months ago

Hi,

to do so, you’ll need at least 3 tables:
- site_content
- site_tmplvar
- site_tmplvars_contentvalues

your query will look like something like that

SELECT sc.id, stc.value
FROM site_content as sc
LEFT JOIN site_tmplvar as st ON st.id=stc.tmplvarid
LEFT JOIN site_tmplvars_contentvalues as stc sc.id=stc.contentid
WHERE st.name=’offer-category’ AND stc.value=’leisure’ AND sc.parent=15

I don’t remember the exact name of each table field but you get the idea

:-)

Made with MODx : [url=http://www.copadel.com]copadel, fruits et l

MODX Staff
10,725 Posts

opengeek Reply #6, 17 years, 9 months ago

Quote from: lokust at Feb 01, 2009, 12:34 PM

Bunk that code chunk works well though only for the specific item id (contentid). Any idea on how to specify a parent id and then loop through the child documents looking for the TV value ’Leisure" ?

Here’s another approach...

Use $modx->getChildIds() to get the children id’s, then simply use those as the criteria in your query using the SQL IN clause:

<?php
$total = '0';
$parentId = !empty($parentId) ? $parentId : 0;
$childIds = $modx->getChildIds($parentId, 1);
if (!empty($value) && !empty($childIds)) {
    $query = "SELECT COUNT(`value`) FROM " . $modx->getFullTableName('site_tmplvar_contentvalues') . " WHERE `tmplvarid` = {$tmplVarId} AND `value` = '{$value}' AND `contentid` IN (" . implode(',', $childIds) . ")";
    if ($result = $modx->db->query($query)) {
        $total = $modx->db->getValue($result);
    }
}
return (string) $total;
?>

NOTE: I added the `tmplvarid` = {$tmplVarId} because this will make the query much more efficient, restricting the number of records that have to be searched for the value you are looking for. You can get the id of your Template Variable from the manager interface (it’s next to the name in parenthesis).

Then just pass a parentId, value, and a tmplVarId in the snippet call to make it configurable...

[!GetTVCount? &parentId=`15` &value=`Leisure` &tmplVarId=`10`!]

This snippet can count the number of direct children that have an actual value recorded for a specific value in a specific TV.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

2,310 Posts

bunk58 Reply #7, 17 years, 9 months ago

Jason,
Thanks for your beautifully crafted example.
Added it to my snippet armoury grin

270 Posts

lokust Reply #8, 17 years, 9 months ago

Wow thanks for all your help. I got it working with this:

<?php
$query = 'SELECT COUNT(value) FROM `modx_site_tmplvar_contentvalues` WHERE `value` = \'Food To Go\' AND `tmplvarid` =25';
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$total = ''.$row[0].'';
echo $total;
?>

Though will now take a look at the Modx way. Thanks Jason.

Al

615 Posts

Sylvaticus Reply #9, 17 years, 9 months ago

Quote from: OpenGeek at Feb 01, 2009, 03:43 PM

This snippet can count the number of direct children that have an actual value recorded for a specific value in a specific TV.

lokust’s sollution works, but I need to set the parendID, så I need Jason’s snippet.

Is there a way to make it count the numbers that are two or more levels deeper than the direct children?

I think, thererfor I am! But what I am, and why...?

MODX Staff
10,725 Posts

opengeek Reply #10, 17 years, 9 months ago

Quote from: Sylvaticus at Feb 05, 2009, 09:10 AM

Is there a way to make it count the numbers that are two or more levels deeper than the direct children?

Sure, you could change the depth parameter (second parameter) of the $modx->getChildIds() call to whatever depth you want, or make that a new tag property for the snippet that can be passed in.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek