2

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

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

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.