Kutuz27 10 января 2015 3 11
Привет всем читателям 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')
		)
	)
	
)

11 комментариев
Fi1osof1
Fi1osof 10 января 2015г в 10:24 #
Для начала, с днем рождения! Всего наилучшего!

По сабжу: все это клево, но не оптимально.

1. На все это давно уже есть getdata-процессор.

2. Раз уж вы в рамках xPDO работаете, то и стройте запросы на нем.
Во-первых, запрос
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'
	)

Переделаем на
SELECT * FROM `modx_site_content` c
	INNER JOIN `modx_site_tmplvar_contentvalues` tvv
            ON `tvv`.`contentid` = `c`.`id`
        INNER JOIN `modx_site_tmplvars` tv
            ON tvv.tmplvarid = tv.id
WHERE (
		tv.`name`='icon' 
                AND
		    `tvv`.`value` = 'example-icon'
	)
	OR (
                tv.`name`='isCosmos' 
                AND
		    `tvv`.`value` = 'IS' 
	)

Здесь нет смысла использовать LEFT JOIN, ибо по условию вы все равно требуете значения, а значит отсутствующие записи не пройдут. Но если хотите универсальности в плане того, что будут запросы без дополнительных условий и чтобы в выборку попадали документы, для которых нет ни одной ТВшки, то да, нужно LEFT JOIN использовать.

Во-вторых, переделаем это на xPDO:
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'TemplateVarResources');
$c->leftJoin('modTemplateVar', 'tv', "tv.id=TemplateVarResources.tmplvarid");

$c->where(array(
    array(
        "tv.name"   => 'icon',
        "TemplateVarResources.value"    => "example-icon",
    ),
    array(
        "OR:tv.name:="   => 'isCosmos',
        "TemplateVarResources.value"    => "IS",
    ),
));
Fi1osof1
Fi1osof 10 января 2015г в 10:32 #
P.S. Чтобы подсветка кода была, есть специальная кнопочка: joxi.ru/BA0dezWI1NPJAy
Или просто оборачивайте в тег <code>
K
Kutuz27 10 января 2015г в 11:03 #
Николай привет и спасибо) Может на ты будем? Да с вариантом с OR условиями я конечно, что-то не до сообразил)

Но как быть если нам надо запросить по условиям по строже чем OR и поставить запрос c AND условиям по TV, такого типа
SELECT * FROM `обычная табла` WHERE `tv_name` = 'value' AND 'tv_name_2' = 'value_2'


Через средства xPDO, твой вариант при использовании AND вообще ничего не вернет, т.к мы не можем ставить условие чтобы у 1 строчки tv_name был и 'name1' и 'name2' одновременно(AND), вот это и встало мне в горле когда уже открыл топик) в топике я указал мнимые UPD блоки в которых добавил наблюдения, решением послужил запрос:


/** 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')
                )
        )
        
)
/**
Запрос если бы дополнительные поля были бы основными:
SELECT * FROM `modx_site_content` AS `resource` WHERE `resource`.`icon` = 'example-icon' AND `resource`.`isCosmos` = 'IS'
*/

я догадывался что я велосипед замучу, просто IDE(pshtorm) обновлял, наткнулся на встроенный менеджер баз данных проекта, начал ковыряться и попутно вспомнил что такой механизм с Tv мне был нужен;), про подсветку кода — имел ввиду синтаксис.

Так то я в первый раз где либо топик написал)
Fi1osof1
Fi1osof 10 января 2015г в 13:26 #
Ты видимо недопонимаешь механизма xPDO. Сам xPDO ничего в базе данных не выбирает, у него нет какого-то собственного механизма прямого обращения к данным базы. xPDO формирует в итоге чистый SQL, и потом выполняет этот SQL-запрос к базе данных. Сделай так:
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'TemplateVarResources');
$c->leftJoin('modTemplateVar', 'tv', "tv.id=TemplateVarResources.tmplvarid");

$c->where(array(
    array(
        "tv.name"   => 'icon',
        "TemplateVarResources.value"    => "example-icon",
    ),
    array(
        "OR:tv.name:="   => 'isCosmos',
        "TemplateVarResources.value"    => "IS",
    ),
));
// Готовим SQL-запрос
$c->prepare();
// Выводим этот SQL-запрос
print $c->toSQL();


На выходе ты увидишь сформированный SQL.
То есть что ты сам напишешь чистый SQL, что подготовишь его средствами xPDO — разницы никакой не будет. А какой результат будет возвращен, зависит уже от этого SQL-запроса, и не вежно сам ты его написал, или xPDO сформировал.
Fi1osof1
Fi1osof 10 января 2015г в 13:29 #
про подсветку кода — имел ввиду синтаксис.
Я понял. И как раз показал тебе как это делать. Ты прописываешь тег <pre>. Вот этого делать не нужно. Посмотри подсветку в моих комментах, и код без подсветки у себя в комменте. Найди 10 отличий.
Лучше вставлять в тег <code> или пользоваться специальной кнопочкой.
K
Kutuz27 11 января 2015г в 11:13 #
Ой понял сори, я то полагался на пред-просмотр, а в нем криво отображается содержимое тега code).

