Xaraya / Postnuke CVS Notices - Message

Note: this list is kept only as a demonstration for CVSNotice. For the latest CVS notices, see the Xaraya and Postnuke sites

View Statistics - Next Notice - Previous Notice

Directory filter : [ all ] / postnuke_modules / articles [ view in CVS ]

Date Directory [filter] File(s) [view] Author [filter]
30 Jul 2002 22:22:13postnuke_modules/articlespnuser.php,1.35,1.36 pnuserapi.php,1.24,1.25Mike
 work with LEFT JOIN, and less fields in the query for Archives

Update of /home/cvsroot/postnuke_modules/articles
In directory ns7.hostnuke.net:/tmp/cvs-serv6632

Modified Files:
	pnuser.php pnuserapi.php 
Log Message:
work with LEFT JOIN, and less fields in the query for Archives


Index: pnuser.php
===================================================================
RCS file: /home/cvsroot/postnuke_modules/articles/pnuser.php,v
retrieving revision 1.35
retrieving revision 1.36
diff -C2 -d -r1.35 -r1.36
*** pnuser.php	30 Jul 2002 18:31:48 -0000	1.35
--- pnuser.php	30 Jul 2002 22:22:11 -0000	1.36
***************
*** 219,222 ****
--- 219,231 ----
                                array()),
                       pnML('View Article Map'));
+ 
+         $output->SetInputMode(_PNH_VERBATIMINPUT);
+         $output->Text('   ');
+         $output->SetInputMode(_PNH_PARSEINPUT);
+         $output->URL(pnModURL('articles',
+                               'user',
+                               'archive',
+                               array()),
+                      pnML('View Archive'));
          return $output->GetOutput();
      }

Index: pnuserapi.php
===================================================================
RCS file: /home/cvsroot/postnuke_modules/articles/pnuserapi.php,v
retrieving revision 1.24
retrieving revision 1.25
diff -C2 -d -r1.24 -r1.25
*** pnuserapi.php	30 Jul 2002 18:31:48 -0000	1.24
--- pnuserapi.php	30 Jul 2002 22:22:11 -0000	1.25
***************
*** 51,59 ****
   * @param $args['startnum'] starting article number
   * @param $args['ptid'] publication type ID (for news, sections, reviews, ...)
!  * @param $args['cids'] array of category IDs for which to get articles (for all donīt set it)
!  * @param $args['startdate'] articles published at startdate or later (unix timestamp format)
!  * @param $args['enddate'] articles published before enddate (unix timestamp format)
!  * @param $args['fields'] array with the fields to return per article - default is
!  *                        'aid','title','summary','author','pubdate','pubtypeid','body','cids'
   * @returns array
   * @return array of articles, or false on failure
--- 51,63 ----
   * @param $args['startnum'] starting article number
   * @param $args['ptid'] publication type ID (for news, sections, reviews, ...)
!  * @param $args['cids'] array of category IDs for which to get articles
!  *                      (for all categories donīt set it)
!  * @param $args['startdate'] articles published at startdate or later
!  *                           (unix timestamp format)
!  * @param $args['enddate'] articles published before enddate
!  *                         (unix timestamp format)
!  * @param $args['fields'] array with the fields to return per article
!  *                        default list is : 'aid','title','summary','authorid',
!  *                        'pubdate','pubtypeid','body','cids','author'
   * @returns array
   * @return array of articles, or false on failure
***************
*** 68,85 ****
          $startnum = 1;
      }
!     if (!isset($fields)) {
!         $fields = array('aid','title','summary','author','pubdate','pubtypeid',
!                         'body','cids');
      }
  
! /*
!     // Required argument
!     if (!isset($numitems)) {
!         $msg = pnML('Empty numitems (#(1)).', $numitems);
!         pnExceptionSet(PN_SYSTEM_EXCEPTION, 'BAD_PARAM',
!                        new SystemException(__FILE__.'('.__LINE__.'): '.$msg));
!         return;
      }
- */
  
      $articles = array();
--- 72,87 ----
          $startnum = 1;
      }
!     if (!isset($cids)) {
!         $cids = array();
      }
  
