Фильтрация и сравнение по TV в pdoPage

Здравстуйте
задача: вывести все товар где цена меньше старой цены

как правильно реализовать сравнение двух tv при запросе

пробовал так

&where=`{"tvprice:<":"tvprice_old"}`

но выводит всё подряд

код

[[!pdoPage?
    &parents=`98`
    &context=`catalog`
    &where=`{"price:<":"price_old"}`
    &tpl=`prod_cat`
    &includeTVs=`price,price_old,prod_promo,images`
    &prepareTVs=`1`
    &processTVs=`1`
    &pageLimit=`12`
    &limit=`1000`
    &maxLimit=`12`
    &hideContainers=`1`
    &showLog=`1`
]]
[[!+page.nav]]

лог

0.0001869: pdoTools loaded
0.0000451: xPDO query object created
0.0011802: Included list of tvs: images, price, price_old, prod_promo
0.0004280: leftJoined modTemplateVarResource as TVimages
0.0003772: leftJoined modTemplateVarResource as TVprice
0.0003791: leftJoined modTemplateVarResource as TVprice_old
0.0003679: leftJoined modTemplateVarResource as TVprod_promo
0.0005908: Added selection of modResource: SQL_CALC_FOUND_ROWS `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `richtext`, `template`, `menuindex`, `searchable`, `cacheable`, `createdby`, `createdon`, `editedby`, `editedon`, `deleted`, `deletedon`, `deletedby`, `publishedon`, `publishedby`, `menutitle`, `donthit`, `privateweb`, `privatemgr`, `content_dispo`, `hidemenu`, `class_key`, `context_key`, `content_type`, `uri`, `uri_override`, `hide_children_in_tree`, `show_in_tree`, `properties`
0.0000150: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.images`
0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.price`
0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.price_old`
0.0000110: Added selection of modTemplateVarResource: IFNULL(`value`, 'обычный') AS `tv.prod_promo`
0.0000858: Replaced TV conditions
0.0029690: Processed additional conditions
0.0038400: Added where condition: `TVprice`.`value`:<=price_old, modResource.parent:IN(98,4,196,3,2,7,9,88,89,90,91,92,93,94,95,282,283,285,286,346,347,348,349,350,351,352,208,209,210,211,212,260,261,262,263,264,265,266,269,270,271,272,273,274,275,276,277,278,279,280,281,284,287,288,289,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,267,268,335,8,336,125,146,147,80,148,79,77,149,150,151,78,337,338,339,340,341,342,343,344,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,393,394,395,396,397,398,399,400,401,402,403,505,506,195,439,448,450,452,162,164,178,181,188,334,185,235,239,240,251,293,294,295,301,302,303,306,307,319,321,322,324,325,345,374,381,382,383,384,385,386,387,391,468,469,470,471,472,473,474,475,476,477,478,479,480,481,507,166,171,172,174,176,184,190,191,193,194,204,205,219,222,223,224,225,226,227,229,230,231,241,242,243,244,245,246,247,248,249,250,252,253,308,309,326,327,328,329,330,378,379,380,388,389,390,404,406,409,412,447,449,454,163,173,175,177,179,180,183,186,189,199,200,206,213,214,215,216,217,218,221,228,232,233,234,237,238,255,305,375,376,377,405,407,408,438,451,453,463,464,312,313,314,315,81,82,83,84,85,86,87,192,202,256,299,323,462,161,207,290,291,292,296,297,298,300,304,316,317,318,320,331,165,167,168,169,170,182,187,197,198,201,203,220,236,254,257,258,259,332,333,371,372,373,392,410,411,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,440,441,442,443,444,445,446,455,456,457,458,459,460,461,465,466,467,310,311,96,97,120,121,122,123,124), modResource.published=1, modResource.deleted=0, modResource.isfolder=0, modResource.context_key=catalog
0.0000360: Replaced TV conditions
0.0001650: Sorted by modResource.publishedon, DESC
0.0000050: Limited to 12, offset 
0.0005178: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `modResource`.`id`, `modResource`.`type`, `modResource`.`contentType`, `modResource`.`pagetitle`, `modResource`.`longtitle`, `modResource`.`description`, `modResource`.`alias`, `modResource`.`link_attributes`, `modResource`.`published`, `modResource`.`pub_date`, `modResource`.`unpub_date`, `modResource`.`parent`, `modResource`.`isfolder`, `modResource`.`introtext`, `modResource`.`richtext`, `modResource`.`template`, `modResource`.`menuindex`, `modResource`.`searchable`, `modResource`.`cacheable`, `modResource`.`createdby`, `modResource`.`createdon`, `modResource`.`editedby`, `modResource`.`editedon`, `modResource`.`deleted`, `modResource`.`deletedon`, `modResource`.`deletedby`, `modResource`.`publishedon`, `modResource`.`publishedby`, `modResource`.`menutitle`, `modResource`.`donthit`, `modResource`.`privateweb`, `modResource`.`privatemgr`, `modResource`.`content_dispo`, `modResource`.`hidemenu`, `modResource`.`class_key`, `modResource`.`context_key`, `modResource`.`content_type`, `modResource`.`uri`, `modResource`.`uri_override`, `modResource`.`hide_children_in_tree`, `modResource`.`show_in_tree`, `modResource`.`properties`, IFNULL(`TVimages`.`value`, '') AS `tv.images`, IFNULL(`TVprice`.`value`, '') AS `tv.price`, IFNULL(`TVprice_old`.`value`, '') AS `tv.price_old`, IFNULL(`TVprod_promo`.`value`, 'обычный') AS `tv.prod_promo` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVimages` ON `TVimages`.`contentid` = `modResource`.`id` AND `TVimages`.`tmplvarid` = 5 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprice` ON `TVprice`.`contentid` = `modResource`.`id` AND `TVprice`.`tmplvarid` = 3 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprice_old` ON `TVprice_old`.`contentid` = `modResource`.`id` AND `TVprice_old`.`tmplvarid` = 4 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprod_promo` ON `TVprod_promo`.`contentid` = `modResource`.`id` AND `TVprod_promo`.`tmplvarid` = 2 WHERE  ( `TVprice`.`value` < 'price_old' AND `modResource`.`parent` IN (98,4,196,3,2,7,9,88,89,90,91,92,93,94,95,282,283,285,286,346,347,348,349,350,351,352,208,209,210,211,212,260,261,262,263,264,265,266,269,270,271,272,273,274,275,276,277,278,279,280,281,284,287,288,289,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,267,268,335,8,336,125,146,147,80,148,79,77,149,150,151,78,337,338,339,340,341,342,343,344,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,393,394,395,396,397,398,399,400,401,402,403,505,506,195,439,448,450,452,162,164,178,181,188,334,185,235,239,240,251,293,294,295,301,302,303,306,307,319,321,322,324,325,345,374,381,382,383,384,385,386,387,391,468,469,470,471,472,473,474,475,476,477,478,479,480,481,507,166,171,172,174,176,184,190,191,193,194,204,205,219,222,223,224,225,226,227,229,230,231,241,242,243,244,245,246,247,248,249,250,252,253,308,309,326,327,328,329,330,378,379,380,388,389,390,404,406,409,412,447,449,454,163,173,175,177,179,180,183,186,189,199,200,206,213,214,215,216,217,218,221,228,232,233,234,237,238,255,305,375,376,377,405,407,408,438,451,453,463,464,312,313,314,315,81,82,83,84,85,86,87,192,202,256,299,323,462,161,207,290,291,292,296,297,298,300,304,316,317,318,320,331,165,167,168,169,170,182,187,197,198,201,203,220,236,254,257,258,259,332,333,371,372,373,392,410,411,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,440,441,442,443,444,445,446,455,456,457,458,459,460,461,465,466,467,310,311,96,97,120,121,122,123,124) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 AND `modResource`.`isfolder` = 0 AND `modResource`.`context_key` = 'catalog' )  ORDER BY modResource.publishedon DESC LIMIT 12 "
0.0622201: SQL executed
0.0003309: Total rows: 346
0.0001688: Rows fetched
0.4607699: Prepared and processed TVs
0.0024641: Loaded chunk "prod_cat"
0.2865121: Returning processed chunks
0.8185968: Total time
21 233 664: Memory usage

