Cannot pass where Params to query with Twig variable

Question

I have a text field that has three values on new lines.

E01001667
E01001668
E01000779

I want to use these for a query that looks like this:

{% set mylsoas =  craft.entries.
section('imdData')
.orWhere(['in', 'content.field_lsoacode', ['E01001667','E01001668','E01000779']])
.all() %}

The above query works and returns the records I want when i add the values directly. However I need to build the where params dynamically from the text field values so I am doing this:

{%  set lsoas = uentry.selectedLsoas | split("\n") %}
{% set whereParams = null %}
{# trim and join #}
{% for ons in lsoas %}
    {%  set whereParams = whereParams ~ "'"~ons~"'," %}
{% endfor %}
{% set whereParams = "[" ~ whereParams|trim(',', 'right') ~ "]" %}

The output of whereParams on screen works in the query and give me this:

{{ whereParams }}
outputs: ['E01001667','E01001668','E01000779'] 

But when i pass the variable in to the query i get a zero results - no errors.

{% set mylsoas =  craft.entries.
section('imdData')
.orWhere(['in', 'content.field_lsoacode', whereParams])
.all() %}

At a loss to get this working. Any ideas welcome:)

Answer 1

Guessing the issue is that your whereParams variable is actually a string, brackets and all – not an array. So, you'll just need to make sure that it's an actual array instead.

The good news is that you're quite close, just by doing {% set lsoas = uentry.selectedLsoas | split("\n") %} – at that point the lsoas variable is actually an array already, so in theory you should just be able to use that variable as the whereParams directly.

Something like this should work – note the |filter appended after the |split, this will remove any empty values from the array, and the |map(ons => ons|trim) part removes leading/trailing whitespace from each value:

{% set lsoas = uentry.selectedLsoas | split("\n") | filter | map(ons => ons|trim) %}

{% set mylsoas =  craft.entries
    .section('imdData')
    .orWhere(['in', 'content.field_lsoacode', lsoas])
    .all() %}

Finally, I'm not sure why you need to use orWhere() (or simply where()) in this case – you can probably just do this, which is a lot simpler:

{% set mylsoas =  craft.entries
    .section('imdData')
    .lsoacode(lsoas)
    .all() %}

Answer 2

You may be overthinking this! It's pretty rare that you'll need to reach directly for the ActiveQuery where, orWhere, and andWhere methods—unless I've misunderstood what kind of results you're going for.

There are couple unrelated issues with the sample code that I think are worth addressing, but we're going to sidestep them with the solution.

Broadly, though, it looks like what you're trying to do is replicate Twig syntax ("['one', 'two', 'three']"), rather than build up an array (['one', 'two', three']).

Documentation for .where(), describing the range of formats for conditions.

That said, Craft automatically provides some convenience methods for working with fields. In your case, I think the default behavior is enough to produce the desired results:

{% set mylsoas =  craft
  .entries()
  .section('imdData')
  .lsoacode(['E01001667', 'E01001668', 'E01000779'])
  .all() %}

This will produce an andWhere() SQL condition like content.field_lsoacode IN ('E01001667', 'E01001668', 'E01000779').

So, if you need to parameterize this:

{# Template: main.twig #}
{% set lsoas = uentry.selectedLsoas | split("\n") %}

{{ include('lsoa-list', {
  codes: lsoas,
}) }}

{# Template: lsoa-list.twig #}

{% set mylsoas =  craft
  .entries()
  .section('imdData')
  .lsoacode(codes)
  .all() %}

If you do really need the orWhere() syntax, then we may have to dive deeper into managing arrays in Twig, and check out this question and Moritz's answer about how to dynamically build queries now that field column handles include a randomized suffix.

The same parameterization seems like it'd still work, though, in this context…

{# Template: main.twig #}
{% set lsoas = uentry.selectedLsoas | split("\n") %}

{{ include('lsoa-list', {
  codes: lsoas,
}) }}

{# Template: lsoa-list.twig #}

{% set mylsoas =  craft
  .entries()
  .section('imdData')
  .orWhere(['in', 'content.field_lsoacode', codes])
  .all() %}

…because codes is already an array in the in operator format expected by where().

Just to reiterate—unless there's something really special about to your query that requires direct use of orWhere(), I would avoid it!