!     // Available fields in articles (for now)
!     // + 'cids' = list of categories an article belongs to
!     // + 'author' = user name of authorid
!     $columns = array('aid','title','summary','authorid','pubdate','pubtypeid',
!                      'body','cids','author');
!     if (!isset($fields)) {
!         $fields = $columns;
      }
  
      $articles = array();
***************
*** 90,162 ****
      }
  
!     // Required fields
      $required = array();
      foreach ($fields as $field) {
          $required[$field] = 1;
      }
  
      // Database information
-     // More abstraction?
      list($dbconn) = pnDBGetConn();
-     $pntable = pnDBGetTables();
- 
-     $articlestable = $pntable['articles'];
-     $articlescolumn = &$pntable['articles_column'];
- 
-     $userstable = $pntable['users'];
-     $prefix = $userstable;
-     $userscolumn = Array
-     (
-         'uid' => $prefix . '.pn_uid',
-           'name' => $prefix . '.pn_name',
-           'uname' => $prefix . '.pn_uname',
-           'email' => $prefix . '.pn_email',
-           'url' => $prefix . '.pn_url'
-     );
  
! // TODO: check the order of the conditions for brain-dead databases ?
  
!     $conditions = Array();
!     $conditions[] = $articlescolumn['authorid'].' = '.$userscolumn['uid'];
!     if (!empty($ptid)) {
!         $conditions[] = $articlescolumn['pubtypeid'].' = '.$ptid;
!     }
!     if (!empty($startdate) && !empty($enddate) &&
!         is_numeric($startdate) && is_numeric($enddate) &&
!         $enddate > $startdate) {
!         $conditions[] = $articlescolumn['pubdate'].' >= '.$startdate;
!         $conditions[] = $articlescolumn['pubdate'].' < '.$enddate;
      }
-         
-     // Get articles
-     $columns = Array
-     (
-         $articlescolumn['aid'],
-         $articlescolumn['title'],
-         $articlescolumn['summary'],
-         $userscolumn['name'],
-         $articlescolumn['pubdate'],
-         $articlescolumn['pubtypeid'],
-         $articlescolumn['body']    
-     );
- 
-     $tables = Array
-     (
-         $articlestable,
-         $userstable
-     );
-     
-     $orderings = Array
-     (
-         $articlescolumn['pubdate'].' DESC'
-     );
- 
-     $sql = '';
  
! # TODO: we need FROM (articles LEFT JOIN users ON ...) LEFT JOIN categories ON ... here !
! 
!     $sql .= IterateSQL ('SELECT DISTINCT', $columns, ',');
!     $sql .= IterateSQL (' FROM', $tables, ',');
!     if (isset($cids) && is_array($cids)) {
          // Load API
          if (!pnModAPILoad('categories', 'user')) {
--- 92,124 ----
      }
  
!     // Fields required by the calling function
      $required = array();
      foreach ($fields as $field) {
          $required[$field] = 1;
      }
+     if (count($cids) > 0) {
+         $required['cids'] = 1;
+     }
  
      // Database information
      list($dbconn) = pnDBGetConn();
  
!     // Get the field names and LEFT JOIN ... ON ... parts from articles
!     $articlesdef = pnModAPIFunc('articles','user','leftjoin');
  
!     if (!empty($required['author'])) {
!         // Load API
!         if (!pnModAPILoad('users', 'user')) {
!             $msg = pnML('Unable to load (#(1))īs module (#(2))īs functions.',
!                         pnML('users'), pnML('user'));
!             pnExceptionSet(PN_SYSTEM_EXCEPTION, 'UNABLE_TO_LOAD',
!                  new SystemException(__FILE__.'('.__LINE__.'): '.$msg));
!             return;
!         }
!         // Get the field names and LEFT JOIN ... ON ... parts from users
!         $usersdef = pnModAPIFunc('users','user','leftjoin');
      }
  
!     if (!empty($required['cids'])) {
          // Load API
          if (!pnModAPILoad('categories', 'user')) {
***************
*** 167,183 ****
              return;
          }
!         $catdef = pnModAPIFunc('categories','user','leftjoin',array('cids' => $cids));
!         $sql .= ' LEFT JOIN ' . $catdef['table'];
!         $sql .= ' ON ' . $catdef['field'] . ' = ' . $articlescolumn['aid'];
      }
  
!     $sql .= IterateSQL (' WHERE', $conditions, ' AND '); 
  
!     if (isset($cids) && is_array($cids)) {
!         $sql .= ' AND ' . $catdef['where'] . ' = ' . pnModGetIDFromName('articles');
      }
  
!     $sql .= IterateSQL (' ORDER BY', $orderings, ',');
  
      if (isset($numitems) && is_numeric($numitems)) {
          $result = $dbconn->SelectLimit($sql, $numitems, $startnum-1);
--- 129,197 ----
              return;
          }
!         // Get the LEFT JOIN ... ON ...  and WHERE (!) parts from categories
!         $categoriesdef = pnModAPIFunc('categories','user','leftjoin',
!                                       array('cids' => $cids));
      }
  