Александр

27 октября 2014, 09:20

2 098

Комментарии: 4

Володя

27 октября 2014, 13:41

вам же явно в логе пишет — joxi.ru/Vm63zq8cxKvg2Z
потому что у вас where неправильно написан… примеры на этом сайте уже есть поищите.

Александр

27 октября 2014, 14:02

я понимаю что запрос не правильно написан
если пишу &where=`{«tvprice:<»:«500»}` то всё выводит как и положено
но при сравнении двух tv перестает работать

похожего вопроса не нашел, поэтому и спрашиваю помощи

Василий Наумкин

27 октября 2014, 14:48

Для начала нужно поискать, как это делается на чистом SQL, и только потом уже пробовать прописать условие в pdoTools.

Насколько я понимаю, сравнивать колонки в одной таблице за один запрос не получится.

Александр

05 ноября 2014, 22:08

пришлось все таки доделать
в итоге получился такой костыль
прошу знающих людей прокомментировать и дать совет
спасибо

вызов

[[!pdoPage?
    &parents=`98`
    &context=`catalog`
    &resources=`[[pdoResources?
        &parents=`98`
        &context=`catalog`
        &where=`{"isfolder":"0"}`
        &includeTVs=`price,price_old`
        &prepareTVs=`1`
        &processTVs=`1`
        &depth=`5`
        &limit=`1000`
        &tpl=`price_old`
        &showLog=`0`
    ]]`
    &tpl=`category_items_sale`
    &includeTVs=`price,price_old,prod_promo,images`
    &prepareTVs=`1`
    &processTVs=`1`
    &pageLimit=`12`
    &limit=`1000`
    &maxLimit=`12`
    &showLog=`1`
]]

