Выборка по Значению TV параметра

Jan 9, 2015

Привет всем читателям ModxClub, первым делом пользуясь случаем хочу поздравить и мало-ли кому напомнить о том что наступил новый год ;)

В поисках истока решения по поводу выборки ресурсов с критериями по значению их Телевизоров «Дополнительных полей(Template Variables/TV)», не смог найти именно корень этой возможность и в чем она проявляется, а именно сейчас будет кому-как покажется, средней сложности SQL запрос.

Раньше ведь мы могли(я мог=D) только взять ресурс и припасти все его TV параметры, но зачем тогда нужна сама таблица modx_site_tmplvar_contentvalues? я недоумевал, да и не было времени у меня чтобы вникнуть в вопрос самостоятельно, ведь разрабы Modx могли обеспечить хранение значений или даже ключей+значений конкретного ресурса через поле modx_site_content.properties которое хранит через xPDO JSON массив.

Я припас запрос SQL который может решить задачи посредством вложенных SELECT значений: для меня была задача поставить запрос таким образом — что-бы запрос был шаблонным и имел любые возможности построения критерий по ключ=значение, точно так-же как и обычная выборка ресурсов с их условиями.

SELECT * FROM `modx_site_content` 
	LEFT JOIN `modx_site_tmplvar_contentvalues` 
		ON `modx_site_tmplvar_contentvalues`.`contentid` = `modx_site_content`.`id`
WHERE (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='icon'
		) AND
			`modx_site_tmplvar_contentvalues`.`value` = 'example-icon'
	)
	OR (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='isCosmos'
		) AND
		`modx_site_tmplvar_contentvalues`.`value` = 'IS'
	)

Главное чтобы код отображался в исходном формате и желательно с подсветкой, а-то подумают что я криворучкиц патцан))))

Код SQL запроса в принципе демонстрирует корень того как можно просто ставить критерии на дополнительные поля при обращении за modResource коллекцией или просто объектом.

Первым делом JOIN`имся в таблицу Значений дополнительных полей

LEFT JOIN `modx_site_tmplvar_contentvalues`

соответственно сопоставляя ресурс и ссылку с таблицы TV на этот ресурс.

ON `modx_site_tmplvar_contentvalues`.`contentid` = `modx_site_content`.`id`

Далее идет тот самый шаблонный метод становления критерии

WHERE (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='icon'
		) AND
			`modx_site_tmplvar_contentvalues`.`value` = 'example-icon'
	)
	OR (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='isCosmos'
		) AND
		`modx_site_tmplvar_contentvalues`.`value` = 'IS'
	)

Разделю это на части, здесь 1 блок в скобках = 1 критерию по полю, опишу блок подробнее:

(
	`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
		SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
		WHERE `modx_site_tmplvars`.`name`= <abbr title="icon">{%ИМЯ_TV_ПАРАМЕТРА%}</abbr>
	) AND
		`modx_site_tmplvar_contentvalues`.`value` {&ОПЕРАТОР_ВЫРАЖЕНИЯ&} {%ИСКОМОЕ_ЗНАЧЕНИЕ_TV%}
)

Далее, после 1ого блока — идет оператор OR — это сопоставимо WHERE условию —

WHERE `column` = 'value' OR ... bla bla where statements

UPD1: Оператор AND пока не допустим, я ушел в поиск решения для этой возможности /END_UPD

UPD2: Альтернатива более лучший вариант:

SELECT * FROM `modx_site_content` AS `resource`
WHERE (
	`resource`.`id` IN (
		SELECT `val`.`contentid` FROM `modx_site_tmplvars` AS `tv`
		LEFT JOIN `modx_site_tmplvar_contentvalues` AS `val` ON `tv`.`id` = `val`.`tmplvarid`
		WHERE (
			(tv.name = 'icon' AND val.value = 'example-icon') 
			OR
			(tv.name = 'isCosmos' AND val.value = 'IS')
		)
	)
)

/END_UPD

Еще не думал о оптимизации этого отрывка, просто здесь по логике чем больше критерий по TV — тем больше вложенных SELECT получается, Я пока не знаю как это отразится на производительности

Код выдумал буквально только-что)

Все это дело я еще не впихивал в xPDO реалии, но как многие знают в нем есть возможность подготовки критерии из нативного SQL,

Я мог бы и растянуть статью до моментов с использованием из xPDO и тех прелестях которые открываются с помощью этого не мало важного момента, привести много скринов и time-тестов производительности(которых пока не знаю), но сам решил отписаться вкратце, времени есть не много) сегодня мне стукает 20, поэтому новый топик думаю будет приемлимым)

UPD: Нашел я все-же решение которое будет выбирать по значениям нескольких TV, кидаю все наброски:

/** @sql ver 1
SUPPORT ONLY OR STATEMENT AND JOINING MAIN TABLE TO modx_site_tmplvar_contentvalues
**/
	SELECT * FROM `modx_site_content` 
	LEFT JOIN `modx_site_tmplvar_contentvalues` 
		ON `modx_site_tmplvar_contentvalues`.`contentid` = `modx_site_content`.`id`
WHERE (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='icon'
		) AND
			`modx_site_tmplvar_contentvalues`.`value` = 'example-icon'
	)
	OR (
		`modx_site_tmplvar_contentvalues`.`tmplvarid` = (
			SELECT `modx_site_tmplvars`.`id` FROM `modx_site_tmplvars`
			WHERE `modx_site_tmplvars`.`name`='isCosmos'
		) AND
		`modx_site_tmplvar_contentvalues`.`value` = 'IS'
	)
	 

/** SQL ver 2
SUPPORT ONLY OR STATEMENT
***/
SELECT * FROM `modx_site_content` AS `resource`
WHERE (
	`resource`.`id` IN (
		SELECT `val`.`contentid` FROM `modx_site_tmplvars` AS `tv`
		INNER JOIN `modx_site_tmplvar_contentvalues` AS `val` 
				ON `tv`.`id` = `val`.`tmplvarid`
		WHERE (
			(tv.name = 'icon' AND val.value = 'example-icon')
			OR
			(tv.name = 'isCosmos' AND val.value = 'IS')
		)
	)
) 


/** SQL ver 3 
SUPPORT COMPLETE STATEMENTS
***/
SELECT * FROM `modx_site_content` AS `resource`
WHERE (
	(
		`resource`.`id` IN (
			SELECT `val`.`contentid` FROM `modx_site_tmplvars` AS `tv`
				INNER JOIN `modx_site_tmplvar_contentvalues` AS `val`
					ON `tv`.`id` = `val`.`tmplvarid`
			WHERE (tv.name = 'icon' AND val.value = 'example-icon')
		)
	)
		AND
	(
		`resource`.`id` IN (
			SELECT `val`.`contentid` FROM `modx_site_tmplvars` AS `tv`
				INNER JOIN `modx_site_tmplvar_contentvalues` AS `val`
					ON `tv`.`id` = `val`.`tmplvarid`
			WHERE (tv.name = 'isCosmos' AND val.value = 'IS')
		)
	)
	
)