!     // Create the SELECT part
!     $select = array();
!     foreach ($required as $field => $val) {
!         // we'll handle this later
!         if ($field == 'cids') {
!             continue;
!         } elseif ($field == 'author') {
!             $select[] = $usersdef['name'];
!         } else {
!             $select[] = $articlesdef[$field];
!         }
!     }
!     $sql = 'SELECT ' . join(', ', $select);
  
!     // Create the FROM ... [LEFT JOIN ... ON ...] part
!     $from = $articlesdef['table'];
!     if (!empty($required['author'])) {
!         // Add the LEFT JOIN ... ON ... parts from users
!         $from .= ' LEFT JOIN ' . $usersdef['table'];
!         $from .= ' ON ' . $usersdef['field'] . ' = ' . $articlesdef['authorid'];
! 
!         // add this for SQL compliance when there are multiple JOINs
!         if (count($cids) > 0) {
!             $from = '(' . $from . ')';
!         }
!     }
!     if (count($cids) > 0) {
!         // Add the LEFT JOIN ... ON ... parts from categories
!         $from .= ' LEFT JOIN ' . $categoriesdef['table'];
!         $from .= ' ON ' . $categoriesdef['field'] . ' = ' . $articlesdef['aid'];
      }
+     $sql .= ' FROM ' . $from;
  
!     // Create the WHERE part
!     $where = array();
! // TODO: check the order of the conditions for brain-dead databases ?
!     if (!empty($ptid) && is_numeric($ptid)) {
!         $where[] = $articlesdef['pubtypeid'] . ' = ' . $ptid;
!     }
!     if (!empty($startdate) && is_numeric($startdate)) {
!         $where[] = $articlesdef['pubdate'] . ' >= ' . $startdate;
!     }
!     if (!empty($enddate) && is_numeric($enddate)) {
!         $where[] = $articlesdef['pubdate'] . ' < ' . $enddate;
!     }
!     if (count($cids) > 0) {
!         $where[] = $categoriesdef['where'];
!         // required for categories !
!         $where[] = $categoriesdef['modid'] . ' = ' .
!                    pnModGetIDFromName('articles');
!     }
!     if (count($where) > 0) {
!         $sql .= ' WHERE ' . join(' AND ', $where);
!     }
  
