2

I have a section in Craft that has 36 fields, and I want to:

set entries = craft.entries.section(sectionName).search(searchCriteria).all()

where 'searchCriteria' searches only 6 of the 36 fields.

Craft support suggested that one way to do it might be to uncheck the "Use this field's values as search keywords?" box for the fields that I don't want to search. That approach would be useful in my situation if I wanted to exclude the 30 fields from all searches, but I want to exclude them for this search only.

Another idea they proposed was to search only certain fields by creating a more complex string for 'searchCriteria'. I wrote Twig code that successfully creates such a string. An example follows. (I added line feeds in this post for clarity, but they are not part of the generated string.)

"(stdTitle:whole OR stdTitle:blood OR stdTitle:glucose)
(testAlises:whole OR testAlises:blood OR testAlises:glucose)
(testOrderCode:whole OR testOrderCode:blood OR testOrderCode:glucose)
(testTestId:whole OR testTestId:blood OR testTestId:glucose)
(testTestingIncludes:whole OR testTestingIncludes:blood OR testTestingIncludes:glucose)
(testReferenceLabTestCode:whole OR testReferenceLabTestCode:blood OR testReferenceLabTestCode:glucose)"

As you can see, six fields with only three search terms yields quite a long string, and Craft didn't like it at all. Thinking the length may be part of the issue, I tried it with a single term ("zinc"). When searching for "zinc" only, the search string turns out to be

"(stdTitle:zinc) (testAlises:zinc) (testOrderCode:zinc) (testTestId:zinc) (testTestingIncludes:zinc) (testReferenceLabTestCode:zinc)"

Unfortunately, Craft doesn't like this one either. The status code of the response is 500, and its payload is shown below.

I also considered writing Twig code to perform each of the individual search pieces separately and then returning only the intersection of all result sets to the user, but I'm concerned about the slowness that is likely to ensue.

Has anyone else succeeded in doing such a search?

Thanks in advance for your help.

Craft's Response When Searching Six Fields for One Term ("zinc")

