How to filter channel by numeric field?

Question

If I have a channel with the custom field "Age" (numeric type) how can I filter the channel so that only entries with "Age" >= 24 are shown?

Likewise what would be the way to define and filter a custom field using a date?

Would this require custom SQL statements or could this be achieved using the exp:channel:entries tag?

Answer 1

I don't think either of these things an be accomplished through the exp:channel:entries tag. I'm sure you could accomplish both of these things through the exp:query tag

as an example of filtering based on a field I know contains only numbers:

{exp:query limit="5" sql="SELECT title, entry_date, field_id_5 
  FROM exp_channel_titles AS titles, exp_channel_data AS data 
  WHERE titles.entry_id = data.entry_id AND titles.entry_id > 900"}
    <p>{title} - {entry_date format="%Y %m %d"} - {field_id_5}</p>
{/exp:query}

On your second question, there is a date fieldtype, in ee but it doesn't allow for sorting based on that date in the channel:entries tag, just displaying the output formatted as a date. To filter using the queries tag on dates, you need to be aware that the dates are stored as a unix timestamp. If you wanted to query on your custom date field (here field_id_262) being between 14 and 7 days ago:

{exp:query sql="SELECT titles.title, data.field_id_262 
  FROM exp_channel_titles AS titles, exp_channel_data AS data 
  WHERE titles.entry_id = data.entry_id 
  AND data.field_id_262 > ((unix_timestamp(Now()))-(14*86400)) 
  AND data.field_id_262 < ((unix_timestamp(Now()))-(7*86400))"}
    <p>{title} - {field_id_262 format="%Y %m %d"}</p>
{/exp:query} 

Answer 2

A quick addendum to the other answers (which are both fine for reading back values): it is possible (and often a good idea) to change a text input field's allowed content from the default "all" to "integer", "number", or "decimal". You can do this on the edit field screen:

This is important to do if you then want to filter/sort values numerically rather than alphanumerically, ie if you want the set [32,3,133] to be sorted as [3,32,133] and not as [133,3,32]. It's also important for ranges, as selecting < 4 from the above range will select [3,32,133] if the field's content type is 'all' but [3] if the content type is 'integer'

Internally these values map to MySQL's TEXT ('all'), INT ('integer'), NUMERIC ('number') and DECIMAL ('decimal') datatypes.

When bypassing the channel:entries tag and pulling data direct from the DB, I tend to use the ActiveRecord plugin rather than the query tag as:

• It's far more secure if you're inserting dynamic data into your query, eg from url segments (the query tag does not escape variables, whereas the AR plugin does).
• It syntax is much more readable, which helps you (or another developer) to work out what you was trying to do when you return to the code months later
• There's no need to use prefixes (exp_) when selecting tables
• It supports pagination

Answer 3

Whilst a custom query is a good solution, you could also look to stash!

//This reads all your entries and records them as a list
{exp:stash:set_list name="my_entries" parse_tags="yes"}
{exp:channel:entries channel="my_channel"}

    {stash:entry_title}{title}{/stash:entry_title}
    {stash:entry_id}{entry_id}{/stash:entry_id}
    {stash:theage}{my_age_field}{stash:theage}

{/exp:channel:entries}
{/exp:stash:set_list} 

//Using the list captured above, match an age (could be a segment as well) against
the age captured
{exp:stash:get_list name="my_entries" match=[#24#] against={theage} limit="10"}
    <p><a href="/my_template/{entry_id}">{title}</a></p>
    <p>This is item {count} of {total_results} rows curently being displayed.</p>
    <p>This is item {absolute_count} of {absolute_results} rows saved in this list</p>
{/exp:stash:get_list}

Just putting it out there :)