'string', 'referrer_id' => 'int', 'pageviews' => 'int', 'visitors' => 'int', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.1.0 */ protected static $fields_values = array( 'date' => '', 'referrer_id' => 0, 'pageviews' => 0, 'visitors' => 0, ); /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 5.1.0 */ public static function create_table( $connection = null ): bool { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $collate = $connection->get_charset_collate(); } } else { $collate = self::get_connection()->get_charset_collate(); } $table_name = self::get_table_name( $connection ); if ( ! is_string( $table_name ) || ! preg_match( '/^[a-zA-Z0-9_]+$/', $table_name ) ) { return false; } $wp_entity_sql = ' CREATE TABLE `' . $table_name . '` ( date DATE NOT NULL, referrer_id INT UNSIGNED NOT NULL, pageviews INT UNSIGNED NOT NULL DEFAULT 0, visitors INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (date, referrer_id), KEY `referrer_id` (`referrer_id`) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Upsert referrer stats for a given date and referrer. * * @param string $date Date in Y-m-d format. * @param int $referrer_id Referrer ID from Traffic_Referrer_Urls_Entity. * @param int $visitors Number of new visitors to add. * @param int $pageviews Number of new pageviews to add. * * @return void * * @since 5.1.0 */ public static function upsert( string $date, int $referrer_id, int $visitors, int $pageviews ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (date, referrer_id, pageviews, visitors) VALUES (%s, %d, %d, %d) ON DUPLICATE KEY UPDATE pageviews = pageviews + VALUES(pageviews), visitors = visitors + VALUES(visitors)", $date, $referrer_id, $pageviews, $visitors ) ); } /** * Get top referrers for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * @param int $limit Number of results. * * @return array * * @since 5.1.0 */ public static function get_top_referrers( string $start_date, string $end_date, int $limit = 10 ): array { $_wpdb = static::get_connection(); $stats_table = self::get_table_name( $_wpdb ); $urls_table = Traffic_Referrer_Urls_Entity::get_table_name( $_wpdb ); // Ensure both tables exist before running the JOIN. if ( ! Common_Table::check_table_exists( $stats_table, $_wpdb ) ) { self::create_table( $_wpdb ); } if ( ! Common_Table::check_table_exists( $urls_table, $_wpdb ) ) { Traffic_Referrer_Urls_Entity::create_table( $_wpdb ); } // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $sql = $_wpdb->prepare( "SELECT u.url, SUM(s.visitors) AS visitors, SUM(s.pageviews) AS pageviews FROM `{$stats_table}` s INNER JOIN `{$urls_table}` u ON s.referrer_id = u.id WHERE s.date BETWEEN %s AND %s GROUP BY s.referrer_id ORDER BY pageviews DESC LIMIT %d", $start_date, $end_date, $limit ); $_wpdb->suppress_errors( true ); $results = $_wpdb->get_results( $sql, ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { self::create_table( $_wpdb ); Traffic_Referrer_Urls_Entity::create_table( $_wpdb ); self::log_table_recreated( $stats_table ); $results = array(); } } $_wpdb->suppress_errors( false ); return $results ? $results : array(); } /** * Get top referrers grouped by domain, with per-URL details. * * Returns an array of domains with aggregated stats and a list of * individual URLs (with query params) under each domain. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * @param int $limit Number of top domains to return. * * @return array Array of [ domain => string, visitors => int, pageviews => int, urls => [ [ url, visitors, pageviews ] ] ]. * * @since 5.1.0 */ public static function get_top_referrers_grouped( string $start_date, string $end_date, int $limit = 10 ): array { $all = self::get_top_referrers( $start_date, $end_date, 200 ); // Group by domain. $groups = array(); foreach ( $all as $row ) { $host = \wp_parse_url( $row['url'], PHP_URL_HOST ); if ( ! $host ) { $host = $row['url']; } if ( ! isset( $groups[ $host ] ) ) { $groups[ $host ] = array( 'domain' => $host, 'visitors' => 0, 'pageviews' => 0, 'urls' => array(), ); } $groups[ $host ]['visitors'] += (int) $row['visitors']; $groups[ $host ]['pageviews'] += (int) $row['pageviews']; $groups[ $host ]['urls'][] = array( 'url' => $row['url'], 'visitors' => (int) $row['visitors'], 'pageviews' => (int) $row['pageviews'], ); } // Sort by pageviews descending. usort( $groups, function ( $a, $b ) { return $b['pageviews'] - $a['pageviews']; } ); return array_slice( $groups, 0, $limit ); } } }