Filter

Thanks !

I tried to PM you but didn't see how to - so here's my eMail address.

Looking forward to seeing the code. My GridBuilder.php, which I downloaded yesterday, doesn't have a SELECT DISTINCT statement. The code below is from line 200 - 207.

I'm using this in Joomla 1.5.15 on MySQL...

Here is the complete code of the php file GridBuilder

---

<?php
#

---

# Copyright (c) 2009 Gomilsek-informatika.
# All rights reserved. This program and the accompanying materials
# are made available under the terms of the GNU Public License v2.0
# which accompanies this distribution, and is available at
# www.gnu.org/licenses/old-licenses/gpl-2.0.html
#
# Contributors:
# Gomilsek-informatika (initial API and implementation)
# Contact:
# This email address is being protected from spambots. You need JavaScript enabled to view it.
#

---

require_once(JPATH_ROOT.DS.'components'.DS.'com_grid'.DS.'GridConfigManager.php');
if(file_exists(JPATH_ROOT.DS.'components'.DS.'com_grid'.DS.'views'.DS.'GridView.php'))
require_once(JPATH_ROOT.DS.'components'.DS.'com_grid'.DS.'views'.DS.'GridView.php');
if(file_exists(JPATH_ROOT.DS.'components'.DS.'com_grid'.DS.'views'.DS.'CardView.php'))
require_once(JPATH_ROOT.DS.'components'.DS.'com_grid'.DS.'views'.DS.'CardView.php');

