selector: get pages filtered by date weekday

[SebastianP]

Hi community,

I need help with a selector. For example I want to find all events on a monday. "event_date" is a datetime-field. 

"template=event, event_date.???=1"

"???" should represent the weekdays number of the date - is there a way to get this run? 

Thank you for any help, best regards
Sebastian

[Robin S]

You would need to use an SQL query to first get the IDs of matching pages, then use those IDs in a PW selector.

MySQL has a DAYOFWEEK() function but it starts the numbering on Sunday which is easy to forget. So probably better to use the DAYNAME() function instead.

// Get the IDs of all pages where the day name of the date value is Monday
$fieldName = 'event_date'; // The name of your date field
$tableName = "field_$fieldName"; // The table name is the field name prefixed with "field_"
$weekday = 'Monday'; // The day of the week you want to match
$stmt = $database->prepare("SELECT pages_id from $tableName WHERE DAYNAME(data) = :weekday");
$stmt->bindValue(':weekday', $weekday);
$stmt->execute();
$ids = $stmt->fetchAll(\PDO::FETCH_COLUMN);
$idsStr = implode('|', $ids);

// Find PW pages
$items = $pages->find("template=event, id=$idsStr");

 

[SebastianP]

Thank you, that's a good approach!