I have a bunch of documents that each have tags. I need to return a limited result of the most used tags under a parent id. I'm not quite sure how to get the table name to do a direct query and getresources doesn't seem to process results on the entire batch. I believe I need to do a group by in SQL against all tags. Currently, I'm trying to return all tags under that id and process them as arrays but it can't handle the result set (over 5000) and it's not the most efficient way to do it.
Does anyone know how I might create a query that basically says something like the following?
SELECT tags FROM
tags_table a,
doc_table b,
pivot_table c
where
a.id = c.tag_id and
b.id = c.doc_id and
b.parent_id = 48
GROUP BY tags
ORDER BY Count(tags) DESC LIMIT 0,10
currently I'm doing this and it brings back to many results.
$categories = $modx->runSnippet("getResources",array("parents" => "48","limit" => "0","includeTVs" => "1","processTVs" => "1","outputSeparator" => ",","tpl" => "@INLINE [[+tv.Blog Tags]]","showHidden" => "true","tvFilters"=>"Blog Tags!== "));
I didn't have access to the DB to find the table, but have since gained access. I also found the taglister plugin to help guide the way.
tags (if you have a multiselect-TV for example for your tags) are stored into the table modx_site_tmplvar_contenvalues, all tags into one value delimited by || (double-pipe) or , (comma)
I think with that much Resources it would be better to store them into an extra table, each tag an extra record together with the Resource - id, than its much easier and faster to get sorted and filtered tags.
You can syncronize the tags-table with your tags in TVs with a plugin for OnDocFormSave, OnEmptyThrash and may be OnDuplicateResource
Interesting idea to create a new table for them. So, you would store the tags in both tables (the one modx uses for auto-tag, and the one that I would use for my own sorting)? I did find the modx_site_tmplvar_contenvalues table once I got access and realized that each document has a comma separated list of tags, which doesn't make it easy to compare all docs with one SQL statement (to find the most popular). I was considering a stored procedure to sort.
Quote from: ajmccallum at Jan 13, 2014, 10:17 AMSo, you would store the tags in both tables (the one modx uses for auto-tag, and the one that I would use for my own sorting)
that's what I thought. This way, you can still use some modx-extras for tagging, but you can build fast queries, if you need them with some custom code.
It's a good idea and I haven't heard of those plugins yet either. Thank you.