чанк price_old

[[!If?&subject=`[[+tv.price]]`&operator=`!=`&operand=`[[+tv.price_old]]`&then=`[[+id]],`]]

log

0.0003040: pdoTools loaded
0.0000782: xPDO query object created
0.0013659: Included list of tvs: images, price, price_old, prod_promo
0.0008831: leftJoined modTemplateVarResource as TVimages
0.0007808: leftJoined modTemplateVarResource as TVprice
0.0005729: leftJoined modTemplateVarResource as TVprice_old
0.0004449: leftJoined modTemplateVarResource as TVprod_promo
0.0008640: Added selection of modResource: SQL_CALC_FOUND_ROWS `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `richtext`, `template`, `menuindex`, `searchable`, `cacheable`, `createdby`, `createdon`, `editedby`, `editedon`, `deleted`, `deletedon`, `deletedby`, `publishedon`, `publishedby`, `menutitle`, `donthit`, `privateweb`, `privatemgr`, `content_dispo`, `hidemenu`, `class_key`, `context_key`, `content_type`, `uri`, `uri_override`, `hide_children_in_tree`, `show_in_tree`, `properties`
0.0000210: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.images`
0.0000181: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.price`
0.0000179: Added selection of modTemplateVarResource: IFNULL(`value`, '') AS `tv.price_old`
0.0000181: Added selection of modTemplateVarResource: IFNULL(`value`, 'обычный') AS `tv.prod_promo`
0.0068221: Processed additional conditions
0.0081530: Added where condition: modResource.id:IN(592,593,594,598,599,600,605,607,623,624,625,626,634,643,644,659,671,673,675,681,682,687,688,690,692,696,697,717,719,720,722,723,730,731,733,734,737,738,742,759,766,917), modResource.parent:IN(98,4,611,3,2,7,9,88,89,90,91,92,93,94,95,832,833,834,835,836,873,874,876,877,945,705,706,707,708,709,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,860,861,862,863,864,865,866,867,868,869,870,871,872,875,878,879,880,924,939,940,941,942,943,944,859,962,8,125,146,147,80,148,79,77,149,150,151,78,597,608,609,610,612,616,617,620,621,622,628,636,651,718,728,729,735,747,748,758,761,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,881,882,883,884,923,583,584,585,586,587,588,589,590,614,689,715,749,813,814,676,684,727,740,743,591,593,602,618,629,630,634,645,769,888,889,890,896,897,898,912,914,915,946,619,627,643,739,744,745,746,781,782,783,784,785,786,787,788,789,790,791,792,793,815,822,823,824,825,826,827,828,901,917,918,919,930,931,961,595,601,605,607,613,623,625,632,633,637,639,642,660,664,671,673,675,679,685,686,696,697,702,717,721,722,723,724,725,726,730,731,733,736,751,752,753,754,755,756,757,759,762,763,764,765,767,816,817,819,820,821,829,830,831,902,903,920,925,927,928,929,932,936,954,955,956,957,963,592,596,603,606,615,624,626,631,635,641,646,659,661,680,683,687,688,703,710,711,712,713,714,716,720,732,734,737,738,741,742,750,766,780,818,900,964,905,906,907,908,81,82,83,84,85,86,87,672,691,768,779,885,886,887,894,910,911,913,916,921,922,947,948,949,950,951,952,953,582,704,891,892,893,895,899,909,958,594,598,599,600,604,638,640,644,647,648,649,650,652,653,654,655,656,657,658,662,663,665,666,667,668,669,670,674,677,678,681,682,690,692,693,694,695,698,699,700,701,719,760,770,771,772,773,774,775,776,777,778,926,933,934,935,937,938,959,960,904,96,97,120,121,122,123,124), modResource.published=1, modResource.deleted=0, modResource.context_key=catalog
0.0000789: Replaced TV conditions
0.0002930: Sorted by modResource.publishedon, DESC
0.0000062: Limited to 12, offset 
0.0006728: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `modResource`.`id`, `modResource`.`type`, `modResource`.`contentType`, `modResource`.`pagetitle`, `modResource`.`longtitle`, `modResource`.`description`, `modResource`.`alias`, `modResource`.`link_attributes`, `modResource`.`published`, `modResource`.`pub_date`, `modResource`.`unpub_date`, `modResource`.`parent`, `modResource`.`isfolder`, `modResource`.`introtext`, `modResource`.`richtext`, `modResource`.`template`, `modResource`.`menuindex`, `modResource`.`searchable`, `modResource`.`cacheable`, `modResource`.`createdby`, `modResource`.`createdon`, `modResource`.`editedby`, `modResource`.`editedon`, `modResource`.`deleted`, `modResource`.`deletedon`, `modResource`.`deletedby`, `modResource`.`publishedon`, `modResource`.`publishedby`, `modResource`.`menutitle`, `modResource`.`donthit`, `modResource`.`privateweb`, `modResource`.`privatemgr`, `modResource`.`content_dispo`, `modResource`.`hidemenu`, `modResource`.`class_key`, `modResource`.`context_key`, `modResource`.`content_type`, `modResource`.`uri`, `modResource`.`uri_override`, `modResource`.`hide_children_in_tree`, `modResource`.`show_in_tree`, `modResource`.`properties`, IFNULL(`TVimages`.`value`, '') AS `tv.images`, IFNULL(`TVprice`.`value`, '') AS `tv.price`, IFNULL(`TVprice_old`.`value`, '') AS `tv.price_old`, IFNULL(`TVprod_promo`.`value`, 'обычный') AS `tv.prod_promo` FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `TVimages` ON `TVimages`.`contentid` = `modResource`.`id` AND `TVimages`.`tmplvarid` = 5 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprice` ON `TVprice`.`contentid` = `modResource`.`id` AND `TVprice`.`tmplvarid` = 3 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprice_old` ON `TVprice_old`.`contentid` = `modResource`.`id` AND `TVprice_old`.`tmplvarid` = 4 LEFT JOIN `modx_site_tmplvar_contentvalues` `TVprod_promo` ON `TVprod_promo`.`contentid` = `modResource`.`id` AND `TVprod_promo`.`tmplvarid` = 2 WHERE  ( `modResource`.`id` IN (592,593,594,598,599,600,605,607,623,624,625,626,634,643,644,659,671,673,675,681,682,687,688,690,692,696,697,717,719,720,722,723,730,731,733,734,737,738,742,759,766,917) AND `modResource`.`parent` IN (98,4,611,3,2,7,9,88,89,90,91,92,93,94,95,832,833,834,835,836,873,874,876,877,945,705,706,707,708,709,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,860,861,862,863,864,865,866,867,868,869,870,871,872,875,878,879,880,924,939,940,941,942,943,944,859,962,8,125,146,147,80,148,79,77,149,150,151,78,597,608,609,610,612,616,617,620,621,622,628,636,651,718,728,729,735,747,748,758,761,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,881,882,883,884,923,583,584,585,586,587,588,589,590,614,689,715,749,813,814,676,684,727,740,743,591,593,602,618,629,630,634,645,769,888,889,890,896,897,898,912,914,915,946,619,627,643,739,744,745,746,781,782,783,784,785,786,787,788,789,790,791,792,793,815,822,823,824,825,826,827,828,901,917,918,919,930,931,961,595,601,605,607,613,623,625,632,633,637,639,642,660,664,671,673,675,679,685,686,696,697,702,717,721,722,723,724,725,726,730,731,733,736,751,752,753,754,755,756,757,759,762,763,764,765,767,816,817,819,820,821,829,830,831,902,903,920,925,927,928,929,932,936,954,955,956,957,963,592,596,603,606,615,624,626,631,635,641,646,659,661,680,683,687,688,703,710,711,712,713,714,716,720,732,734,737,738,741,742,750,766,780,818,900,964,905,906,907,908,81,82,83,84,85,86,87,672,691,768,779,885,886,887,894,910,911,913,916,921,922,947,948,949,950,951,952,953,582,704,891,892,893,895,899,909,958,594,598,599,600,604,638,640,644,647,648,649,650,652,653,654,655,656,657,658,662,663,665,666,667,668,669,670,674,677,678,681,682,690,692,693,694,695,698,699,700,701,719,760,770,771,772,773,774,775,776,777,778,926,933,934,935,937,938,959,960,904,96,97,120,121,122,123,124) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 AND `modResource`.`context_key` = 'catalog' )  ORDER BY modResource.publishedon DESC LIMIT 12 "
0.0073550: SQL executed
0.0003011: Total rows: 42
0.0003209: Rows fetched
0.5172410: Prepared and processed TVs
0.0026238: Loaded chunk "category_items_sale"
0.0626450: Returning processed chunks
0.6029010: Total time
20 447 232: Memory usage

Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.