Example:
I have:
- A document "A" with template "TA".
- A document "B" with template "TB".
- A template variable "TV" associated to "TA" and "TB".
- "A" has the value "TRUE" in "TV"
- "B" has the value "TRUE" in "TV"
If I remove the association between "TA" and "TV" I supposed that I’d have an empty value in "TV" for "A", but the value is still in the database, and the only way to remove it is by hand.
The same result if you change from one template to another with different TV associations.
This is an annoying issue when you have a big website. I would like, in example, this feature when you empty the trash bin. It seems to me very rational.
Looking for a solution I’ve tried to write a complex SQL query, here is my attempt. It doesn’t work and removes more than expected, don’t use it.
/* It doesn't work and removes more than expected!! */
SELECT
site_tmplvar_contentvalues.id,
site_content.id,
site_tmplvars.name,
site_tmplvar_contentvalues.value
FROM
site_content,
site_tmplvars,
site_tmplvar_templates,
site_tmplvar_contentvalues
WHERE
site_content.id = site_tmplvar_contentvalues.contentid
AND
(
site_tmplvar_templates.tmplvarid = site_tmplvar_contentvalues.tmplvarid
AND
site_tmplvar_templates.templateid != site_content.template
)
AND
site_tmplvars.id = site_tmplvar_contentvalues.tmplvarid
LIMIT 10;
/* tmp table */
CREATE TABLE tmptable
SELECT
site_tmplvar_contentvalues.*
FROM
site_content,
site_tmplvars,
site_tmplvar_templates,
site_tmplvar_contentvalues
WHERE
site_content.id = site_tmplvar_contentvalues.contentid
AND
(
site_tmplvar_templates.tmplvarid = site_tmplvar_contentvalues.tmplvarid
AND
site_tmplvar_templates.templateid != site_content.template
)
AND
site_tmplvars.id = site_tmplvar_contentvalues.tmplvarid;
/* delete */
DELETE FROM
site_tmplvar_contentvalues
USING
site_tmplvar_contentvalues
INNER JOIN
tmptable
ON
site_tmplvar_contentvalues.id = tmptable.id;
/* drop */
ALTER tmptable DROP;
Any help will be appreciated.
Thank you.
