<p>When joining the tables you get more results like you mentioned in Edit2.
First pick distinct ids, to use later on in the main query.</p>

<p>I came up with this:</p>

<pre><code>$timeSlotIds = Entry::find()
    -&gt;section('activities')
    -&gt;innerJoin('matrixblocks', 'matrixblocks.ownerId = elements.id AND (SELECT enabled FROM elements WHERE matrixblocks.id = elements.id) = 1')
    -&gt;innerJoin('matrixcontent_timeslots', 'matrixcontent_timeslots.elementId = matrixblocks.id')
    -&gt;where([
        '&lt;=', 
        'matrixcontent_timeslots.field_timeSlot_startDate', 
        new Expression('NOW()')
    ])
    -&gt;andWhere([
        '&gt;=', 
        'matrixcontent_timeslots.field_timeSlot_endDate', 
        new Expression('NOW()')
    ])
    -&gt;distinct()
    -&gt;ids();

$query = Entry::find()
  -&gt;section('activities');
  -&gt;where(['in', 'elements.id', $timeSlotIds])
  -&gt;all();
</code></pre>

CC BY-SA 4.0
2018-06-06T21:17:12.730Z
2018-06-22T12:11:58.050Z
2018-06-22T12:11:58.050Z
Jeroen
<p>You need to <a href="https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#join" rel="nofollow noreferrer">join</a> the <code>matrixblocks</code> and the specific content table</p>

<pre><code>$query-&gt;join('{{%matrixblocks}}, 'matrixblocks.ownerId = elements.id);
$query-&gt;join('{{%matrixcontent_your_table_name}}, 'matrixcontent_your_table_name.elementId = matrixblocks.id);
$query-&gt;andWhere([
    '&gt;=', 
    'matrixcontent_your_table_name.name-of-the-column', 
    new Expression('NOW()')
]);
</code></pre>

<p>Edit: if you have multiple blocks with the same field handle you'll need to wrap all those in a <code>new OrCondition</code></p>

<pre><code>$now = new Expression('NOW()');
$query-&gt;andWhere(
    new OrCondition([
        ['&gt;=', 'column1', $now],
        ['&gt;=', 'column2', $now],
        ['&gt;=', 'column3', $now],
    ])
);
</code></pre>

<p>Edit2: I would suggest you to only fetch ids in that query because it's likely to have more results when you join these tables</p>

CC BY-SA 4.0
2018-06-06T12:46:11.080Z
2018-06-06T12:57:15.960Z
2018-06-06T12:57:15.960Z
Robin Schambach
<p>I'm trying to query entries in a section where the (activities) entry has an item in a matrix field timeSlots -> timeSlot -> startDate.
The startDate must be >= NOW().</p>

<pre><code>$query = Entry::find()-&gt;section('activities');

// something like 
// $query-&gt;where('timeSlots.*.startDate &gt;= NOW()');

$activities = $query-&gt;all();
</code></pre>

<matrix><query><elementquery><join>
Element Query matrixfields where
# Element Query matrixfields where

(Asked by: username_0 on 2018-06-06)

I'm trying to query entries in a section where the (activities) entry has an item in a matrix field timeSlots -> timeSlot -> startDate.
The startDate must be >= NOW().
$query = Entry::find()->section('activities');

// something like 
// $query->where('timeSlots.*.startDate >= NOW()');

$activities = $query->all();

**Tags:** matrix, query, elementquery, join


---

## 2 answers

### Answer by username_0 on 2018-06-06. Score: 3

When joining the tables you get more results like you mentioned in Edit2.
First pick distinct ids, to use later on in the main query.
I came up with this:
$timeSlotIds = Entry::find()
    ->section('activities')
    ->innerJoin('matrixblocks', 'matrixblocks.ownerId = elements.id AND (SELECT enabled FROM elements WHERE matrixblocks.id = elements.id) = 1')
    ->innerJoin('matrixcontent_timeslots', 'matrixcontent_timeslots.elementId = matrixblocks.id')
    ->where([
        '<=', 
        'matrixcontent_timeslots.field_timeSlot_startDate', 
        new Expression('NOW()')
    ])
    ->andWhere([
        '>=', 
        'matrixcontent_timeslots.field_timeSlot_endDate', 
        new Expression('NOW()')
    ])
    ->distinct()
    ->ids();

$query = Entry::find()
  ->section('activities');
  ->where(['in', 'elements.id', $timeSlotIds])
  ->all();


---

### Answer by username_1 on 2018-06-06. Score: 3

You need to join the matrixblocks and the specific content table
$query->join('{{%matrixblocks}}, 'matrixblocks.ownerId = elements.id);
$query->join('{{%matrixcontent_your_table_name}}, 'matrixcontent_your_table_name.elementId = matrixblocks.id);
$query->andWhere([
    '>=', 
    'matrixcontent_your_table_name.name-of-the-column', 
    new Expression('NOW()')
]);

Edit: if you have multiple blocks with the same field handle you'll need to wrap all those in a new OrCondition
$now = new Expression('NOW()');
$query->andWhere(
    new OrCondition([
        ['>=', 'column1', $now],
        ['>=', 'column2', $now],
        ['>=', 'column3', $now],
    ])
);

Edit2: I would suggest you to only fetch ids in that query because it's likely to have more results when you join these tables


---

