[Solar-talk] Patch for Solar_Content_Abstract

[Paul M Jones]

Hey Travis --

Sorry to top-post here, but I want to keep your patch inline *and*  
show you this link:

   <http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html>

That's where the SQL commands come from; it explains the need for the  
HAVING clause.  Read that (if you have not already) and then we can  
continue the discussion.

With respect to the COUNT(), I'm surprised that SQLite isn't carrying  
the names forward.  Can you check if this is a known issue in your  
version of SQLite?  If it's the expected behavior, I'll see what I  
can do to reformat the query so that it works properly under that  
system.


On Oct 1, 2006, at 10:01 AM, Travis Swicegood wrote:

> Howdy all,
>
> Yet another patch.  The bookmark app was working until I tried to  
> view by tags.  Here's the latest changes.  You'll see the tags.name  
> changes at the top still, plus two new sets of changes.
>
> The first one has to do with the count().  SQLite (possibly just my  
> version, 3.2.8?) doesn't like this type of query:
>
> SELECT
>    COUNT(nodes.id)
> FROM
>    (SELECT nodes.id FROM nodes) AS nodes
>
> The COUNT() can't figure out what it's counting on because  
> apparently (and probably a bug) SQLite doesn't carry column names  
> from sub-queries properly back - at least for COUNT()s.  I didn't  
> try just doing a straight select.
>
> The second block removes the having() call.  I'm not sure what  
> that's there for.  As soon as I add a having to the query, I get  
> nothing back.  As soon as I remove it, the sub-query performs like  
> it's supposed to.  What is the reasoning behind the having?
>
> With this patch applied, the Bookmarks app works against trunk for me.
>
> -Travis
> Index: Solar/Content/Abstract.php
> ===================================================================
> --- Solar/Content/Abstract.php	(revision 1882)
> +++ Solar/Content/Abstract.php	(working copy)
> @@ -350,10 +350,10 @@
>          $select->multiWhere($where);
>
>          // group by tag name
> -        $select->group('name');
> +        $select->group('tags.name');
>
>          // order and return
> -        $select->order('name');
> +        $select->order('tags.name');
>          return $select->fetch('pairs');
>      }
>
> @@ -460,7 +460,7 @@
>              $wrap = Solar::factory('Solar_Sql_Select');
>              $wrap->fromSelect($select, 'nodes');
>              $wrap->setPaging($this->_paging);
> -            return $wrap->countPages('nodes.id');
> +            return $wrap->countPages('');
>          } else {
>              // no need for subselect
>              return $select->countPages('nodes.id');
> @@ -679,7 +679,7 @@
>      {
>          $select->join($this->_content->tags, 'tags.node_id =  
> nodes.id')
>                 ->where('tags.name IN (?)', $tags)
> -               ->having("COUNT(nodes.id) = ?", count($tags))
> +               //->having("COUNT(nodes.id) = ?", count($tags))
>                 ->group("nodes.id");
>      }
>
> _______________________________________________
> solar-talk mailing list
> solar-talk at lists.solarphp.com
> http://mail.killersoft.com/mailman/listinfo/solar-talk



--

Paul M. Jones  <http://paul-m-jones.com>

Solar: Simple Object Library and Application Repository
for PHP5.   <http://solarphp.com>

Savant: The simple, elegant, and powerful solution for
templates in PHP.   <http://phpsavant.com>