1

I am trying to

  1. filter and sort show entries by what time they begin
  2. display the current show

I have two fields, startTimeand endTime, and while Craft lets me display only the time part in the Control Panel, I can't filter entries in my template by the time part only.

Listing out entries:

{% set shows = craft.entries({
    section: 'shows',
    order: 'startTime asc',
    day: 'sunday',
}) %}

Displaying the current show:

{% set nowPlaying = craft.entries({
    section: 'shows',
    startTime: '<= ' ~ rightNow | date('c'),
    endTime: '>' ~ rightNow | date('c'),
    day: rightNow | date('l'),
}) %}

If I create all my show entries on the same day, filtering by the whole date is fine, but that's assuming

  1. the times are never changed (thus updating the date)
  2. I don't care to know what show is currently playing (because a show will only show up if I created it today).

I prefer not to reinvent the wheel by making a template-side sorting algorithm. Is there not a more elegant way to query just the time part in Craft filtering?

1
  • To clarify, day is a related category in the show entry. – Joshua Dec 27 '17 at 16:43
2

For those interested, I found out you can tell Craft to search the database for the time part only with the mySQL command time() (Order entries by custom time field). For example:

{% set shows = craft.entries({
  section: 'shows',
  relatedTo: day.id,
  order: "time(startTime)",
}) %}

However, this sorts based on the absolute DB time (GMT time), so I substituted a conversion command for startTime:

{% set convertedStartTime = 'convert_tz(startTime,"+00:00","-06:00")' %}

{% set shows = craft.entries({
  section: 'shows',
  relatedTo: day.id,
  order: "time(#{convertedStartTime})",
}) %}

To my complete shock, this actually works. I could not find a similar way to search for the current show, so I first filtered entries by day, then ran a for-loop to find the current show (there's only ten shows or so per day):

{% set todaysShows = craft.entries({
  section: 'shows',
  relatedTo: today,
}) %}

{% for show in todaysShows if not done %}
    {% set start = show.startTime | date_modify('now') %}
    {% set end = show.endTime | date_modify('now') %}

    {% if now >= start and now < end %}
        {% set nowPlaying = show %}
        {% set done = true %}
    {% endif %}

  {% endfor %}`
1

If your day field is a relation field you should search for your related entries with

{% set today = insertYourLogicToFetchTheCurrentDay %}
{# eg set todayName = "now"|date("l")#}
{# set today = craft.categories.title(todayName).first() #}
{% set showsToday = craft.entries.section('shows').relatedTo(today).find() %}

I don't really understand the second part of your question to be honest.

Is your rightNow a valid DateTime object? Normally I would do it like that

{% set startDate = now|date('U')%}
{% set endDate = now|date('U') %}
{% set entries = craft.entries.section('shows').startTime('>='~startDate).endDate('<=' ~ endDate).find() %}

Edit

Okay: based on our comments this solution could work (at least from my test) but this is just an example what you could do: i did no performance tests and it looks a little bit ugly since I did the "fast" way not the "I'm using craft styles and craft macros to make everything look good". You would have to extend it.

  1. Go to Pluginfactory and fill out your fields. Turn on the light switch for FieldTypes
  2. Download the files and move it to your Craft Plugins folder. You Plugin name should have the same name like your folder in plugins. So when you name your Plugin MyPlugin your folder is /craft/plugins/myplugin/
  3. Go to the generated file /fieldtypes and overwrite these functions

    /**
     * Returns the input value as it should be saved to the database.
     *
     * @param mixed $value
     * @return mixed
     */
    public function prepValueFromPost($value)
    {
        return $value['hours'] . $value['minutes'];
    }
    
    /**
     * Prepares the field's value for use.
     *
     * @param mixed $value
     * @return mixed
     */
    public function prepValue($value)
    {
        if(empty($value)){
            return [
                'hours' => '00',
                'minutes'   => '00'
            ];
        }
        return [
            'hours' => substr($value, 0, 2),
            'minutes' => substr($value, 2, 4),
        ];
    }
    
  4. There will be a template in your /templates/field/...twig folder change the input to something like this... I know this looks awful... you should style it and implement some logic to preselect fields and such

    <select name="{{ name ~ '[hours]' }}">
        <option value="00">00</option>
        <option value="01">01</option>
        <option value="02">02</option>
        <option value="03">03</option>
        <option value="04">04</option>
        <!-- and so on -->
    </select>
    selected = {{ values.hours }}
    
    <select name="{{ name ~ '[minutes]' }}">
        <option value="00">00</option>
        <option value="01">01</option>
        <option value="02">02</option>
        <option value="03">03</option>
        <option value="03">03</option>
        <!-- and so on -->
    </select>
    selected = {{ values.minutes }}
    
  5. Change your two fields to the new field type. That way your db values will look like 0030 for 00:30 or 1340 for 13:40 you can now display the values in your frontend with

    {% for entry in craft.entries.section('shows') %}
        {{ entry.startTime.hours }}:{{ entry.startTime.minutes }}<br>
        {{ entry.enddate.hours }}:{{ entry.e.minutes }}<br>
        {% set startDate = entry.startTime.hours ~ entry.startTime.minutes %}
        {% set endDate = entry.endTime.hours ~ entry.endTime.minutes %}
        {% if startDate >= 0430 and endDate <= 1340   %}
            The show is between 04:30 and 13:40
        {% endif %}
    {% endfor %}
    

    Thus you can search via

    {% set now = "now"|date('H') ~ "now"|date('i') %}
    {% set entries = craft.entries.section('shows').relatedTo(day).find({
        'startdate': '>='~now,
        'enddate': '<='~now
    }) %}
    
5
  • Yes, rightNow is a dateTime object formatted as locale time (i.e. 4:00 PM). To clarify, these shows are date agnostic, meaning they happen at the same time every week, for instance 4-5 PM every Wednesday. (I turned the day into own category is because it makes the craft.entries search process easier.) Unfortunately, if I query for entries using just the time part (4:00 PM), Craft filtering assumes a full date (today at 4 PM), which throws off both the current show and and the sorting. Is that making more sense? – Joshua Dec 27 '17 at 17:43
  • Ah now I see. I'm not sure if there is a good way to achieve this only with twig. I would probably build my custom Query in PHP and use a custom field type and no date field since you don't need the date anyway. But this can take some time to achieve if you are new to Craft and my experience is: most people don't want to use php. Let me know whether I should provide some code or not – Robin Schambach Dec 27 '17 at 17:59
  • Thanks Robin! That's what I was afraid of. I've not yet explored the dark ways of PHP/custom plugin path yet because we push stuff out so quickly :P. I could do always do a regular number field and use military time, though that's less idiomatic from a client perspective. Appreciate your time! – Joshua Dec 27 '17 at 18:09
  • Please check my edit. If you want to have only one select box for hours and minutes just remove the other select and input values like 0000, 0030, 0100 with the corresponding display values 00:00, 00:30, 01:00 and so on. You can inspect the Crafts html or use the macro to generate your inputs and make them look good – Robin Schambach Dec 27 '17 at 19:20
  • Thanks so much Robin! I found out afterwards that we couldn't go with the military time option, but I did find a neat solution I thought I'd post. See below. – Joshua Dec 27 '17 at 22:50

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.