Element Query matrixfields where

Question

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();

Answer 1

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

Answer 2

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();