Query section entries based on start/end date and type

Question

I'm having the following layout on a page: a messages page that shows events and news that are grouped in a section called messages. The page starts with current events, then upcoming events and finishes with all messages (old events and news).

The queries i need to run:

Current events

• section messages
• entry type event
• where startdate is = or < now (starting today or earlier)
• and enddate is = or > now (ending today or later)
• ordered by startdate

Upcoming events

• section messages
• entry type event
• where startdate is > now (starting later than today)
• ordered by startdate

All messages

• section messages
• entry type all (event and news)
• that exclude current events
• and exclude upcoming events
• with pagination

My current code where some parts are working:

Current events

{% set entries = craft.entries.section("messages").type("event").orderBy('startDate asc').all() %}
{% for entry in entries %}
  {% if entry.startDate < now and entry.endDate > now  %}
    <h1>{{ entry.title }}</h1>
  {% endif %}
{% endfor %}

Upcoming events

{% set entries = craft.entries.section('messages').type("event").orderBy('startDate asc').all() %}
{% for entry in entries %}
  {% if entry.startDate > now %}
    <h1>{{ entry.title }}</h1>
  {% endif %}          
{% endfor %}
</section>

All messages

{% paginate craft.entries.section("messages").limit(9) as pageInfo, pageEntries %}
{% for entry in pageEntries %}
  <h1>{{ entry.title }}</h1>
{% endfor %}
<ul>
  {% for page, url in pageInfo.getPrevUrls(5) %}
    <li><a href="{{ url }}">{{ page }}</a></li>
  {% endfor %}
    <li>{{ pageInfo.currentPage }}</li>
  {% for page, url in pageInfo.getNextUrls(5) %}
    <li><a>{{ page }}</a></li>
  {% endfor %}
</ul>

Two questions i have:

How do i query current events so startDate is < AND =? And endDate is > AND =?

And how do i query all messages (news and events), excluding current and upcoming events but with past events?

Answer 1

Update

Found the solution for my first question.

For current events my working solution is:

{% set entries = craft.entries()
  .section('messages')
  .type('event')
  .startDate('<= ' ~ now|date('c'))
  .endDate('>= ' ~ now|date('c'))
  .orderBy('startDate asc')
  .all()
%}

{% for entry in entries %}
  <h1>{{ entry.title }}</h1>
{% endfor %}

For upcoming events a simpler solution is:

{% set entries = craft.entries()
  .section('messages')
  .type('event')
  .startDate('> ' ~ now|date('c'))
  .orderBy('startDate asc')
  .all()
%}

{% for entry in entries %}
    <h1>{{ entry.title }}</h1>
{% endfor %}

I can't figure out how to get my second question working.

I need to query all messages (type: news and events) and exclude current and upcoming events. The output: all messages of type news and all messages of type events that happened in the past. Including pagination.

It should be a combination of this i think:

{% set news = craft.entries()
  .section('messages')
  .type('news')
  .all()
%}

{% set events = craft.entries()
  .section('messages')
  .type('event')
  .startDate('< ' ~ now|date('c'))
  .endDate('< ' ~ now|date('c'))
  .all()
%}

Together combined with ordering by post date and pagination. Help! :-)