Выборка по Значению TV параметра
Привет всем читателям 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 на этот ресурс. ONmodx_site_tmplvar_contentvalues.contentid=modx_site_content.id Далее идет тот самый шаблонный метод становления критерии WHERE ( modx_site_tmplvar_contentvalues.tmplvarid= ( SELECTmodx_site_tmplvars.idFROMmodx_site_tmplvars WHEREmodx_site_tmplvars.name='icon' ) AND modx_site_tmplvar_contentvalues.value = 'example-icon' ) OR ( modx_site_tmplvar_contentvalues.tmplvarid= ( SELECTmodx_site_tmplvars.idFROMmodx_site_tmplvars WHEREmodx_site_tmplvars.name='isCosmos' ) AND modx_site_tmplvar_contentvalues.value = 'IS' ) Разделю это на части, здесь 1 блок в скобках = 1 критерию по полю, опишу блок подробнее: ( modx_site_tmplvar_contentvalues.tmplvarid= ( SELECTmodx_site_tmplvars.idFROMmodx_site_tmplvars WHEREmodx_site_tmplvars.name= <abbr title="icon">{%ИМЯ_TV_ПАРАМЕТРА%}</abbr> ) AND modx_site_tmplvar_contentvalues.value{&ОПЕРАТОР_ВЫРАЖЕНИЯ&} {%ИСКОМОЕ_ЗНАЧЕНИЕ_TV%} ) Далее, после 1ого блока — идет оператор OR — это сопоставимо WHERE условию — WHEREcolumn= 'value' OR ... bla bla where statements UPD1: Оператор AND пока не допустим, я ушел в поиск решения для этой возможности /END_UPD UPD2: Альтернатива более лучший вариант: SELECT * FROMmodx_site_contentASresource WHERE ( resource.idIN ( SELECTval.contentidFROMmodx_site_tmplvarsAStv LEFT JOINmodx_site_tmplvar_contentvaluesASvalONtv.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 * FROMmodx_site_content LEFT JOINmodx_site_tmplvar_contentvalues ONmodx_site_tmplvar_contentvalues.contentid=modx_site_content.id WHERE ( modx_site_tmplvar_contentvalues.tmplvarid= ( SELECTmodx_site_tmplvars.idFROMmodx_site_tmplvars WHEREmodx_site_tmplvars.name='icon' ) AND modx_site_tmplvar_contentvalues.value = 'example-icon' ) OR ( modx_site_tmplvar_contentvalues.tmplvarid= ( SELECTmodx_site_tmplvars.idFROMmodx_site_tmplvars WHEREmodx_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')
)
)
)
Для начала, с днем рождения! Всего наилучшего! По сабжу: все это клево, но не оптимально. 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", ), ));
P.S. Чтобы подсветка кода была, есть специальная кнопочка: joxi.ru/BA0dezWI1NPJAy Или просто оборачивайте в тег <code>
Николай привет и спасибо) Может на ты будем? Да с вариантом с 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 мне был нужен;), про подсветку кода — имел ввиду синтаксис. Так то я в первый раз где либо топик написал)
Ты видимо недопонимаешь механизма 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 сформировал.
про подсветку кода — имел ввиду синтаксис. Я понял. И как раз показал тебе как это делать. Ты прописываешь тег <pre>. Вот этого делать не нужно. Посмотри подсветку в моих комментах, и код без подсветки у себя в комменте. Найди 10 отличий. Лучше вставлять в тег <code> или пользоваться специальной кнопочкой.
Ой понял сори, я то полагался на пред-просмотр, а в нем криво отображается содержимое тега 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.
Посмотри еще раз внимательно мой запрос: $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-запросов :) Так что я знаю, что говорю.
Да все правильно :) фишка не в этом, если задача у нас будет — выбрать ресурсы у которых должны быть несколько TV полей определенного значения, а не ресурсы у которых имеется какая-то любая комбинация TV => Значение среди заданных в критерии. Я знаю что у тебя есть понимание, просто на SQL перевожу дабы меня поняли лучше :)
$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 )) и т.п., но тем не менее на большинство задач тривиальных его хватает вполне.
Да 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'");
Развивай далее запрос как хочется. :)