By jimsmith on

Surely this must be possible, but so far I haven't found a way to do it...

I'm building a sports site. In it I have a content type (using CCK) called "game." It contains a date the game was played, two node reference fields (one for the home team and one for the visitor team) and two integer fields for the home and visitor scores. The node reference fields are used to select the teams from another content type called "team."

For each team I want to show a table of all games that that team plays, both home and away. It makes sense to create this table as a view (using Views, of course).

Where I run into a jam is trying to get both the home games and the visitor games of one team to show up in the table. I haven't figured out to tell the view that if Team X (as expressed by the NID of the team content type) plays either home or away to include that game in the table.

Perhaps this done by using two arguments to select the node ID of the home team and the visitor team. (It would be the same NID, but passed twice to collect both home and away games.) Unfortunately, it appears this cannot be done in the same way one might pass two taxonomy IDs in the URL, like taxonomy/4+7+9 or taxonomy/4,7,9.

So presumably Views Argument Handling Code must be used. But I cannot understand how to use it properly.

Has anyone tried something like this?

⋅ Categories: Drupal 5.x

Comments

mikeschinkel’s picture

mikeschinkel commented

Hmm. To start, I don't think putting multiple teams IDs into the URL is the right approach. Can you post a mockup table in HTML so I can make sure I understand what you are trying to do?

-Mike

jimsmith’s picture

jimsmith commented

For each team there would be a table of game results. Let's say this one is for "Valley High," which is why it appears as either the home team or the visitor team in each row.

Date          Home               H Score         Visitor                     V Score 
8/1/07       Valley High          20             Mountain High               14
8/8/07       Preppy High          0              Valley High                 41
8/15/07      Valley High          14             Big City High               21

Each row in this table comes from fields in a content type called "game." The two fields of the two team names are actually node reference fields. The nodes being referenced are from a different content type: "team." In this way, the names of the teams can link back to pages where (hopefully) this view will appear, along with additional information about the team.

I have set up a view that contains all of the above fields. It was my thought that by using two arguments -- the node reference for the home team (field_hometeam) and the (field_hometeam) and node reference for the visiting team (field_visitteam) I could show both home and away games of a particular team.

What I found was I could get a list of home games to show up using the %arg of the home team node, but not also of the visit team.

In other words, let's say in the above example, Valley High's team page is node/47. So by using view/47 or some variation, I should be able to see home and away games in the view.

As I said, I have two arguments, so by using the URL view/47/47 or view/47,47 or view/47+47 I thought I could get what I wanted. However, none of those work. I found that if I use view/47/48 I get a list of 47's home games combined with 48's away games, but that isn't helpful.

I know that multiple arguments like above work with multiple tids, but they apparently don't when referrencing multiple nids. But I don't see a way to make taxonomies work in my case because I want to link back to team pages that contain additional data on the teams.

mikeschinkel’s picture

mikeschinkel commented

The way I look at this is I start by modeling the URLs. So for your situation I would probably use:

http://www.example.com/teams/
The list of teams implemented using a View.
http://www.example.com/games/page[page]/
The paged list of games played, implemented using a View.
http://www.example.com/games/[year]/
The list of games for a year implemented using a View.
http://www.example.com/games/[year]/[month]/
The list of games for a month in that year implemented using a View.
http://www.example.com/games/[year]/[month]/[day]/
The list of games for a day in that year implemented using a View.
http://www.example.com/games/[team]/
The list of games played by a team implemented using a View.
http://www.example.com/games/[year]/[team]/
The list of games played by a team in a year implemented using a View.
http://www.example.com/games/[year]/[month]/[team]/
The list of games played by a team in a month implemented using a View.
http://www.example.com/games/[team1]/[team2]/
The list of games played by a team1 against team2 implemented using a View.
http://www.example.com/games/[year]/[team1]/[team2]/
The list of games played by a team1 against team2 in a year implemented using a View.
http://www.example.com/games/[year]/[month]/[team1]/[team2]/
The list of games played by a team1 against team2 in a month implemented using a View.
http://www.example.com/games/[year]/[month]/[day]/[home-team]-vs-[away-team]/
A game played between a home team and an away team using the Game content type.

For your example I think we are talking about this URL http://www.example.com/games/[team]/ and similar

Starting from there we work toward a solution for each. I was actually able to use a View to duplicate the following:

______________________________________________________________
| Date    | Home  H      | Score  | Visitor        | V Score | 
--------------------------------------------------------------
| 8/1/07  | Valley High  | 20     | Mountain High  | 14      |
| 8/15/07 | Valley High  | 20     | Big City High  | 14      |
--------------------------------------------------------------

Or I could get this to work with a view:

______________________________________________________________
| Date    | Home  H      | Score  | Visitor        | V Score | 
--------------------------------------------------------------
| 8/8/07  | Preppy High  | 20     | Valley High    | 14      |
--------------------------------------------------------------

But not both. Combine those two views with the View's Fusion add-in sounds like it would give you what you want, but you said Views Fusion was buggy? I can document how I got the views to work later (no time left at the moment) but it doesn't help if Views Fusion is not your solution.

BTW, I'm pretty sure that most of the permutations above should be able to be implemented with a small number of views and just a little bit of argument code.

jimsmith’s picture

jimsmith commented

You got the idea exactly right. What you said is why I thought using Views would be perfect for this and several other ways to sort the information. It allows many different ways of looking at a schedule of games and the teams involved, including league standings.

