System info: http://www.iloveideamen.com/ Running Habari 0.6.6 MySQL 5 Database
Hello again HabariFolk. I've been using Habari for a wee over a year now, and until the last couple of weeks finally got it to sort shows of a certain content_type. I'm using a Posts::get() and sorting by a postinfo field that exists in my custom content type. My initial stabs at it are painfully recorded here: http://groups.google.com/group/habari-users/browse_thread/thread/7260b69b72247f81/a0cb4c8b04432616 Not long thereafter, I gave up, clueless of the importance of my simple need and how you folks were kind of working on the very same thing. I'm currently giving the site a makeover and I started looking again for a solution. Here's what I found. A simple request and and a simple answer here (from May 21 2009): http://groups.google.com/group/habari-dev/browse_thread/thread/ba2cb84517903d90/0a8526e31f5a297f A similar request and a better answer here (from Dec 1 2009): http://groups.google.com/group/habari-dev/browse_thread/thread/28d52f560f7193ef/732aec795c802231 Then I found this (from Wow! 4 weeks ago): http://trac.habariproject.org/habari/ticket/1397 with a 'wontfix' and this bit of info - "It is no longer recommended to include an aggregate function in a Posts::get() orderby parameter. Use the add_select parameter to add an aliased field that includes the aggregate function, and then order by that field instead. Note that this might not be necessary depending on your database engine, but that the recommendation described here works in all engines." Ouch. To my delight I stumbled upon this (also 4 weeks ago): http://trac.habariproject.org/habari/ticket/1220 Which pointed me in the 2 directions that almost worked for me. First I downloaded a version of posts.php at: http://trac.habariproject.org/habari/changeset/4773 And this code on my page worked fine for a Shows Archive: <?php $showposts = Posts::get(array( 'content_type' => Post::type('ideamen_show'), 'status' => Post::status('published'), 'has:info' => 'eventtimestamp', 'orderby' => 'info_eventtimestamp_value ASC', 'nolimit' => TRUE )); foreach ( $showposts as $post ) { include( 'ideamen_show.php' ); } ?> Plus this code on a different page to display only shows with showdates in the future: <?php $showposts = Posts::get(array( 'content_type' => Post::type('ideamen_show'), 'status' => Post::status('published'), 'tag' => 'Shows', 'has:info' => 'eventtimestamp', 'orderby' => 'info_eventtimestamp_value ASC', 'nolimit' => TRUE )); foreach ( $showposts as $post ) { if ( $post->info->eventtimestamp > strtotime('yesterday') ) { include( 'ideamen_show.php' ); } } ?> It's great that this works, except that all I was looking at was those 2 pages - the rest of my site broke. All posts would display on every other page on the site. Boo. I reverted posts.php back to the original. //////////////////////////////// I took another look at the http://trac.habariproject.org/habari/ticket/1220 info, finding this: $posts = Posts::get( array( 'tag' => 'habari', 'has:info' => 'test', 'orderby' => "cast(hipi1.value as integer) DESC" ) ); $posts = Posts::get( array( 'tag' => 'habari', 'has:info' => 'test', 'add_select' => array("cast(hipi1.value as integer) as order1"), 'orderby' => 'order1 DESC' ) ); and also found that http://wiki.habariproject.org/en/Dev:Retrieving_Posts#Ordering_by_post_info had been updated recently to include this code: public function theme_popular_posts($theme, $limit = 5) { $theme->popular_posts = Posts::get(array( 'content_type' => 'entry', 'has:info' => 'views', 'add_select' => array("cast(hipi1.value as integer) as order1"), 'orderby' => 'order1 DESC' 'limit' => $limit )); return $theme->display( 'popular_posts' ); } However the answer I was looking for was not there, given the info provided: "Either of these works on the latest commit, assuming hipi1 is Habari's internal name for the table that provides the field 'info_test_value':" "The disadvantage of these approaches is that you have to know the real name of the column Habari will use in the SQL, hipi1 above. Future versions of Habari will work around this shortcoming of PostgreSQL." I really want to know how to use this. The above code has syntax errors, and I have no idea what they are. Plus I have no idea how to find out what Habari calls my eventtimestamp field. I 'fixed' it for my page, here's what I have currently: <?php $showposts = Posts::get(array( 'content_type' => Post::type('ideamen_show'), 'status' => Post::status('published'), 'has:tag' => 'Shows', 'has:info' => 'eventtimestamp', 'add_select' => array("cast(eventtimestamp.value as integer) as order1"), 'orderby' => 'order1 ASC', 'nolimit' => TRUE )); foreach ( $showposts as $post ) { include( 'ideamen_show.php' ); } ?> This is my error message: Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order1' in 'order clause' in system/classes/databaseconnection.php line 290 Warning: Invalid argument supplied for foreach() in user/themes/ ideaTheme/ideamen_show.showsarchive.php line 16 Any thoughts/suggestions/solutions? I think first off there's some syntax error in the line: array("cast(eventtimestamp.value as integer) as order1"), I think I'd also like a pointer towards "the real name of the column Habari will use in the SQL" Thanks again and looking forward to your solution, --Dave from Ideamen -- To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/habari-users
