I was looking around the ditto snippet (and MODx in general) and I realized that ditto uses a large number of db queries to display it’s data. I thought "what if I could accomplish this in just one query?" So I immediately went to work on writing a MySQL query to retrieve documents and their respective template variables.
I was somewhat successful. But I accomplished what I needed, I built a query to retrieve any number of documents and ONE TV that applies to that document’s template. I thought someone else might find a use for this. It’s pretty complex, so I’ll explain it later.
Here it is:
SELECT DISTINCT
sc.*,
tv.name AS tv_name,
tvc.value AS tv_value,
tv.display AS tv_display,
tv.display_params AS tv_params,
tv.type AS tv_type
FROM modx_site_content AS sc
LEFT JOIN modx_document_groups AS dg ON dg.document = sc.id
LEFT JOIN modx_site_tmplvar_contentvalues AS tvc ON tvc.contentid = sc.id
LEFT JOIN modx_site_tmplvar_templates AS tvtpl ON tvtpl.tmplvarid = tvc.tmplvarid
LEFT JOIN modx_site_tmplvars AS tv ON tv.id = tvc.tmplvarid
WHERE (sc.parent IN (40, 41) AND sc.deleted=0)
AND (sc.privateweb=0)
AND sc.template=tvtpl.templateid
AND tv.name='NewsCategories'
AND published=1
GROUP BY sc.id
ORDER BY createdon ASC
A nasty query indeed. It’s a combination of the [tt]getTemplateVars()[/tt] and [tt]getDocumentChildren()[/tt] with a lot of work in between. Here’s what it does:
- Retrieves all data for the documents whose parent id is 40 or 41 (sc.*) and (sc.parent IN (40, 41))
- Retrieves TV data for generating output (rest of the SELECT)
[li]The TV to search for is defined in tv.name in the WHERE clause (AND tv.name=’NewsCategories’)
This is the raw query that I wrote using phpMyAdmin, so it will require some table name changing to get it to work in your scenario, but there you have it, a single query for document and template variable pairs.
I am personally using this query in a snippet to generate a list of documents grouped by the content of ’NewsCategories’ TV I created and then display the list in a tree-like fashion where each document is displayed under it’s respective category. I hope someone else can make use of this. Thanks MODx Dev Team for making a great System!
EDIT: Oops... wrong query...
...