select
`tournament`.`id`,
`tournament`.`TourneyID` as `tourney_id`,
`tournament`.`user_id` as `usr_id`,
`tournament`.`Title` as `title`,
`tournament`.`TypeID` as `type_id`,
`tournament`.`Status` as `status`,
`tournament`.`stage_id` as `stage`,
`tournament`.`Public` as `public`,
`tournament`.`Featured` as `featured`,
`tournament`.`Location` as `location`,
`tournament`.`Elimination` as `elimination`,
`tournament`.`ReplayPack` as `replay_pack`,
`tournament`.`ReplayDownloads` as `replay_downloads`,
`tournament`.`ReplayUploads` as `replay_uploads`,
`tournament`.`MaxTeams` as `max_teams`,
`tournament`.`TeamMode` as `team_mode`,
`tournament`.`Bronze` as `bronze`,
`tournament`.`ExtendedFinals` as `extended_finals`,
`tournament`.`GroupCount` as `group_count`,
`tournament`.`group_teams_to_lb`,
`tournament`.`TeamsFromGroup` as `teams_from_group`,
date(`tournament`.`DateStart`) as `DateStart`,
`tournament`.`DateCreated` as `date_created`,
`tournament`.`DateLastView` as `date_last_view`,
`tournament`.`ViewCount` as `view_count`,
`tournament`.`PlayerPassword` as `player_password`,
`tournament`.`Hidden` as `hidden`,
`tournament`.`LayoutID` as `layout_id`,
`tournament`.`short_url`,
`tournament`.`GameCode` as `game_code`,
`tournament`.`PlatformID` as `platform_id`,
`description`.`Description` as `description`,
`layout`.`URL` as `url`,
`layout`.`Layout` as `layout`,
coalesce(
`tournament`.`NetworkID`,
`game_game`.`NetworkID`
) as `network_id`,
coalesce(
`tournament`.`PlatformID`,
`game_game`.`PlatformID`
) as `platform_id`, (
select count(*)
from TourneyTeams
where `Status` = '1'
and `tournament_id` = `tournament`.`id`
and `DisplayName` != 'FreeWin'
) as `teams_confirmed_count`, (
select count(*)
from TourneyTeams
where `tournament_id` = `tournament`.`id`
and `DisplayName` != 'FreeWin'
) as `teams_joined_count`, (
select count(*)
from TourneyTeams
where `tournament_id` = `tournament`.`id`
and `Brackets` = '1'
) as `teams_bracket_count`,
timestampdiff(second, utc_timestamp(), coalesce(`tournament`.`date_started`, `tournament`.`DateStart`)) as `seconds_left`,
`user_user`.`id` as `user_id`,
`user_user`.`Email` as `user_email`,
`user_user`.`Power` as `user_power`,
`user_user`.`CountryCode` as `user_country_code`,
`user_user_settings`.`DisplayName` as `user_display_name`,
`user_user_settings`.`FirstName` as `user_first_name`,
`user_user_settings`.`LastName` as `user_last_name`,
`user_user_settings`.`Website` as `user_website`,
`user_user_settings`.`DateJoin` as `user_date_join`,
`user_user_settings`.`TimezoneCode` as `user_timezone_code`,
`user_user_settings`.`LanguageCode` as `user_language_code`,
`user_user_settings`.`TourniesPlayed` as `user_tournaments_played`,
`user_user_settings`.`TourniesWon` as `user_tournaments_won`,
`user_user_settings`.`TourniesCreated` as `user_tournaments_created`,
`user_user_settings`.`DateLastLogin` as `user_date_last_login`,
`game_game`.`Game` as `game_game`,
`game_game`.`GameCode` as `game_game_code`,
`game_game`.`ParentID` as `game_parent_id`,
`game_game`.`DefaultChild` as `game_default_child`,
`game_game`.`ForceChild` as `game_force_child`,
`game_game`.`URL` as `game_url`,
`game_game`.`Description` as `game_description`,
`game_game`.`CreationCount` as `game_creation_count`,
`game_game`.`date_added` as `game_date_added`,
`game_game`.`has_time_leaderboard` as `game_has_time_leaderboard`,
`game_game`.`has_score_leaderboard` as `game_has_score_leaderboard`,
`game_game`.`RaceLabel` as `game_race_label`,
`game_game`.`MapLabel` as `game_map_label`,
`game_game`.`DivisionLabel` as `game_division_label`,
`game_game`.`RegionLabel` as `game_region_label`,
`game_game`.`NetworkID` as `game_network_id`,
`game_game`.`GenreID` as `game_genre_id`,
`game_game`.`RegionID` as `game_region_id`,
`game_game`.`PlatformID` as `game_platform_id`,
`game_game`.`GiantBombID` as `game_giant_bomb_id`,
`game_game`.`HasMaps` as `game_has_maps`,
`game_game`.`HasRaces` as `game_has_races`,
`game_game`.`HasRegions` as `game_has_regions`,
`game_game`.`HasDivisions` as `game_has_divisions`,
`game_game`.`HasProfiles` as `game_has_profiles`,
`game_game`.`InheritMaps` as `game_inherit_maps`,
`game_game`.`InheritRaces` as `game_inherit_races`,
`game_game`.`InheritPlatforms` as `game_inherit_platforms`,
`game_game`.`InheritRegions` as `game_inherit_regions`,
`game_game`.`InheritDivisions` as `game_inherit_divisions`,
`game_game`.`InheritProfiles` as `game_inherit_profiles`,
`game_genre`.`id` as `game_genre_id`,
`game_genre`.`genre` as `game_genre_genre`,
`game_genre`.`genre` as `game_genre_name`,
`game_genre`.`abbreviation` as `game_genre_abbreviation`, (
select sum(`CreationCount`)
from games
) as `game_total_creation_count`,
`network_network`.`NetworkID` as `network_network_id`,
`network_network`.`Network` as `network_network`,
`network_network`.`HasProfiles` as `network_has_profiles`,
`network_network`.`DisplayName` as `network_display_name`,
`network_network`.`Description` as `network_description`,
`network_network`.`giant_bomb_id` as `network_giant_bomb_id`,
`platform_platform`.`id` as `platform_id`,
`platform_platform`.`platform` as `platform_platform`,
`platform_platform`.`giant_bomb_id` as `platform_giant_bomb_id`,
`platform_platform`.`disabled` as `platform_disabled`
from `TourniesIndex` as `tournament`
left join `TourneyDescriptions` as `description`
on `description`.`tournament_id` = `tournament`.`id`
left join `Layouts` as `layout`
on `layout`.`LayoutID` = `tournament`.`LayoutID`
left join `users` as `user_user`
on `user_user`.`id` = `tournament`.`user_id`
left join `user_settings` as `user_user_settings`
on `user_user_settings`.`id` = `user_user`.`id`
left join `games` as `game_game`
on `game_game`.`GameCode` = `tournament`.`GameCode`
left join `game_genres` as `game_genre`
on `game_genre`.`id` = `game_game`.`GenreID`
left join `game_networks` as `network_network`
on `network_network`.`NetworkID` = coalesce(
`tournament`.`NetworkID`,
`game_game`.`NetworkID`
)
left join `game_platforms` as `platform_platform`
on `platform_platform`.`id` = coalesce(
`tournament`.`PlatformID`,
`game_game`.`PlatformID`
)
WHERE tournament.Deleted = 0
and (
tournament.Public = 1
)
ORDER BY tournament.DateStart DESC
LIMIT 0, 15
|