Page 1 of 1
CTLModuleMaker - Where clause concerning dates
Posted: Thu Nov 26, 2009 9:55 am
by nmotion
Hi all,
First, great module, highly recommend it.
I have one small thing I can't get to work.
I need to order a list by date which is easy to do. Now the hard part. I also need to add a WHERE clause that says that all posts older than todays date should not be included.
In SQL it would look something like this:
WHERE date < NOW()
ORDER BY date
Is that possible in CTLModuleMaker.
Re: CTLModuleMaker - Where clause concerning dates
Posted: Thu Nov 26, 2009 12:49 pm
by plger
The only method right now would be to enable "Allow manual sql queries" and use the "query" parameter to enter the sql where clause (I think there are infos on that in the faq).
(By the way, if you want elements not older than today's date - and not time - use date > CURRENT_DATE instead of NOW())
Re: CTLModuleMaker - Where clause concerning dates
Posted: Wed Dec 02, 2009 11:35 am
by nmotion
I can't get to accept anything that resembles a date.
Ex.
Code: Select all
{cms_module module="aktiviteter" what="aktivitet" nbperpage="7" query="A.start = '2009-03-15'"}
It will only take simple number quires like:
Code: Select all
{cms_module module="aktiviteter" what="aktivitet" nbperpage="7" query="A.pris = 0}
I can't get to accept text values either. What am I doing wrong?
Re: CTLModuleMaker - Where clause concerning dates
Posted: Sat Dec 05, 2009 5:52 pm
by plger
There's probably a bug.
in action.default.php, you should see :
Code: Select all
if(isset($params["query"])){
$queryid = (int) $params["query"];
if($query = $this->get_queries(array("id"=>$queryid))){
// we retrieve the name of the function that will do the query
$query = $query[0];
$what = $query->what;
$getfunction = "get_level_".$what;
$parentlevel = $this->get_nextlevel($what, false);
$whereclause = $query->whereclause;
$wherevalues = $query->wherevalues;
$customorder = ($query->queryorder == ""?false:$query->queryorder);
}elseif($this->GetPreference("allow_sql",false)){
$getfunction = "get_level_".$what;
$parentlevel = $this->get_nextlevel($what, false);
$whereclause = $params["query"]; // query should be sanitized...
$wherevalues = array();
$customorder = false;
$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $query);
}else{
$message = $this->Lang("error_wrongquery");
if($queryid != $query && $msg = mysql_error()) $message .= "<br/>".$this->Lang("givenerror").$msg;
echo $this->ShowErrors($message);
return false;
}
if(!isset($params["alias"]) && $nbperpage){
// we need pagination - so we first retrieve the total number of items fitting the query
$total = $this->countsomething($what,"id",array(),$whereclause, $wherevalues, $parentlevel);
}
if(isset($total) && $total == 0){
$itemlist = array(); // we already know there's nothing to query
}else{
$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $whereclause, $wherevalues, $customorder);
}
}else{
try replacing it with:
Code: Select all
if(isset($params["query"])){
$queryid = (int) $params["query"];
if($query = $this->get_queries(array("id"=>$queryid))){
// we retrieve the name of the function that will do the query
$query = $query[0];
$what = $query->what;
$getfunction = "get_level_".$what;
$parentlevel = $this->get_nextlevel($what, false);
$whereclause = $query->whereclause;
$wherevalues = $query->wherevalues;
$customorder = ($query->queryorder == ""?false:$query->queryorder);
}elseif($this->GetPreference("allow_sql",false)){
$getfunction = "get_level_".$what;
$parentlevel = $this->get_nextlevel($what, false);
$whereclause = $params["query"]; // query should be sanitized...
$wherevalues = array();
$customorder = false;
}else{
$message = $this->Lang("error_wrongquery");
if($queryid != $query && $msg = mysql_error()) $message .= "<br/>".$this->Lang("givenerror").$msg;
echo $this->ShowErrors($message);
return false;
}
$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $whereclause, $wherevalues, $customorder);
if(!isset($params["alias"]) && $nbperpage){
// we need pagination - so we first retrieve the total number of items fitting the query
$total = $this->countsomething($what,"id",array(),$whereclause, $wherevalues, $parentlevel);
}else{
$total = count($itemlist);
}
}else{
and let me know what comes out of it