Need help with argument

Cool, thanks. I've got the artist name working. Now I'm working on getting all the aggregator2_audio records for a specific agg2 feed id. The agg2_audio records are tied to the agg2_item records which have a fid in them.

I need help laying out the the tables structure.

Here is what I have so far:

function mp3pig_views_tables() {
  $tables['aggregator2_audio'] = array(
    'name' => 'aggregator2_audio',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  /* TODO: Hopefully you can see the table layout from the following: 
   * 
   * SELECT * FROM {aggregator2_audio} a 
   * LEFT JOIN {node} n ON (a.nid = n.nid) 
   * LEFT JOIN {aggregator2_item} ai ON (ai.nid = a.item_nid) 
   * LEFT JOIN {aggregator2_feed} af ON (af.nid = ai.fid) 
   * WHERE af.nid = <argument feed id>
   * 
   * I'm not sure how to lay the table structure out here. I am assuming that I
   * could just have one item in $tables describing this complex join?
   */ 
  return $tables;
}

function mp3pig_views_arguments() {
  $arguments = array(
    'artist' => array('name' => t('MP3PIG: Artist Name'), 'handler' => 'mp3pig_handler_arg_artist'),
    'fid' => array('name' => t('MP3PIG: Aggregator2 Feed ID'), 'handler' => 'mp3pig_handler_arg_fid'),
  );
  return $arguments;
}

function mp3pig_handler_arg_artist($op, &$query, $argtype, $arg = '') {
  switch ($op) {
    case 'summary':
      $query->ensure_table('aggregator2_audio');
      $query->add_field('artist', 'aggregator2_audio');
      $fieldinfo['field'] = 'aggregator2_audio.artist';
      return $fieldinfo;
      break;
    case 'filter':
      if ($arg == '(no artist)') {
        $arg = '';
      }
      $query->ensure_table('aggregator2_audio');
      $query->add_where("aggregator2_audio.artist LIKE '$arg'");
      break;
    case 'link':
      if ($query->artist == '') {
        $query->artist = '(no artist)';
      }
      return l($query->artist, $arg . '/' . drupal_urlencode($query->artist));              
    case 'title':
      return $query;
  }
}
/*
 * TODO: This probably needs some work
 */
function mp3pig_handler_arg_fid($op, &$query, $argtype, $arg = '') {
  switch ($op) {
    case 'summary' :      
      $query->add_field('title');
      $fieldinfo['field'] = 'node.title';
      return $fieldinfo;
      break;
    case 'filter' :
      $nid = intval($arg);
      $query->add_where("node.nid = $nid");
      break;
    case 'link' :      
      return l($query->title, "$arg/$query->nid");              
    case 'title':
      $node = db_fetch_object(db_query('SELECT title FROM {node} WHERE nid = %d', $query));
      return $node->title;
  }
}

P.S. I haven't gone through filtering the args, but it's on the TODO so don't worry about that.

Here is a general example of what I would do directly:

I have my module table aggregator2_audio which stores mp3 info
and ties to the following fields:

1. aggregator2_audio.nid ==== node.nid
2. aggregator2_audio.item_nid ==== aggregator2_item.nid (to see in which agg2 item this mp3 was found)

Then each agg2 item is tied to a agg2 feed:

1. aggregator2_item.fid ==== aggregator2_feed.nid

So I want to find all aggregator2_audio records for a specific feed.

Something like this:

SELECT a.nid, a.url_to_mp3_file, a.artist, a.track FROM {aggregator2_audio} a 
LEFT JOIN {aggregator2_item} ai ON (a.item_nid = ai.nid) 
LEFT JOIN {aggregator2_feed} af ON (ai.fid = af.nid) 
WHERE af.nid = feed_id_from_argument

Obviously, we could remove the join to the agg2_feed table, but I thought that might help illustrate my point. Really, it would be this:

SELECT a.nid, a.url_to_mp3_file, a.artist, a.track FROM {aggregator2_audio} a 
LEFT JOIN {aggregator2_item} ai ON (a.item_nid = ai.nid) 
WHERE ai.fid = feed_id_from_argument

Still getting this when going to audio_by_feed/4936

Here is the relevant section of the code:

function mp3pig_views_tables() {
  $tables['aggregator2_audio'] = array(
    'name' => 'aggregator2_audio',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  $tables['aggregator2_item'] = array(
    'name' => 'aggregator2_item',
    'join' => array(
      'left' => array(
        'table' => 'aggregator2_audio',
        'field' => 'item_nid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  $tables['aggregator2_feed'] = array(
    'name' => 'aggregator2_feed',
    'join' => array(
      'left' => array(
        'table' => 'aggregator2_item',
        'field' => 'fid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  return $tables;
}

function mp3pig_handler_arg_fid($op, &$query, $argtype, $arg = '') {
  switch ($op) {
    case 'summary':
      $query->ensure_table('aggregator2_feed');
      $query->add_field('nid', 'aggregator2_feed');      
      $fieldinfo['field'] = 'aggregator2_feed.nid';
      return $fieldinfo;
      break;
    case 'filter' :
      $fid = intval($arg);
      $query->ensure_table('aggregator2_feed');
      $query->add_where('aggregator2_feed.nid = %d', $fid);
      break;
    case 'link' :      
      return l($query->title, $arg .'/'. $query->fid);              
    case 'title':
      $node = db_fetch_object(db_query('SELECT title FROM {node} WHERE nid = %d', $query));
      return $node->title;
  }
}

This is the MySQL error: Unknown table 'aggregator2_feed' in where clause query: SELECT node.nid FROM drupal_node node WHERE (node.status = '1') AND (aggregator2_feed.nid = 4936) ORDER BY node.created DESC LIMIT 0, 36

@merlin I got it working. I had some test data in the db that seemed to cause the error. Thanks for all your help. Here is the working views code.

/**
 * Views: Construct the table relationships for MP3PIG audio and Agg2 nodes.
 */
function mp3pig_views_tables() {
  $tables['aggregator2_audio'] = array(
    'name' => 'aggregator2_audio',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  $tables['aggregator2_item'] = array(
    'name' => 'aggregator2_item',
    'join' => array(
      'left' => array(
        'table' => 'aggregator2_audio',
        'field' => 'item_nid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  $tables['aggregator2_feed'] = array(
    'name' => 'aggregator2_feed',
    'join' => array(
      'left' => array(
        'table' => 'aggregator2_item',
        'field' => 'fid',
      ),
      'right' => array(
        'field' => 'nid',
      ),
    ),
  );
  
  return $tables;
}

/**
 * Views: Register the handlers for the arguments.
 */
function mp3pig_views_arguments() {
  $arguments = array(
    'artist' => array('name' => t('MP3PIG: Artist Name'), 'handler' => 'mp3pig_handler_arg_artist'),
    'fid' => array('name' => t('MP3PIG: Aggregator2 Feed ID'), 'handler' => 'mp3pig_handler_arg_fid'),
  );
  return $arguments;
}

/**
 * Views: Handler for artist name argument. 
 * 
 * Example: http://test.mp3pig.com/artist/Cursive
 */
function mp3pig_handler_arg_artist($op, &$query, $argtype, $arg = '') {
  switch ($op) {
    case 'summary':
      $query->ensure_table('aggregator2_audio');
      $query->add_field('artist', 'aggregator2_audio');
      $fieldinfo['field'] = 'aggregator2_audio.artist';
      return $fieldinfo;
      break;
    case 'filter':
      if ($arg == '(no artist)') {
        $arg = '';
      }
      $query->ensure_table('aggregator2_audio');
      $query->add_where("aggregator2_audio.artist LIKE '%s'", $arg);
      break;
    case 'link':
      if ($query->artist == '') {
        $query->artist = '(no artist)';
      }
      return l($query->artist, $arg . '/' . drupal_urlencode($query->artist));              
    case 'title':
      return $query;
  }
}

/**
 * Views: Handler for audio by Agg2 Feed ID argument. 
 * 
 * Example: http://test.mp3pig.com/feed/5100
 */
function mp3pig_handler_arg_fid($op, &$query, $argtype, $arg = '') {
  switch ($op) {
    case 'summary':
      $query->add_field('title');
      $query->add_field('nid');
      $fieldinfo['field'] = 'title';
      return $fieldinfo;
      break;
    case 'filter' :
      $fid = intval($arg);
      $query->ensure_table('aggregator2_feed');
      $query->add_where('aggregator2_feed.nid = %d', $fid);
      break;
    case 'link' :      
      return l($query->title, $arg .'/'. $query->nid);              
    case 'title':
      $node = db_fetch_object(db_query('SELECT title FROM {node} WHERE nid = %d', $query));
      return $node->title;
  }
}