OK, When on of the site's TV value is : 10,001-20,000
and when I search the DB like:
<code>
SELECT *
FROM `modx_site_tmplvar_contentvalues`
WHERE value = '10,001-20,000'
LIMIT 0 , 30
</code>
then mysqlphpadmin returns: Showing rows 0 - 29 (324 total, Query took 0.0052 sec)
But here is a more complex sql query I logged from ajaxSearch snippet:
<code>
SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.template, sc.menutitle, sc.content, sc.publishedon, GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR "," ) AS tv_value, ntv1.value AS property, ntv2.value AS bedrooms1, ntv3.value AS condo, ntv4.value AS condorentprice, ntv5.value AS condosaleprice, ntv6.value AS location, ntv7.value AS houserentprice, ntv8.value AS housesaleprice FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid FROM `modx_site_tmplvar_contentvalues` tv ) AS ntv ON sc.id = ntv.contentid LEFT JOIN( SELECT DISTINCT tv1.contentid , tv1.value FROM `modx_site_tmplvar_contentvalues` tv1 WHERE tv1.tmplvarid = '18' ) AS ntv1 ON sc.id = ntv1.contentid LEFT JOIN( SELECT DISTINCT tv2.contentid , tv2.value FROM `modx_site_tmplvar_contentvalues` tv2 WHERE tv2.tmplvarid = '11' ) AS ntv2 ON sc.id = ntv2.contentid LEFT JOIN( SELECT DISTINCT tv3.contentid , tv3.value FROM `modx_site_tmplvar_contentvalues` tv3 WHERE tv3.tmplvarid = '15' ) AS ntv3 ON sc.id = ntv3.contentid LEFT JOIN( SELECT DISTINCT tv4.contentid , tv4.value FROM `modx_site_tmplvar_contentvalues` tv4 WHERE tv4.tmplvarid = '22' ) AS ntv4 ON sc.id = ntv4.contentid LEFT JOIN( SELECT DISTINCT tv5.contentid , tv5.value FROM `modx_site_tmplvar_contentvalues` tv5 WHERE tv5.tmplvarid = '23' ) AS ntv5 ON sc.id = ntv5.contentid LEFT JOIN( SELECT DISTINCT tv6.contentid , tv6.value FROM `modx_site_tmplvar_contentvalues` tv6 WHERE tv6.tmplvarid = '14' ) AS ntv6 ON sc.id = ntv6.contentid LEFT JOIN( SELECT DISTINCT tv7.contentid , tv7.value FROM `modx_site_tmplvar_contentvalues` tv7 WHERE tv7.tmplvarid = '21' ) AS ntv7 ON sc.id = ntv7.contentid LEFT JOIN( SELECT DISTINCT tv8.contentid , tv8.value FROM `modx_site_tmplvar_contentvalues` tv8 WHERE tv8.tmplvarid = '24' ) AS ntv8 ON sc.id = ntv8.contentid WHERE ((sc.id IN (3,464,33,477,478,479,480,7,136,138,161,847,15,551,67,10,19,9,1620,12,300,364,11,646,647,648,649,650,651,652,653,632,633,634,635,986,2,1069,1070,1152,1153,1094,27,82,25,22,26,604,1035,1802,1920,1877,1906,1982,872,955,1204,1696,400,1933,1977,273,112,288,391,1943,1332,1390,1346,24,192,190,838,128,142,143,157,169,205,234,278,312,331,455,577,349,355,419,420,517,554,555,605,679,689,707,732,752,1990,820,855,856,861,868,873,899,1829,989,1033,1203,1305,1331,1333,1377,1460,1591,1592,1617,1680,1852,1019,1886,1055,1493,1792,1903,1894,334,1356,1396,1397,1401,1494,1586,1853,1956,1960,1986,21,127,1276,602,706,809,990,1214,1265,1375,1439,1478,1560,1561,1613,1871,1880,1908,1955,1981,1636,340,519,912,939,1267,1373,1649,1677,1808,1630,1500,1515,1884,1339,1365,1809,1909,1952,660,674,1262,1340,1726,1799,1635,1638,1642,174,852,953,1730,1827,1854,1945,1641,1675,1719,1749,1504,1839,1932,1652,1918,1927,954,1352,844,970,240,805,1548,1942,784,1769,237,443,836,1385,813,1969,1976,803,357,528,1431,430,173,520,81,356,523,343,213,228,231,95,132,318,267,271,30,755,823,798,1351,909,598,1148,1834,432,1056,1667,1741,1891,1557,1650,1558,1666,31,58,120,125,98,145,146,175,184,183,194,206,208,210,221,232,1404,394,261,270,272,274,1554,313,314,324,350,358,366,375,405,1374,429,437,439,450,458,476,486,490,496,505,512,518,533,539,550,1125,610,1133,1345,641,645,664,669,676,704,720,725,728,733,756,765,771,768,782,1520,788,814,854,860,863,913,915,927,929,1519,1452,979,983,996,1018,1031,1463,1053,1060,1122,1124,1141,1147,1175,1184,1207,1256,1264,1275,1304,1353,1367,1371,1391,1407,1427,1428,1469,1483,1486,1523,1573,1574,1578,1589,1721,1759,1779,1805,1879,1605,1904,212,1030,1061,1145,1247,1451,1458,1807,1910,1915,1992,1980,28,113,248,1581,862,1140,1269,1291,1424,1482,1928,259,333,1575,1501,1885,1260,1369,1550,1555,1603,1611,1616,584,1513,1435,1569,1767,1778,1826,925,1925,330,407,1528,1698,1806,534,1665,1647,220,1115,1840,1921,1973,1961,227,1533,485,775,167,211,23,1780,235,229,524,332,402,414,217,1873,45,871,433,1098,85,1096,474,766,1526,1786,1238,1430,1717,207,310,311,155,133,103,163,182,269,209,214,219,225,257,258,260,262,275,277,279,316,341,342,373,389,422,426,493,499,508,527,570,609,1299,681,683,722,731,740,742,758,783,817,1437,867,875,876,945,951,1139,1208,1249,1261,1335,1336,1366,1379,1433,1496,1527,1544,1582,1663,1678,1697,1744,1801,265,1958,289,680,835,922,1194,1450,1570,1810,297,668,1473,1883,721,1596,1461,1546,1559,1456,1491,1643,1631,1567,1844,135,1529,1930,843,1624,494,885,529,760,532,637,640,667,1733,255,1743,491,778,131,1349,119,141,187,223,263,290,291,292,351,1475,497,501,542,560,580,746,753,799,839,883,895,921,928,956,1048,1085,1113,1246,1334,1347,1488,1614,1727,1117,1255,1784,1490,1521,975,1710,1368,1593,1543,1983,254,564,841,896,1860,488,1013,250,665,1708,670,293,353,368,369,396,440,226,245,694,699,296,359,365,435,451,1723,483,489,561,565,594,606,657,666,684,749,785,1041,857,974,1036,1342,1481,1492,1707,1747,1787,1659,829,1306,1422,1585,1704,1734,1768,750,1126,1676,1712,1859,1509,692,686,1916,1691,998,1384,1705,1738,1923,824,825,178,444,687,1163,1824,1855,1288,1987,139,1692,325,1938,710,1545,1949,1968,1644,682,575,547,559,718,786,830,870,898,908,385,586,1300,425,544,387,540,1311,247,691,804,961,339,587,1324,384,386,591,152,149,153,154,171,170,582,965,552,1322,1325,1489,1326,1327,1328,1625,1896,2003,942,980,999,1100,1221,1610,1795,1797,1815,1989,1686,302,711,1997,935,1024,1028,1455,1487,1572,1656,1757,1793,1796,1870,1905,1640,304,1510,1999,878,1001,1084,1143,1303,1329,1388,1556,1825,1864,2001,466,937,1360,1830,286,914,1026,1645,994,743,1381,831,1689,626,1330,243,463,581,390,1032,621,1416,1900,1302,1361,1756,1798,1898,1902,1292,1758,417,203,242,393,266,285,287,299,306,328,363,411,413,446,449,456,815,569,572,576,818,596,620,631,713,764,734,762,793,797,812,802,840,1626,880,890,893,910,917,984,1755,1045,1059,1363,1216,1323,1348,1376,1503,1505,1600,1606,1607,1654,1828,1775,1800,1865,1971,614,1627,1857,1102,1480,1495,1669,1670,1816,1820,1935,1890,323,1998,1058,1192,1243,1399,1534,1549,1583,1599,1608,1684,1907,1964,1967,1979,1895,2004,1301,1419,1177,1602,1702,1736,1817,1841,1893,2000,1420,1604,1899,1051,307,1205,887,973,1160,1405,1539,1701,284,1819,1965,1629,658,964,1803,888,1023,1535,1536,1537,1594,1794,1818,1972,1648,737,1011,109,811,1861,1985,1897,600,982,678,1418,1966,727,1835,1919,588,1847,1843,116,303,599,1700,1729,1842,467,795,410,1089,256,1746,1522,1750,1764,1814,1912,1888,1387,1423,1683,1901,567,1400,1295,305,110,195,197,198,201,298,969,452,461,503,1628,537,545,556,558,608,611,736,716,726,745,754,761,779,789,791,796,849,1421,865,892,931,933,938,960,968,1004,1000,1027,1050,1067,1087,1088,1101,1297,1350,1402,1415,1471,1694,1651,1655,1688,1760,1763,1774,1776,1782,1811,1832,1833,1848,1863,1867,1869,507,1576,1716,1577,1882,1849,1850,1862,1924,1383,1477,1851,1929,673,780,822,1142,1309,700,1047,1639,643,1836,1846,1838,1845,1837,1831,428,469,563,553,612,317,1731,1485,894,1414,1823,1565,1728,1777,241,447,513,578,589,1448,618,902,1017,1052,1362,1118,1156,1623,1658,1785,2005,701,1621,1466,949,702,712,1673,348,346,693,1014,1564,1674,1144,1770,1939,1947,1984,1551,1714,1038,1129,1502,1468,1584,1601,1740,1994,1634,1178,1196,1657,1662,1685,1813,1821,1875,1995,1975,1083,1552,932,1082,1646,801,833,985,1553,1804,1382,1079,1313,1690,1914,832,418,252,879,816,1511,1978,698,627,1660,688,309,468,1293,301,268,372,416,457,470,471,482,504,506,1484,1106,597,603,622,624,625,628,630,672,703,1105,724,763,735,757,810,881,934,950,981,1037,1271,1298,1453,1609,1516,1524,1531,1753,1754,1773,1917,1146,1193,1532,1579,1661,1672,1681,1703,1732,1788,1856,1876,202,1963,891,1135,1359,1462,1598,1761,1812,1881,1988,1283,1671,616,1134,1310,1687,717,907,1138,1934,1946,1633,918,1822,1595,1913,601,1005,557,930,781,1937,1889,978,1950,1020,308,619,1951,926,1722,1713,864,137,613,322,977,792,1389,959,1789,1637,1109,196,1040,448,453,462,514,579,615,617,623,769,794,828,827,886,859,869,962,1006,1116,1136,1158,1180,1294,1321,1393,1541,1679,1745,1751,1752,1858,1866,1563,1653,1682,1108,1354,1668,1016,1137,337,327,1131,1112,1063,1417,326,1566,1121,118,338,399,541,677,971,1622,642,906,1440,1441,638,1312,1015,709,180,1465,1244,1392,1765,392,88,123,124,102,147,1008,383,398,404,521,583,767,897,1715,1289,1562,1922,1996,1464,1054,1064,1065,1066,1459,1911,1957,1892,294,1664,546,1103,1099,1149,1195,1215,1372,1406,1467,1479,1878,403,738,1235,1277,1344,1590,1612,730,1499,1438,1953,585,744,924,1003,1568,1991,39,853,1791,1772,842,44,475,509,1547,729,819,1002,739,36,408,78,77,719,59,1970,837,215,222,821,997,54,916,156,96,661,1742,280,68,319,526,522,376,381,473,571,946,38,51,191,87,117,126,99,105,114,177,379,423,244,395,695,320,321,378,380,401,406,409,465,487,498,659,663,715,882,988,991,993,995,1155,1169,1274,1378,1470,1571,1762,1615,1954,1959,943,1695,1154,1182,1206,1587,1107,1457,1270,1282,1411,1720,1597,1993,1436,759,1029,1245,1434,1540,1618,1632,193,249,1580,850,957,246,362,1530,1588,1699,1241,1737,1931,360,1766,107,510,747,150,675,1718,495,79,530,415,944,1043,1709,562,1781,295,160,166,168,281,233,671,920,696,1514,1748,188,218,535,230,236,32,377,481,445,639,397,434,777,1086,162,115,185,216,224,238,834,427,538,566,590,656,874,936,947,952,1049,1429,1114,1512,1268,1355,1408,1191,1412,923,1234,1724,903,1164,472,800,904,992,1183,1426,1735,1425,776,919,1218,1525,1926,354,787,851,1039,1042,543,1007,1012,253,536,549,592,806,1948,941,963,335,705,158,2002,186,866,1220,1706,251,382,424,1790,1150,568,442,367,436,111,1476,459,484,502,548,607,629,636,685,741,748,940,1044,1341,987,1872,905,1517,1010,655,1034,1711,1771,1454,1542,148,877,884,826,697,911,1009,790,1075,1076,1078,1077,1080,1071,1072,1073,1074,1081,1518,1307,1257,1394,1266,1258,1168,1410,1171,1172,1285,1170,1179,1197,1217,1263,1279,1224,1286,1202,1230,1251,1337,239,1380,1498,1062,1239,1250,1281,1166,1167,1188,1209,1222,1236,1318,1273,1284,1444,1248,1442,1211,1237,1223,1229,1242,1287,1201,1252,1253,1278,1162,1280,1319,1357,1409,1165,1198,1413,1212,1213,1343,1176,1445,1189,1219,1228,1200,1231,1240,1173,1174,1093,1187,1190,1259,1186,1232,1104,1497,1119,1210,1225,1025,1128,1296,1308,1386,1181,1364,1130,1272,1370,1157,967,1120,1199,1046,1739,1314,1398,1316,901,948,1395,1449,1432,1057,1358,972,1227,1226,1123,1446,1151,1447,1317,1127)) AND (sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0) AND (sc.privateweb=0)) GROUP BY sc.id HAVING (FIND_IN_SET('House',property) AND FIND_IN_SET('0',bedrooms1) AND FIND_IN_SET('10,001 to 20,000',houserentprice)) ORDER BY sc.publishedon,sc.pagetitle
</code>
Nothing returned when I submit this into the same phpMyAdmin: MySQL returned an empty result set (i.e. zero rows). ( Query took 144.5573 sec )
The question is if there should be a better way to use anything for TV houserentprice instead of FIND_IN_SET('10,001 to 20,000',houserentprice) in HAVING mysql statement for getting the results?
For example the change with REPLACE in AND FIND_IN_SET('10,001to20,000',REPLACE(houserentprice, ' ', '')) didn't help.. Maybe the FIND_IN_SET has limitations or other..
I think the best way to fix is to improve FIND_IN_SET rather then edit ajaxsearch snippet's class (assets/snippets/ajaxSearch/classes/ajaxSearchRequest.class.inc.php line 59)
☆ A M B ☆