class GridBuilder{
var $_ID= null;
var $_config;

var $dbo;
var $_data;
var $rows; //number of all records
var $queryRows; //number of records displayed on page - lower than $rpp only ol last page

var $parentCont = false; //parent content data

var $colList; //query columns list
var $searchQuery;

var $starttime;

var $rpp; //records per page
var $orderBy = "";//order by field name
var $direction = ""; //order direction
var $page = ""; // records page
var $searchStr = ""; //search string
var $searchField = "";
var $isAjax = "";

function __construct ($id, &$config){
$this->starttime = microtime(true);
//$this->_ID je oblike '[id configuracije]_[karkoli]'
$this->_ID=$id;
//$this->setConfig($id);
$this->_config = &$config;
$this->setUserDefinedVars();
$this->setDBO();
$session = & JFactory::getSession();
if($session->has($id))
$this->parentCont = $session->get($id);
$this->buildColumnsLists();
$query = $this->buildQuery($conf_order);
$this->loadData($query);
}

/*function setConfig($id){
//$this->_ID je oblike '[id configuracije]_[karkoli]'
$idArr = array();
$idArr = explode('_', $id);
$this->_config = new GridConfigManager($idArr[0]);
}*/

function setUserDefinedVars(){
if(isset($_GET)) $this->rpp=$_GET;
else $this->rpp=$this->_config->nrRows;

if (isset($_GET["o_b"])) $this->orderBy = mysql_escape_string($_GET["o_b"]);
else $this->orderBy = $this->_config->default_order[0];

if (isset($_GET["o_d"])) $this->direction = mysql_escape_string($_GET["o_d"]);
else $this->direction = $this->_config->default_order[1];

if (isset($_GET["p"]))
$this->page = mysql_escape_string($_GET["p"]);
if (isset($_GET["data_search"]))
$this->searchStr = mysql_escape_string($_GET["data_search"]);
if (isset($_GET["s_f"]))
$this->searchField = mysql_escape_string($_GET["s_f"]);
if (isset($_GET["ajax"]))
$this->isAjax = mysql_escape_string($_GET["ajax"]);
}

function setDBO(){
if($this->_config->connection == 'joomla'){
$this->dbo = JFactory::getDBO();
}
else{
$option = array(); //prevent problems
$option = $this->_config->_dbtype; // Database driver name
$option = $this->_config->_dbhost; // Database host name
$option = $this->_config->_dbuser; // User for database authentication
$option = $this->_config->_dbpass; // Password for database authentication
$option = $this->_config->_dbname; // Database name
$option = ''; // Database prefix (may be empty)
$_DB=$this->_config->_dbtype;
$this->dbo = & JDatabase::getInstance( $option );
if ( JError::isError($db) ) {
jexit('Database Error: ' . $db->toString() );
}

}
}

function loadData($query){
//echo $query;
$this->dbo->setQuery($query);
$this->_data = $this->dbo->loadAssocList();
$this->queryRows=$this->dbo->getAffectedRows();
}

function buildColumnsLists(){

$query_columns = $this->_config->columnNames;
$conf_columns_count = count($this->_config->columnNames);

if ($conf_columns_count > 0) {

//links
for ($i = 0; $i < $conf_columns_count; $i++) {
//if type isn't 0 and column is not already in array

if($this->_config->link[2*$i]!=0 && !in_array($this->_config->link[2*$i+1], $query_columns))
$query_columns[] = $this->_config->link[2*$i+1];
}

$columns_list = "";
$search_query = "";

for ($i = 0; $i < count($query_columns); $i++) {

if ("$i"!="0") {
$columns_list .= ", ";
if ("$this->searchField"=="0") {
if ("$this->searchStr"!="") {
$search_query .= " OR ";
}
}
}

$columns_list .= "$query_columns[$i]";

if ("$this->searchField"=="0" || "$this->searchField"=="$query_columns[$i]") {

if ("$this->searchStr"!="") {
if($this->_config->caseSensitive){
$search_query .= "$query_columns[$i] LIKE '%$this->searchStr%'";
}
else{
$search_query .= "UPPER($query_columns[$i]) LIKE ('%".strtoupper($this->searchStr)."%')";
}
}
}

$this->colList = $columns_list;
$this->searchQuery = $search_query;
}
} else {
return "No mySQL columns specified for data listings.";
}
}

function buildQuery()
{
if ($this->_config->tableName=="") return "No mySQL table specified for data listings.";

//orderby
$order='';
if ("$this->orderBy"!="") {
$order .= " ORDER BY $this->orderBy $this->direction";

//secondary order by
$secOrder=explode('|', $this->_config->secOrder);
if($secOrder[0]!= '0'){ //if secondary orderby is defined
if($this->orderBy != $secOrder[0]){ //if is not same as current order
if($secOrder[2] == 1){ // if only on default sort is set
if($this->_config->default_order[0] == $this->orderBy)
$order.=','.$secOrder[0].' '.$secOrder[1]; // if current sort == default sort
}
else $order.=','.$secOrder[0].' '.$secOrder[1];
}}
}

//where
$conf_query = $this->translateWhereCond($this->_config->whereCond);
if (("$conf_query"!="") && ($this->searchQuery!="")) {
$where .= " WHERE ".$conf_query." AND ( $this->searchQuery )";
} elseif ("$conf_query"!="") {
$where .= " WHERE $conf_query";
} elseif ("$this->searchQuery"!="") {
$where .= " WHERE $this->searchQuery";
}

//limit
if ($this->page=="") $this->page = "0";
$count = $this->count($where);
$start = $this->page*$this->rpp;

//query
if($this->dbo->name=='mysql')
{
$query = "SELECT DISTINCT ".$this->colList;

$query .= " FROM ".$this->_config->tableName;

//limit
$limit = " LIMIT $start, ".$this->rpp;

$query = $query.$where.$order.$limit;

//echo "MySQL frontend:\n".$query;
}
else if($this->_config->_dbtype == 'mssql')
{

if("$where"=="")
{
$query = "
SELECT ".$this->colList."
FROM (
SELECT
ROW_NUMBER() OVER (".$order.") AS rownum,
".$this->colList."
FROM ".$this->_config->tableName."
) AS foo
WHERE rownum > ".$start." AND rownum <= (".$this->rpp."+".$start.")";
}
else
{
$query = "
SELECT ".$this->colList."
FROM (
SELECT
ROW_NUMBER() OVER (".$order.") AS rownum,
".$this->colList."
FROM ".$this->_config->tableName.$where."
) AS foo
WHERE rownum > ".$start." AND rownum <= (".$this->rpp."+".$start.")";
}

//echo "SQL frontend:\n".$query;
}
else if($this->_config->_dbtype == 'oracle')
{
if("$where"=="")
{
$query = "
SELECT ".$this->colList."
FROM (
SELECT
ROW_NUMBER() OVER (".$order.") AS rownumber,
".$this->colList."
FROM ".$this->_config->tableName."
)
WHERE rownumber > ".$start." AND rownumber <= (".$this->rpp."+".$start.")";
}
else
{
$query = "
SELECT ".$this->colList."
FROM (
SELECT
ROW_NUMBER() OVER (".$order.") AS rownumber,
".$this->colList."
FROM ".$this->_config->tableName.$where."
)
WHERE rownumber > ".$start." AND rownumber <= (".$this->rpp."+".$start.")";
}

//echo "SQL frontend:\n".$query;
}

return $query;
}

function count($where){
$query = "SELECT COUNT(*) FROM ".$this->_config->tableName.$where.";";
$this->dbo->setQuery($query);
$count = $this->dbo->loadResult();
$this->rows = $count;
return $count;
}

function translateWhereCond($whereCond){
$user =& JFactory::getUser();
$userId = $user->get( 'id' );
$groupId =$user->get( 'gid' );
if($this->parentCont){
$contentId=$this->parentCont->id;
$catId=$this->parentCont->catid;
$sectionId=$this->parentCont->sectionid;
}
else{
$contentId=0;
$catId=0;
$sectionId=0;
}

$whereCond = str_replace('@article_id', $contentId, $whereCond);
$whereCond = str_replace('@category_id', $catId, $whereCond);
$whereCond = str_replace('@section_id', $sectionId, $whereCond);
$whereCond = str_replace('@user_id', $userId, $whereCond);
$whereCond = str_replace('@group_id', $groupId, $whereCond);

//echo $contentId.' '.$catId.' '.$sectionId.' '.$userId.' '.$groupId;

return $whereCond;

}

}

Thanks for sending the code along.

I see the SELECT DISTINCT statements, but am unsure how to implement the filtering.

I'm using grid to display CommunityBuilder users, and would like to only display users who are approved.

I tried adding a WHERE ('cb_fieldname' = '1') but it didn't seem to process....

Any insights would be most appreciated.

First, I really appreciate your help.

Second, I'm sorry for not being clearer in my question.

If you could share with me where you were able to enter your filter in the file you sent along, I think that would be most helpful.

Also, I noticed your code is quite different than the version I have. I just bought the component two days ago, and I get the feeling there have been updates.

Either way, if the code you sent can meet my needs with the hard coding of the filter I need, I'll happily use it.

Thanks again.

mm

i am trying to filter based on section and category but i can't seem to get it to work

is the correct?

WHERE (cb_fieldname = '@section_id')

thank you