Как я понял еще, мы друг друга сейчас вообще не понимаем) прям какой-то барьер)
Механизм я понимаю, я знаю что xPDOQuery это абстракция SQL запроса — разница лишь в подходе(ООП) его организации, так-же как и xPDOObject представление объекта.

Когда я писал:
Через средства xPDO, твой вариант при использовании AND вообще ничего не вернет, т.к мы не можем ставить условие чтобы у 1 строчки tv_name был и 'name1' и 'name2' одновременно(AND), вот это и встало мне в горле когда уже открыл топик) в топике я указал мнимые UPD блоки в которых добавил наблюдения, решением послужил запрос:

я имел ввиду:
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'TemplateVarResources');
$c->leftJoin('modTemplateVar', 'tv', "tv.id=TemplateVarResources.tmplvarid");

$c->where(array(
    array(
        "tv.name"   => 'icon',
        "TemplateVarResources.value"    => "example-icon",
    ),
    array(
        "AND:tv.name:="   => 'isCosmos',
        "TemplateVarResources.value"    => "IS",
    ),
));


что эквивалентно:
SELECT * FROM `modx_site_content` AS `resource`
        INNER JOIN `modx_site_tmplvar_contentvalues` AS `val` ON `val`.`contentid` = `resource`.`id`
        INNER JOIN `modx_site_tmplvars` AS `tv` ON `tv`.`id` = `val`.`tmplvarid`
WHERE (
    (tv.name = `icon` AND val.value = `example-icon`)
    AND
    (tv.name = `isCosmos` AND val.value = `IS`)
)


То-есть когда нам нужны ресурсы у которых оба(или более) поля чему то равны, запрос всегда будет возвращать 0 строк, даже если и будут ресурсы с обоими TV параметрами в БД, проверь сам)

Делая такой запрос с OR, мы получим ресурсы в которых какое либо указанное TV поле соответствует критерии — ну все верно ведь оператор OR.

Fi1osof1
Fi1osof 11 января 2015г в 12:15 #
Посмотри еще раз внимательно мой запрос:
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'TemplateVarResources');
$c->leftJoin('modTemplateVar', 'tv', "tv.id=TemplateVarResources.tmplvarid");

$c->where(array(
    array(
        "tv.name"   => 'icon',
        "TemplateVarResources.value"    => "example-icon",
    ),
    array(
        "OR:tv.name:="   => 'isCosmos',
        "TemplateVarResources.value"    => "IS",
    ),
));

Там не AND:, там OR:. И формируется вот такой SQL-запрос:
SELECT modResource.* 
    FROM `modx_site_content` AS `modResource` 
    LEFT JOIN `modx_site_tmplvar_contentvalues` `TemplateVarResources` ON `modResource`.`id` =  `TemplateVarResources`.`contentid` 
    LEFT JOIN `modx_site_tmplvars` `tv` ON tv.id=TemplateVarResources.tmplvarid 
    WHERE  (  
        ( `tv`.`name` = 'icon' AND `TemplateVarResources`.`value` = 'example-icon' )  
        OR  
        ( `tv`.`name` = 'isCosmos' AND `TemplateVarResources`.`value` = 'IS' )  )  


Поверь, у меня есть понимание SQL-запросов :) Так что я знаю, что говорю.
K
Kutuz27 11 января 2015г в 12:20 #
Да все правильно :) фишка не в этом, если задача у нас будет — выбрать ресурсы у которых должны быть несколько TV полей определенного значения, а не ресурсы у которых имеется какая-то любая комбинация TV => Значение среди заданных в критерии.

Я знаю что у тебя есть понимание, просто на SQL перевожу дабы меня поняли лучше :)
Fi1osof1
Fi1osof 11 января 2015г в 12:34 #
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource', 'tv', "tv.tmplvarid=modResource.id AND tv.tmplvarid = 1 AND tv.value='value1'");
$c->innerJoin('modTemplateVarResource', 'tv', "tv2.tmplvarid=modResource.id AND tv2.tmplvarid = 2 AND tv2.value='value2'");

Развивай далее запрос как хочется.

Да, в xPDO не получается формировать запросы с подзапросами (типа select… where col.value in (select… from )) и т.п., но тем не менее на большинство задач тривиальных его хватает вполне.
K
Kutuz27 11 января 2015г в 13:10 #
Да xPDO вообще крутая штука честно говоря :)
поправлю:

$c = $modx->newQuery('modResource');

$c->innerJoin('modTemplateVar', 'tv1', "tv1.name='icon'");
$c->innerJoin('modTemplateVarResource', 'val1', "val1.contentid=modResource.id AND val1.tmplvarid = tv1.id AND val1.value='example-icon'");

$c->innerJoin('modTemplateVar', 'tv2', "tv2.name='isCosmos'");
$c->innerJoin('modTemplateVarResource', 'val2', "val2.contentid=modResource.id AND val2.tmplvarid = tv2.id AND val2.value='IS'");
Fi1osof1
Fi1osof 11 января 2015г в 15:26 #
Развивай далее запрос как хочется.
:)
Авторизуйтесь или зарегистрируйтесь (можно через соцсети ), чтобы оставлять комментарии.