If you couldn't get both home and away games to show in the same view either, that tells me this is not simply a matter of me not seeing something I should be seeing.

I'll give Views Fusion another try. Maybe I need to freshen up my install of the other modules.

As far as using argument code, that's where I run into trouble. I have only a basic level of skill with PHP right now, but I'm trying to learn.

jimsmith’s picture

jimsmith commented

One thing I know about Views Fusion is that it relies on nodefamily module. What I'm not clear is how to apply nodefamily's parent-child relationship to a situation like mine. What should be the parent and what should be the child?

Should the parent be teams? The games are derived from two teams, of course, and it takes two parents to make a child. (This is still true, isn't it? :-) ). Or should it be the game that is the parent?

jimsmith’s picture

jimsmith commented

I abandoned my efforts to get Views to work. I really like Views, but it just will not work for my efforts to pull together a sports schedule with scores. I switched to using the teams as taxonomy terms instead of nodes, but that didn't help. I tried using Views Fusion, but I could never get it to work.

But now I'm close to what I want using a plain old PHP and MySQL to build the table. With the following code I can get a table that delivers a schedule, but unfortunately duplicates each row. I also have an incorrectly-formatted date and I'm not sure what's causing that.

<?php

  $header = array('Date', 'Home', 'H Score', 'Visitor', 'V Score');
  $rows = array();

  $results = db_query("SELECT DISTINCT g.field_date_value AS date, t.name AS hteam, g.field_homescore_value AS hscore, t.name AS vteam, g.field_visitscore_value AS vscore
            FROM {content_type_game} g, {term_data} t 
            WHERE t.tid = g.field_home_team_tid OR t.tid = g.field_visitor_team_tid 
            ORDER BY g.field_date_value ASC");

   while ($node = db_fetch_object($results)) {

   $rows[] = array(format_date($node->date, 'custom', 'F d, Y'), 
                              $node->hteam, 
                              array('data' => $node->hscore, 'align' => 'right'), 
                               $node->vteam, 
                               array('data' => $node->vscore, 'align' => 'right'));

   }
  return theme('table', $header, $rows);

?>

That delivers a table like this:

--------------------------------------------------------------------------------------------------
|     Date               |         Home	     | H Score  |         Visitor      | V Score   |
--------------------------------------------------------------------------------------------------
|December 31, 1969    |   Roadrunners  |     13       |    Roadrunners  |   14        |
|December 31, 1969   |   Bulldogs         |     13       |    Bulldogs        |    14       |
|December 31, 1969   |   Jaguars           |     32       |   Jaguars           |    19       |
|December 31, 1969   |   Wildcats          |     32       |   Wildcats         |    19       |
|December 31, 1969   |   Warriors          |      5       |    Warriors         |     7        |
|December 31, 1969   |   Panthers        |       5       |    Panthers        |     7        |
--------------------------------------------------------------------------------------------------

Obviously, I need to merge the data so that each game is listed once, not twice, and put the correct team in the Home and Visitor columns. In my above code, however, any attempts to change to a JOIN fail. Maybe I'm just been trying to do that wrong.

I'm trying to be as efficient in my code as possible, but it hasn't been easy. I'm getting down to the wire with this project and it's driving me crazy!

mikeschinkel’s picture

mikeschinkel commented

"taxonomy terms instead of nodes"

Ouch. With my strong relationship database background it hurts to just think about that.
I really wish I could help you but at the moment can't because I have three urgent client projects on my plate.

When and if I get a breather I'll help you but it won't be for at least a week.

jimsmith’s picture

jimsmith commented

I appreciate your willingness to try and I certainly understand being buried by work. I was really feeling the pressure on this task because the football season begins in a week, but thankfully, I figured it out. The solution was simple once I had a better understanding of how joins work.

I still don't know how this problem can be solved in a views argument, but I solved it by using a regular query with two left joins. For the benefit of anyone else who might be in need of such a table, or in case someone finds a better way to handle this, here's my code:

<?php

  $header = array('Date', 'Home', 'H Score', 'Visitor', 'V Score');
  $rows = array();

  $results = db_query("SELECT DISTINCT g.field_date_value AS date, n.title AS hteam, g.field_homescore_value AS hscore, nn.title AS vteam, g.field_visitscore_value AS vscore, g.field_hometeam_nid AS homenid,
g.field_visitteam_nid AS visitnid
FROM content_type_game g
LEFT JOIN node n ON (g.field_hometeam_nid = n.nid)
LEFT JOIN node nn ON (g.field_visitteam_nid = nn.nid)
ORDER BY g.field_date_value ASC");

   while ($node = db_fetch_object($results)) {

   $rows[] = array(strftime($node->date), 
                              l($node->hteam, 'node/'. $node->homenid), 
                              array('data' => $node->hscore, 'align' => 'right'), 
                               l($node->vteam, 'node/'.$node->visitnid), 
                               array('data' => $node->vscore, 'align' => 'right'));

   }
  return theme('table', $header, $rows);

?>

Actually, I said I figured it out, but that's not entirely true. I still don't understand why I'm getting December 31, 1969 dates when I try to modify the display format of the date string. Perhaps that warrants a new post in the forum.

jimsmith’s picture

jimsmith commented

change

strftime($node->date)

to

strftime( "%b %d, %Y", strtotime($node->date))