{"error":"An exception has been thrown during the rendering of a template 
(\"SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, 
unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'\nThe SQL being 
executed was: SELECT * FROM `craft_searchindex` WHERE MATCH(`keywords`) 
AGAINST('+\"stdtitle zinc\"* +\"testalises zinc\"* +\"testordercode zinc\"* 
+\"testtestid zinc\"* +\"testtestingincludes zinc\"* 
+\"testreferencelabtestcode zinc\"*' IN BOOLEAN MODE) AND `siteId` = 1 AND 
`elementId` IN 
(13054,13056,13058,13060,13062,13064,13066,13068,13070,13072,13074,13076,
13078,13080,13082,13084,13086,13088,13090,13092,13094,13096,13098,13100,
13102,13104,13106,13108,13110,13112,13114,13116,13118,13120,13122,13124,
13126,13128,13130,13132,13134,13136,13138,13140,13142,13144,13146,13148,
13150,13152,13154,13156,13158,13160,13162,13164,13166,13168,13170,13172,
13174,13176,13178,13180,13182,13184,13186,13188,13190,13192,13194,13196,
13198,13200,13202,13204,13206,13208,13210,13212,13214,13216,13218,13220,
13222,13224,13226,13228,13230,13232,13234,13236,13238,13240,13242,13244,
13246,13248,13250,13252,13254,13256,13258,13260,13262,13264,13266,13268,
13270,13272,13274,13276,13278,13280,13282,13284,13286,13288,13290,13292,
13294,13296,13298,13300,13302,13304,13306,13308,13310,13312,13314,13316,
13318,13320,13322,13324,13326,13328,13330,13332,13334,13336,13338,13340,
13342,13344,13346,13348,13350,13352,13354,13356,13358,13360,13362,13364,
13366,13368,13370,13372,13374,13376,13378,13380,13382,13384,13386,13388,
13390,13392,13394,13396,13398,13400,13402,13404,13406,13408,13410,13412,
13414,13416,13418,13420,13422,13424,13426,13428,13430,13432,13434,13436,
13438,13440,13442,13444,13446,13448,13450,13452,13454,13456,13458,13460,
13462,13464,13466,13468,13470,13472,13474,13476,13478,13480,13482,13484,
13486,13488,13490,13492,13494,13496,13498,13500,13502,13504,13506,13508,
13510,13512,13514,13516,13518,13520,13522,13524,13526,13528,13530,13532,
13534,13536,13538,13540,13542,13544,13546,13548,13550,13552,13554,13556,
13558,13560,13562,13564,13566,13568,13570,13572,13574,13576,13578,13580,
13582,13584,13586,13588,13590,13592,13594,13596,13598,13600,13602,13604,
13606,13608,13610,13612,13614,13616,13618,13620,13622,13624,13626,13628,
13630,13632,13634,13636,13638,13640,13642,13644,13646,13648,13650,13652,
13654,13656,13658,13660,13662,13664,13666,13668,13670,13672,13674,13676,
13678,13680,13682,13684,13686,13688,13690,13692,13694,13696,13698,13700,
13702,13704,13706,13708,13710,13712,13714,13716,13718,13720,13722,13724,
13726,13728,13730,13732,13734,13736,13738,13740,13742,13744,13746,13748,
13750,13752,13754,13756,13758,13760,13762,13764,13766,13768,13770,13772,
13774,13776,13778,13780,13782,13784,13786,13788,13790,13792,13794,13796,
13798,13800,13802,13804,13806,13808,13810,13812,13814,13816,13818,13820,
13822,13824,13826,13828,13830,13832,13834,13836,13838,13840,13842,13844,
13846,13848,13850,13852,13854,13856,13858,13860,13862,13864,13866,13868,
13870,13872,13874,13876,13878,13880,13882,13884,13886,13888,13890,13892,
13894,13896,13898,13900,13902,13904,13906,13908,13910,13912,13914,13916,
13918,13920,13922,13924,13926,13928,13930,13932,13934,13936,13938,13940,
13942,13944,13946,13948,13950,13952,13954,13956,13958,13960,13962,13964,
13966,13968,13970,13972,13974,13976,13978,13980,13982,13984,13986,13988,
13990,13992,13994,13996,13998,14000,14002,14004,14006,14008,14010,14012,
14014,14016,14018,14020,14022,14024,14026,14028,14030,14032,14034,14036,
14038,14040,14042,14044,14046,14048,14050,14052,14054,14056,14058,14060,
14062,14064,14066,14068,14070,14072,14074,14076,14078,14080,14082,14084,
14086,14088,14090,14092,14094,14096,14098,14100,14102,14104,14106,14108,
14110,14112,14114,14116,14118,14120,14122,14124,14126,14128,14130,14132,
14134,14136,14138,14140,14142,14144,14146,14148,14150,14152,14154,14156,
14158,14160,14162,14164,14166,14168,14170,14172,14174,14176,14178,14180,
14182,14184,14186,14188,14190,14192,14194,14196,14198,14200,14202,14204,
14206,14208,14210,14212,14214,14216,14218,14220,14222,14224,14226,14228,
14230,14232,14234,14236,14238,14240,14242,14244,14246,14248,14250,14252,
14254,14256,14258,14260,14262,14264,14266,14268,14270,14272,14274,14276,
14278,14280,14282,14284,14286,14288,14290,14292,14294,14296,14298,14300,
14302,14304,14306,14308,14310,14312,14314,14316,14318,14320,14322,14324,
14326,14328,14330,14332,14334,14336,14338,14340,14342,14344,14346,14348,
14350,14352,14354,14356,14358,14360,14362,14364,14366,14368,14370,14372,
14374,14376,14378,14380,14382,14384,14386,14388,14390,14392,14394,14396,
14398,14400,14402,14404,14406,14408,14410,14412,14414,14416,14418,14420,
14422,14424,14426,14428,14430,14432,14434,14436,14438,14440,14442,14444,
14446,14448,14450,14452,14454,14456,14458,14460,14462,14464,14466,14468,
14470,14472,14474,14476,14478,14480,14482,14484,14486,14488,14490,14492,
14494,14496,14498,14500,14502,14504,14506,14508,14510,14512,14514,14516,
14518,14520,14522,14524,14526,14528,14530,14532,14534,14536,14538,14540,
14542,14544,14546,14548,14550,14552,14554,14556,14558,14560,14562,14564,
14566,14568,14570,14572,14574,14576,14578,14580,14582,14584,14586,14588,
14590,14592,14594,14596,14598,14600,14602,14604,14606,14608,14610,14612,
14614,14616,14618,14620,14622,14624,14626,14628,14630,14632,14634,14636,
14638,14640,14642,14644,14646,14648,14650,14652,14654,14656,14658,14660,
14662,14664,14666,14668,14670,14672,14674,14676,14678,14680,14682,14684,
14686,14688,14690,14692,14694,14696,14698,14700,14702,14704,14706,14708,
14710,14712,14714,14716,14718,14720,14722,14724,14726,14728,14730,14732,
14734,14736,14738,14740,14742,14744,14746,14748,14750,14752,14754,14756,
14758,14760,14762,14764,14766,14768,14770,14772,14774,14776,14778,14780,
14782,14784,14786,14788,14790,14792,14794,14796,14798,14800,14802,14804,
14806,14808,14810,14812,14814,14816,14818,14820,14822,14824,14826,14828,
14830,14832,14834,14836,14838,14840,14842,14844,14846,14848,14850,14852,
14854,14856,14858,14860,14862,14864,14866,14868,14870,14872,14874,14876,
14878,14880,14882,14884,14886,14888,14890,14892,14894,14896,14898,14900,
14902,14904,14906,14908,14910,14912,14914,14916,14918,14920,14922,14924,
14926,14928,14930,14932,14934,14936,14938,14940,14942,14944,14946,14948,
14950,14952,14954,14956,14958,14960,14962,14964,14966,14968,14970,14972,
14974,14976,14978,14980,14982,14984,14986,14988,14990,14992,14994,14996,
14998,15000,15002,15004,15006,15008,15010,15012,15014,15016,15018,15020,
15022,15024,15026,15028,15030,15032,15034,15036,15038,15040,15042,15044,
15046,15048,15050,15052,15054,15056,15058,15060,15062,15064,15066,15068,
15070,15072,15074,15076,15078,15080,15082,15084,15086,15088,15090,15092,
15094,15096,15098,15100,15102,15104,15106,15108,15110,15112,15114,15116,
15118,15120,15122,15124,15126,15128,15130,15132,15134,15136,15138,15140,
15142,15144,15146,15148,15150,15152,15154,15156,15158,15160,15162,15164,
15166,15168,15170,15172,15174,15176,15178,15180,15182,15184,15186,15188,
15190,15192,15194,15196,15198,15200,15202,15204,15206,15208,15210,15212,
15214,15216,15218,15220,15222,15224,15226,15228,15230,15232,15234,15236,
15238,15240,15242,15244,15246,15248,15250,15252,15254,15256,15258,15260,
15262,15264,15266,15268,15270,15272,15274,15276,15278,15280,15282,15284,
15286,15288,15290,15292,15294,15296,15298,15300,15302,15304,15306,15308,
15310,15312,15314,15316,15318,15320,15322,15324,15326,15328,15330,15332,
15334,15336,15338,15340,15342,15344,15346,15348,15350,15352,15354,15356,
15358,15360,15362,15364,15366,15368,15370,15372,15374,15376,15378,15380,
15382,15384,15386,15388,15390,15392,15394,15396,15398,15400,15402,15404,
15406,15408,15410,15412,15414,15416,15418,15420,15422,15424,15426,15428,
15430,15432,15434,15436,15438,15440,15442,15444,15446,15448,15450,15452,
15454,15456,15458,15460,15462,15464,15466,15468,15470,15472,15474,15476,
15478,15480,15482,15484,15486,15488,15490,15492,15494,15496,15498,15500,
15502,15504,15506,15508,15510,15512,15514,15516,15518,15520,15522,15524,
15526,15528,15530,15532,15534,15536,15538,15540,15542,15544,15546,15548,
15550,15552,15554,15556,15558,15560,15562,15564,15566,15568,15570,15572,
15574,15576,15578,15580,15582,15584,15586,15588,15590,15592,15594,15596,
15598,15600,15602,15604,15606,15608,15610,15612,15614,15616,15618,15620,
15622,15624,15626,15628,15630,15632,15634,15636,15638,15640,15642,15644,
15646,15648,15650,15652,15654,15656,15658,15660,15662,15664,15666,15668,
15670,15672,15674,15676,15678,15680,15682,15684,15686,15688,15690,15692,
15694,15696,15698,15700,15702,15704,15706,15708,15710,15712,15714,15716,
15718,15720,15722,15724,15726,15728,15730,15732,15734,15736,15738,15740,
15742,15744,15746,15748,15750,15752,15754,15756,15758,15760,15762,15764,
15766,15768,15770,15772,15774,15776,15778,15780,15782,15784,15786,15788,
15790,15792,15794,15796,15798,15800,15802,15804,15806,15808,15810,15812,
15814,15816,15818,15820,15822,15824,15826,15828,15830,15832,15834,15836,
15838,15840,15842,15844,15846,15848,15850,15852,15854,15856,15858,15860,
15862,15864,15866,15868,15870,15872,15874,15876,15878,15880,15882,15884,
15886,15888,15890,15892,15894,15896,15898,15900,15902,15904,15906,15908,
15910,15912,15914,15916,15918,15920,15922,15924,15926,15928,15930,15932,
15934,15936,15938,15940,15942,15944,15946,15948,15950,15952,15954,15956,
15958,15960,15962,15964,15966,15968,15970,15972,15974,15976,15978,15980,
15982,15984,15986,15988,15990,15992,15994,15996,15998,16000,16002,16004,
16006,16008,16010,16012,16014,16016,16018,16020,16022,16024,16026,16028,
16030,16032,16034,16036,16038,16040,16042,16044,16046,16048,16050,16052,
16054,16056,16058,16060,16062,16064,16066,16068,16070,16072,16074,16076,
16078,16080,16082,16084,16086,16088,16090,16092,16094,16096,16098,16100,
16102,16104,16106,16108,16110,16112,16114,16116,16118,16120,16122,16124,
16126,16128,16130,16132,16134,16136,16138,16140,16142,16144,16146,16148,
16150,16152,16154,16156,16158,16160,16162,16164,16166,16168,16170,16172,
16174,16176,16178,16180,16182,16184,16186,16188,16190,16192,16194,16196,
16198,16200,16202,16204,16206,16208,16210,16212,16214,16216,16218,16220,
16222,16224,16226,16228,16230,16232,16234,16236,16238,16240,16242,16244,
16246,16248,16250,16252,16254,16256,16258,16260,16262,16264,16266,16268,
16270,16272,16274,16276,16278,16280,16282,16284,16286,16288,16290,16292,
16294,16296,16298,16300,16302,16304,16306,16308,16310,16312,16314,16316,
16318,16320,16322,16324,16326,16328,16330,16332,16334,16336,16338,16340,
16342,16344,16346,16348,16350,16352,16354,16356,16358,16360,16362,16364,
16366,16368,16370,16372,16374,16376,16378,16380,16382,16384,16386,16388,
16390,16392,16394,16396,16398,16400,16402,16404,16406,16408,16410,16412,
16414,16416,16418,16420,16422,16424,16426,16428,16430,16432,16434,16436,
16438,16440,16442,16444,16446,16448,16450,16452,16454,16456,16458,16460,
16462,16464,16466,16468,16470,16472,16474,16476,16478,16480,16482,16484,
16486,16488,16490,16492,16494,16496,16498,16500,16502,16504,16506,16508,
16510,16512,16514,16516,16518,16520,16522,16555,16557,16559,16561,16563,
16565,16567,16569,16571,16573,16575,16577,16579,16581,16583,16585,16587,
16589,16591,16593,16595,16597,16599,16601,16603,16605,16607,16609,16611,
16613,16615,16617,16619,16621,16623,16625,16627,16629,16631,16633,16635,
16637,16639,16641,16643,16645,16647,16649,16651,16653,16655,16657,16659,
16661,16663,16665,16667,16669,16671,16673,16675,16677,16679,16681,16683,
16685,16687,16689,16691,16693,16695,16697,16699,16701,16703,16705,16707,
16709)\")."}
Alan O'Neill
  • 21
  • 2

2 Answers2

1

If the word(s) should be in one of the fields, I think I would go about it like this:

{# Create an array of field handles #}
{% set fieldHandles = ['stdTitle', 'testAlises', 'testOrderCode', 'testTestId', 'testTestingIncludes', 'testReferenceLabTestCode'] %}

{# Create an array of search terms #}
{% set searchTerms = ['whole', 'blood', 'glucose'] %}

{% set searchString = '' %}
{% for handle in fieldHandles %}
    {% set handleLoop = loop %}
    {% for term in searchTerms %}
        {% set searchString = searchString ~ handle ~ ':"' ~ term ~ '"' %}

        {# Add the 'OR' part, but only if it's not the last occurence #}
        {% if not(handleLoop.last and loop.last) %}
            {% set searchString = searchString ~ ' OR ' %}
        {% endif %}
    {% endfor %}
{% endfor %}

This outputs:

stdTitle:"whole" OR stdTitle:"blood" OR stdTitle:"glucose" OR testAlises:"whole" OR testAlises:"blood" OR testAlises:"glucose" OR testOrderCode:"whole" OR testOrderCode:"blood" OR testOrderCode:"glucose" OR testTestId:"whole" OR testTestId:"blood" OR testTestId:"glucose" OR testTestingIncludes:"whole" OR testTestingIncludes:"blood" OR testTestingIncludes:"glucose" OR testReferenceLabTestCode:"whole" OR testReferenceLabTestCode:"blood" OR testReferenceLabTestCode:"glucose"

Which can then be used in craft.entries.search(searchString), without a DB error (at least when I tested it.

Paul
  • 6,338
  • 12
  • 26
  • Thanks! That's pretty close to what I'm trying to do, yet the resulting Boolean logic is not quite right. It's more like "if field1 contains ALL search terms OR field2 contains ALL search terms..." So I need some ANDs in there. My understanding is that AND is the default conjunction, so I wrote code to generate something like '(fld1:"x" OR fld1:"y" OR fld1:"z") (fld2:"x" OR fld2:"y" OR fld2:"z")...'. Do you know how to incorporate both ANDs and ORs with parenthetic grouping (assuming AND and OR have the same precedence)? Thanks again. – Alan O'Neill Apr 16 '19 at 12:07
  • Hm, no. My best guess would be what you did, maybe even with some ANDs in between, just to be sure. I don't see anything about grouping in the docs, though ... Guess a workaround would be to first search for ids with the all the terms in field 1, then field 2 etc, then merge ids and request the entries for those IDs. – Paul Apr 16 '19 at 12:10
0

Starting with Paul's suggestion, I have arrived at a solution that fits the bill. The following annotated Twig code contains all of the relevant details.

{#

Purpose:  To search for entries where at least one of a subset of a section's fields contain all of the search terms

Assume:   site = mnemonic of the site to search
          term = user-supplied search string

#}

{# Split the search string into an array of individual search terms #}
{%- set terms = term | split(' ') -%}

{# The names of the fields to search #}
{%- set fields = [ 'field1', 'field2', 'field3', 'field4', 'field5', 'field6' ] -%}

{# An array to hold the IDs of search result entries #}
{%- set ids = [] -%}

{# For each of the fields, conduct a search using the full set of search terms and reduce the #}
{# ids[] array so it ultimately contains the IDs of the intersection of all search result sets #}

{%- for field in fields -%}
    {# Build a search string that yields truth when all search terms are found in 'field', e.g., "field1:searchTerm1 field1:searchTerm2 field1:searchTerm3")
    {%- set search = '' -%}
    {%- for term in terms -%}
        {%- if term | length -%}
            {%- if search | length -%}{%- set search = search ~ ' ' -%}{%- endif -%}{# AND #}
            {%- set search = search ~ field ~ ':' ~ term -%}
        {%- endif -%}
    {%- endfor -%}

    {# Conduct the search #}
    {%- set entries = craft.entries.section(section).site(site).search(search).all() -%}

    {# Gather the IDs of the resulting entries into the ids[] array making sure IDs are not duplicated in the array #}
    {%- for entry in entries -%}
        {%- if (entry.id not in ids) -%}
            {%- set ids = ids | merge([ entry.id ]) -%}{# OR #}
        {%- endif -%}
    {%- endfor -%}
{%- endfor -%}

{# ids[] contains a list of IDs for entries that we want to present to the user #}
{# Search again, this time using the array of IDs to select the result set #}

{%- set entries = craft.entries.section(section).site(site).id(ids).all() -%}

{# 'entries' contains records where at least one of the searched fields contains all of the search terms #}
Alan O'Neill
  • 21
  • 2