3

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?

8
  • Hi Lea, are you using an add on numeric field type or is it actually a text input that you only put numbers into? – UltraBob Dec 6 '12 at 1:01
  • @UltraBob I guess it must be a text input that you can put numbers into. Is there a way to define an actual numeric field type? – Lea Hayes Dec 6 '12 at 2:14
  • 1
    Hi Lea, there may be an add-on fieldtype for it, but I couldn't find one in a quick search. Using a text input is just fine as long as you input only numbers, I was just curious because a) I hadn't heard of one, b) It would actually be useful to be able to sort by a field numerically in the channel:entries tag. I may have to look into writing such a fieldtype if it doesn't exist, because it may solve an existing issue I have. – UltraBob Dec 6 '12 at 2:30
  • @UltraBob if you do write one any chance that you could post it to GitHub? – Lea Hayes Dec 6 '12 at 2:35
  • 1
    At the moment, a really simple one that came about due to this question (linked there). Most of the add-on development I've done has been for internal use, but I hope to start doing more general stuff going forward, as I get deeper into ee2. – UltraBob Dec 6 '12 at 2:40
3

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} 
8

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:

enter image description here

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
1
  • That is great Tom, I forgot or didn't know that the content setting for input fields existed. – UltraBob Dec 7 '12 at 8:34
1

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 :)

1
  • is your match and against right? match=[#24#] against={theage} – Natetronn Dec 6 '12 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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