Thread

On my site I have standings tables for football/soccer.

I have three Channels, Seasons, Cups and Fixtures each are linked by Categories.

A Season/Cup entry will have an entry date and an Expiration date for when the season starts and ends.

Each Season, Cup is made up of a number of fixture entries.

My Season works grand and starting a new season will display the correct entries between the dates for the latest season.

The Cups however will use all fixtures from each cup entry for that category.

One differenct between the two is the Categories. Seasons have only a Parent and Child

South Antrim Football League
  Premier Division

whereas the Cups have an extra level.

South Antrim Football League
  Supplementary Plate
      Group A

The code used was built for me and I am trying to get the person to looking again, but thought I would try here too.

<pre><code>
SELECT
      team.title AS team,
      team.url_title AS team_url,
      COUNT(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ THEN fixture.entry_id END) AS p,
      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 > fixture_data.field_id_51 THEN 1
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 < fixture_data.field_id_51 THEN 1
            ELSE 0 END) AS w,
      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 = fixture_data.field_id_51 THEN 1
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 = fixture_data.field_id_51 THEN 1
            ELSE 0 END) AS d,
      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 < fixture_data.field_id_51 THEN 1
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 > fixture_data.field_id_51 THEN 1
            ELSE 0 END) AS l,

      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_50
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_51
            ELSE 0 END) AS gf,

      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_51
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_50
            ELSE 0 END) AS ga,

      SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_50 - fixture_data.field_id_51
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id THEN fixture_data.field_id_51 - fixture_data.field_id_50
            ELSE 0 END) AS gd,

      (SUM(CASE WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 > fixture_data.field_id_51 THEN 3
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 < fixture_data.field_id_51 THEN 3
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_48 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 = fixture_data.field_id_51 THEN 1
            WHEN UNIX_TIMESTAMP(NOW()) > fixture.entry_date AND fixture.status = ‘open’ AND fixture_data.field_id_49 = fixture_link.rel_id AND fixture_link.rel_child_id = team.entry_id AND fixture_data.field_id_50 = fixture_data.field_id_51 THEN 1
            ELSE 0 END) - (team_data.field_id_30+team_data.field_id_53)) AS pts,
    team_data.field_id_30+team_data.field_id_53 as mpts
    FROM exp_channels ch
    INNER JOIN exp_channel_titles team ON team.channel_id = ch.channel_id AND ch.channel_name = ‘teams’

    INNER JOIN exp_channel_data team_data ON team_data.entry_id = team.entry_id

    LEFT OUTER JOIN exp_relationships fixture_link ON fixture_link.rel_child_id = team.entry_id
    LEFT OUTER JOIN exp_channel_data fixture_data ON fixture_data.field_id_48 = fixture_link.rel_id OR fixture_data.field_id_49 = fixture_link.rel_id
    LEFT OUTER JOIN exp_channel_titles fixture ON fixture.entry_id = fixture_data.entry_id
    LEFT OUTER JOIN exp_channels ch_fixture ON ch_fixture.channel_id = fixture.channel_id AND ch_fixture.channel_name=‘fixtures’

    LEFT OUTER JOIN exp_category_posts division_link ON division_link.entry_id = fixture.entry_id
    LEFT OUTER JOIN exp_categories division ON division.cat_id = division_link.cat_id
 
    WHERE division.cat_url_title = ‘{segment_3}’ AND fixture.entry_date BETWEEN IFNULL( (SELECT season_data.entry_date FROM exp_channel_titles season_data INNER JOIN exp_channels ch ON season_data.channel_id = ch.channel_id inner join exp_category_posts season_link ON season_link.entry_id = season_data.entry_id WHERE ch.channel_name = ‘seasons’  AND season_link.cat_id = division_link.cat_id and IFNULL(season_data.entry_date,0) < UNIX_TIMESTAMP(NOW())  AND IFNULL(season_data.expiration_date, 9999999999) >

One thing I had noticed was that the sql query had the seasons channel hardcoded in the join statements but even changing that hasn’t made a difference.