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 NoticeDirectory filter : [ all ] / postnuke_modules / articles [ view in CVS ]
| Date | Directory [filter] | File(s) [view] | Author [filter] |
| 30 Jul 2002 22:22:13 | postnuke_modules/articles | pnuser.php,1.35,1.36 pnuserapi.php,1.24,1.25 | Mike |
| 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;
}
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 |