+ // TODO: make this configurable too someday ?
+     // Create the ORDER BY part
+     $sql .= ' ORDER BY ' . $articlesdef['pubdate'] . ' DESC';
+ 
+     // Run the query - finally :-)
      if (isset($numitems) && is_numeric($numitems)) {
          $result = $dbconn->SelectLimit($sql, $numitems, $startnum-1);
***************
*** 195,213 ****
      // Put articles into result array
      for (; !$result->EOF; $result->MoveNext()) {
!         // Does AdoDB has something equivalent to fetch_assoc?
!         list($aid, $title, $summary, $author, $pubdate, $pubtypeid, $body) = 
!             $result->fields;
!         if (pnSecAuthAction(0, 'articles::item', "$title::$aid", ACCESS_READ)) {
!             $articles[] = array('aid'        => $aid,
!                                 'title'        => $title,
!                                 'summary'    => $summary,
!                                 'author'    => $author,
!                                 'pubdate'    => $pubdate,
!                                 'pubtypeid'    => $pubtypeid,
!                                 'body'        => $body);
          }
      }
      $result->Close();
!     
      if ($required['cids']) {
          // Get all the categories at once
--- 209,229 ----
      // Put articles into result array
      for (; !$result->EOF; $result->MoveNext()) {
!         $data = $result->fields;
!         $item = array();
!         // loop over all required fields again
!         foreach ($required as $field => $val) {
!             if ($field == 'cids' || $val != 1) {
!                 continue;
!             }
!             $value = array_shift($data);
!             $item[$field] = $value;
!         }
!         // check security
!         if (pnSecAuthAction(0, 'articles::item', "$item[title]::$item[aid]", ACCESS_READ)) {
!             $articles[] = $item;
          }
      }
      $result->Close();
! 
      if ($required['cids']) {
          // Get all the categories at once
***************
*** 517,520 ****
--- 533,610 ----
  
      return $months;
+ }
+ 
+ /**
+  * return the field names and correct values for joining on articles table
+  * example : SELECT ..., $title, $body,...
+  *           FROM ...
+  *           LEFT JOIN $table
+  *               ON $field = <name of articleid field in your module>
+  *           WHERE ...
+  *               AND $pubdate > 123456789
+  *               AND $where
+  *
+  * @param $args['aids'] optional array of aids that we are selecting on
+  * @returns array
+  * @return array('table' => 'nuke_articles',
+  *               'field' => 'nuke_articles.pn_aid',
+  *               'where' => 'nuke_articles.pn_aid IN (...)',
+  *               'title'  => 'nuke_articles.pn_title',
+  *               ...
+  *               'body'  => 'nuke_articles.pn_body')
+  */
+ function articles_userapi_leftjoin($args)
+ {
+     // Get arguments from argument array
+     extract($args);
+ 
+     // Optional argument
+     if (!isset($aids)) {
+         $aids = array();
+     }
+ 
+     // Security check
+     if (!pnSecAuthAction(0, 'articles::', '::', ACCESS_OVERVIEW)) {
+         $msg = pnML('Not authorized to view articles');
+         pnExceptionSet(PN_SYSTEM_EXCEPTION, 'NO_PERMISSION',
+                        new SystemException($msg));
+         return array();
+     }
+ // TODO: check this !
+     foreach ($aids as $aid) {
+         if (!pnSecAuthAction(0, 'articles::item', "::$aid", ACCESS_READ)) {
+             $msg = pnML('Not authorized to view #(1) #(2)',
+                         'article',pnVarPrepForStore($aid));
+             pnExceptionSet(PN_SYSTEM_EXCEPTION, 'NO_PERMISSION',
+                            new SystemException($msg));
+             return array();
+         }
+     }
+ 
+     // Table definition
+     $pntable = pnDBGetTables();
+     $articlestable = $pntable['articles'];
+ 
+     $leftjoin = array();
+ 
+     // Specify LEFT JOIN ... ON ... [WHERE ...] parts
+     $leftjoin['table'] = $articlestable;
+     $leftjoin['field'] = $articlestable . '.pn_aid';
+     if (count($aids) > 0) {
+         $allaids = join(', ', $aids);
+         $leftjoin['where'] = $articlestable . '.pn_aid IN (' .
+                              pnVarPrepForStore($allaids) . ')';
+     } else {
+         $leftjoin['where'] = '';
+     }
+ 
+     // Add available columns in the articles table (for now)
+     $columns = array('aid','title','summary','authorid','pubdate','pubtypeid',
+                      'body');
+     foreach ($columns as $column) {
+         $leftjoin[$column] = $articlestable . '.pn_' . $column;
+     }
+ 
+     return $leftjoin;
  }
  


Directory filter : [ all ] / postnuke_modules / articles [ view in CVS ]

View Statistics - Next Notice - Previous Notice


Visit Developer Site - Browse CVS Repository Syndicate via backend.rss
(max. once per hour please)
Powered by CVSNotice 0.1.3