Sample Timeslot Rules

Timeslot Rules fall somewhere between normal SQL and Views.  After the list of the shows that could potentially be scheduled is generated, additional processing is done to rule shows out from the list to be scheduled based on other settings like it aired since the Days Since Last Airing setting, a show from the same project had already been scheduled, or this show is rated Adult and being scheduled within a block that airs at a time adult content isn't allowed.

Before reading the rest of this page, it would be helpful to read Wikipedia article about JOINS.

Many of the example queries use the {tablename} syntax.  This isn't required.  The reason is for compatibility with Drupal's Database Abstraction Layer.  If you want to preview the query using a tool like phpmyadmin, you would need to remove the {} before running the query.

POST SQL PROCESSING OPTIONS:

Days Since Last Airing - This eliminates shows as candidates to be scheduled based on the latest om_airing nodes.  This will eliminate both shows that have aired X days ago as well as shows scheduled to air in the future.

Do Not Air Shows from the Same Project Concurrently - This prevents shows form the same project from running back to back.  Useful when ordering by the date the show was added.  If a member adds 10 shows at once, only the one show would air each time the Rule was processed.  If another member added show after the first 5, the 6th show from the first project would air as well.

Adult Content - The hours adult content is removed from the pool of schedulable shows isn't configured on Timeslot Rule or Events, but is processed if hours are set in the Admin > Open Media > Scheduling Configuration.

If the Limit Pool by Rating, this must be included in the SQL result.  Add this join to your SQL...

JOIN content_field_om_rating  ON content_field_om_rating.vid = <vid of original table>

Do Not Air Shows from the Same Project in Rule - This is a more restrictive configuration and should only be used by stations with a large archive.

If the Limit Rules to Scheduling by Project the Organic Group must be included in the SQL result.  Add this join to your SQL...

JOIN {og_ancestry} ON og_ancestry.nid = <nid of original table>

Custom Variables:

Anyone familiar with Views will recognize this syntax.  While the longer term goal is to generate Rule from Views, this release of om_timeslot_scheduler lays the foundation for that development.

***CURRENT_THEME*** - When the Rule is processed, this code is replaced by the nid of the Theme (or Paired Theme when using om_timeslot_pairings) Attempting to Preview a Rule that utilizes this variable directly will return this error...

This Rule utilizes a Theme. Results can only be previewed from a Timeslot Theme or Event.

It will also return a list of Timeslot Themes that can be used to preview the Rule's results.

***NOW_-7_DAYS*** - This variable will be converted to a time using PHP's strtotime function.  Any string PHP can process can be used; -1_Week, Last_Monday, ect.

SQL to Order by Alternative Ratings:

Rules using the Bayesian score provided by alternative_ratings module should add this SQL...

JOIN {alternative_rating} ON alternative_rating.nid = node.nid

and...

ORDER BY score

SQL to Order by Voting API averages:

This SQL will return the shows rated 5 of 5 if you are using five stars or thumbs up/like with that configuration where the vote was made in the last 7 days.  This creates a nearly immediate feedback for viewers who rate shows.  If they rate a show, it will very likely be scheduled the next time Rule is processed.

WHERE field_om_show_theme_nid = ***CURRENT_THEME*** AND value = 100 AND votingapi_vote.timestamp > ***NOW_-7_DAYS*** GROUP BY nid ORDER BY total_votes

SQL for Limits:

Limiting the number of results is important for performance.  The number of shows that should be returned in a query varies based on the duration of the Timeslot Event, number of Rules applied to an Event, and amount of content matching that criteria that will be ruled out based on post SQL processing.  The SQL for a limit is...

LIMIT 0, 100

SQL for Random:

When getting started with Rule based scheduling, Random is useful option to add variety to a small amount of content.  To randomize the results, simply add this ORDER BY statement to your query...

ORDER BY RAND()

SAMPLE RULES:

Locally Produced - Top-rated by Alternative Rating

SELECT *  FROM {content_field_om_locally_produced}
JOIN {node} ON content_field_om_locally_produced.vid = node.vid
JOIN {og_ancestry} ON og_ancestry.nid = node.nid
JOIN {alternative_rating} ON alternative_rating.nid = node.nid
WHERE field_om_locally_produced_value = 1 AND status = 1
ORDER BY score DESC
LIMIT 0, 100

Because that Rule doesn't rely on a theme, it is standard SQL.  A more complicated Rule would be...

Most Popular for Theme That Hasn't Aired in 14 Days

SELECT * FROM {content_field_om_theme}
JOIN {node} ON content_field_om_theme.vid = node.vid
JOIN {og_ancestry} ON og_ancestry.nid = node.nid
JOIN {alternative_rating} ON alternative_rating.nid = node.nid
WHERE field_om_theme_nid = ***CURRENT_THEME*** AND status = 1
ORDER BY score DESC
LIMIT 0, 100

This Rule utilizes both the Days Since Last Airing option and the ***CURRENT_THEME*** variable in the SQL.  The same SQL could be used with a higher Days Since Last Airing number and run after the 14 day version.  Because using the Days Since Last Airing option rules out shows based on both past and future airings, shows scheduled in the first rule would not be scheduled by the 21 or 30 day version of the Rule.

Random Shows in Theme

SELECT * FROM {content_type_om_show}
JOIN {node} ON node.vid = content_type_om_show.vid
JOIN {content_field_om_theme} ON content_field_om_theme.vid = content_type_om_show.vid
WHERE field_om_show_mpeg2_fid IS NOT NULL AND node.status = 1 AND field_om_theme_nid = ***CURRENT_THEME***
ORDER BY RAND()