Hey Ernest,
So an update on this one, generally, this is working as expected for existing media items, so thanks!!
I have modified this to also add a second acf field that also has to be there else no index. What I have noticed though, was some new media items were showing in the index when they didnt have those flags selected. I will add, we add these media items via the rest API, I wondering if there’s some funny timing things happening? I think with the rest scripts, the initial item is added, then we do a second pass to add the acf values, not sure if relevant, but thought I would add that detail. Let me know what you think.
add_filter(
'asp/index/database/get_posts_to_index/query/add_where',
function ( $add_where ) {
global $wpdb;
return $add_where . " AND (
( post.post_type='attachment' AND EXISTS(
SELECT 1 FROM $wpdb->postmeta pm1
WHERE pm1.post_id=post.ID
AND pm1.meta_key='available'
AND pm1.meta_value=1
) AND EXISTS(
SELECT 1 FROM $wpdb->postmeta pm2
WHERE pm2.post_id=post.ID
AND pm2.meta_key='web_status'
AND pm2.meta_value IN ('Available', 'Under Consideration')
))
OR post.post_type <> 'attachment'
)";
}
);