3

I would like to do something like this:

craft.entries.section('mySection').field1('> field2').all()

And it should return all the entries in mySection whose field1 value is greater than their field2 value.

Is it possible?

2 Answers 2

4

I agree that any solution where you run a query and then conditionally render the result isn't very efficient.

You can do this in SQL by using the where() condition.

craft.entries
    .section('mySection')
    .where('content.field_field1 > content.field_field2')
    .all()

Documentation on the where() condition can be found here.

3
  • Is the syntax for the .where() clause in your example documented anywhere? Especially the content table and and field_ prefix for custom fields. I can only find the Yii framework documentation, but of course that doesn't tell you anything about Craft's database structure, and Craft's own documentation is sorely lacking regarding those advanced queries ...
    – MoritzLost
    Apr 27, 2021 at 7:31
  • 1
    I don't believe it is documented by Craft no, their where() condition documentation links straight through to Yii, as that's where where() is implemented. Usually I reverse engineer this sort of thing using the getRawSql() function, but agree that some more advanced query examples like this could be provided within the Craft documentation. Apr 27, 2021 at 8:00
  • Yeah I agree, thanks for the reply! I'm always feeling a bit uncomfortable using undocumented code like this since it could change at any moment. Definitely something the documentation could improve on!
    – MoritzLost
    Apr 27, 2021 at 8:03
0

You could just do (Assuming your field1 and field2 are set to numbers as Field type):

craft.entries.section('mySection').all()

as your query then within your loop:

{% for entry in entries %}
    {% if entry.field1 > entry.field2 %}
        // Show the entry
    {% endif %}
{% endfor %}

It depends on what you need but this would work.

1
  • 2
    This seems horrible inefficient, return ALL entries when you only need certain ones. Is there no other way? Aug 2, 2019 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.