The Rand Show: programming

Showing posts with label programming. Show all posts

Tuesday, September 23, 2025

Hibernate Tip - Using case expression in sorting

I wanted to share this hard-earned discovery but couldn't find a suitable place to (or maybe didn't want to try that much)

Yes, the great and awesome Rand McRanderson has a day job and in such day job I do program and that programming involves the Hibernate ORM system.

One annoyance has been that Hibernate does not allow simple expressions in the sort by clause, that is I cannot do (inside a query annotation or by functionally using the entity manager to create a query)

SELECT * FROM Books b WHERE b.title LIKE CONCAT('%' , :param , '%') ORDER BY b.title = :param DESC, b.publishDate

For the unfamiliar, what this does is it looks through a database's book table for titles (the b.title field of the object) that contain the value of :param (which could be user input, for our example let's say the value is "Bob"), the LIKE and '%', '%', mean that the text "Bob" could have any text before or after it in the title, ie, "Bob's adventure", "Adventures with Bob", "The Bob of Bobbington" are all matches. The select clause is set to '*' which means to fetch all the fields (in this case that would be the entire Book entity).

In this scenario, sorting the results by b.publishDate is reasonable, however then you get cases where, a book called "Bob", published in 2022, is ranked after a book called "Long list of names including Alan, Joe, Michael, also Bob" published in 2024. Some people are fine with this, I find it a little annoying.

So what I'm trying to do is to first sort by whether or not there is an exact match for the title "Bob" using the expression b.title = :param - this will evaluate to a boolean value, either TRUE or FALSE, and since TRUE (which means there is an exact match) = 1 and FALSE = 0 in Hibernate (as in most of the computer world), I sort this descending, after this sort criteria, I still sort by publishDate so I end up with exact matches first sorted by date and then the other matches sorted by date afterwards.

That's the idea at least, but Hibernate doesn't like this. Try to run this code and you get an exception like:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column ...

(On a side note, I wish there was a better way to check this expressions at compile time, I know I could use the Criteria API for this with the static metamodel approach, but that ends up being very verbose with a significant learning curve that I cannot spare at the moment)

This issue is that Hibernate does not like that raw '=' expression in an ORDER BY clause (it is legal in SQL I believe, which raises questions about why I'm using Hibernate, which I'm not going to go into now)

Anyways, I went through a lot of variations trying to figure out how to get around this behavior (once I understood what the issue was, which took a little while), I seem not to be the only one to get this error:

How to order result of hibernate based on a specific order

(Although given that this post is from 10 years ago I might be the only one in recent times)

What I finally discovered is while you cannot use simple expressions like X = Y in order by clauses for Hibernate, however you can use a CASE expression, like CASE WHEN A THEN B ELSE C END, where A is a boolean expression which can be... X = Y. So I can rewrite this thing as:

SELECT * FROM Books b WHERE b.title LIKE CONCAT('%' , :param , '%') ORDER BY (CASE WHEN b.title = :param THEN 1 ELSE 0 END) DESC, b.publishDate

(Not sure if the parentheses are necessary, but I think it looks pretty ugly without them, and it is my blog so...)

And this works, and it gives me the ordering I want, so I'm happy with it. Now the trick is to remember this into the future where I might encounter this type of situation again, whether that will happen or not, well, that is a true mystery of life.

Anywho, take it to your head, take it to your heart, and remember Rand rocks!

Goodnight Folks!

And God Bless!

Tuesday, March 9, 2010

An overall sketch of Drupal 7

-get drupal root
-drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL)
 define constants
 define('REQUEST_TIME', $_SERVER['REQUEST_TIME']);
 _drupal_bootstrap_configuration()
  error reporting set
  drupal_environment_initialize()
   server vars read
   ini settings set
   error reporting set
  timer_start('page')
   start timers
  drupal_settings_initialize()
   load settings
   set up path
   session cookies
 _drupal_bootstrap_page_cache();
  init user
  load cache
  check if no-db opt else - get db and variables
  block_denied
  cookie init
  _drupal_bootstrap_page_header()
   bootstrap_invoke_all
   drupal_page_get_cache
   check cachable - get cache
    -maybe still run boot hooks
    serve page from cache
    -maybe still run exit hooks
    _drupal_bootstrap_database();
  -set up db
  -register autoload
    _drupal_bootstrap_variables();
  -init variables (either via cache or db)
  -load modules
    require_once DRUPAL_ROOT . '/' . variable_get('session_inc', 'includes/session.inc');
    drupal_session_initialize();
  -conditionally start session
    _drupal_bootstrap_page_header();
  -boot all
  check cache
  get lock and init
  if !cli - ob start and page_header
   -init static + send headers   
    drupal_language_initialize();
  -get languages
  -invoke modules
    require_once DRUPAL_ROOT . '/includes/common.inc';
  define more constants
    _drupal_bootstrap_full();
  -load files
  -set up settings
  -varify
  -init path
  -init custom theme
  -run init hooks
-menu_execute_active_handler()

Wednesday, January 27, 2010

Because if you weren't supposed to abuse the api it wouldn't start with a

Of course, I mean really!

Let me say I have little sympathy with hackers from an ethical, sociological, or philosophical perspective (that's a gross exaggeration, I mean ewww, but to explicate the matter would be an even more gross waste of time)

But when it doesn't hurt, or waste people's time, or screw with peoples stuff, etc., why not tae things apart and then put them together in different ways so you can take it apart again, and so on, and so forth, and thus I decided to conduct a reflection upon the window object of javascript.

Since my sign off would be far too down at the bottom of the page let me sign off before the mark, because I can. So take it to your head, take it to your heart and remember Rand rocks. Goodnight Folks!

And God Bless.

Reflecting on the infinity of madness within the sanity of the sands of time

My creativity takes many forms, writing stories, poetry, essays, this blog, twitter feed, speculating on math, history, philosophy, etc. and so on.

And among the items I left out of that statement, or at least left implied was programming

My feelings toward programming have gone up and down, risen and fallen, but the one constant is... actually there's been no special constant to my feelings about programming, except perhaps my affection for C++.

But nowadays, perhaps because my work requires coding and the like, I find programming often coming to my head... to the point of disturbance.

Dun, dun, dun...!

Now the disturbance isn't because of a lack of creative potential in programming, though it is partially because of a lack of creative realization in my programming, but perhaps more strongly it is that programming can often involve very minute details that are ultimately useless... indenting lines of code, optimizing loops and recursions, rehashing solutions to old algorithms solved a million times over by smarter minds... and it's very easy for me to program and in that manner. That is in a manner which is mindless and emotionless and detail obsessed... well, truthfully obsession is a dear friend to any artist, but with me and programming I feel it perhaps comes a bit easier...

Counter-strike!

I could tell many strategies old and new about counteracting this feeling and that one, etc. leading to a triumphant reversal of fortunes, just over the horizon... and I will give you this one... to overcome the risk of tedium, numbness and insanity that programming risks, I do now fill my veins with the purpose of bringing out worth and polish to my often odd and weird programming experiments, or at the very least, and perhaps more humbly, at least sharing my programming experience, if not to share knowledge of programming, perhaps just knowledge of a man programming.

(Stay tuned for posts in this vein, or not, whichever I feel like or what you feel like wiht the staying tuned-ness and all, I mean all I'm saying is if you're all stressed out, maybe you shouldn't stay tuned, maybe you should just get some sleep... or maybe I should)

Now enjoy the very nice jamming of a warm-hearted code monkey, just like me

And if I cannot share a great algorithm, perhaps I can share the desire for a great algorithm, the artist's drive...perhaps... only God knows.

So take it to your head, take it to your heart and remember Rand rocks. Goodnight Folks!

And God Bless.

Sunday, November 22, 2009

Tech Note: Calender fix got to get fixed

So I'm no something of a calender fix, don't ask me why that's just how I role. And thus here's some code for an xhtml calendar, php-gened + with a custom start and a custom end:


$start=array(4,19,1986);
$day = 24*60*60;
$ts = mktime(0,0,0,$start[0],$start[1],$start[2]);
$ts -= date('w',$ts)*$day;
$doctype = DOMImplementation::createDocumentType("html",
                "-//W3C//DTD XHTML 1.0 Strict//EN",
                "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd");
$doc = DOMImplementation::createDocument(null, 'html', $doctype); 
$html=$doc->documentElement;
$head=$doc->createElement('head');
$head->appendChild($doc->createElement('title','Timeline'));
$style = $doc->createElement('style');
$style->setAttribute('type','text/css');
$styledata = '
table {
width: 100%;
}
table td {
width: 12%;
}
';
$style->appendChild($doc->createCDATASection($styledata));
$head->appendChild($style);
$html->appendChild($head);
$body=$doc->createElement('body');
$html->appendChild($body);

$table=$doc->createElement('table');
$body->appendChild($table);
$thead=$doc->createElement('thead');
$table->appendChild($thead);
$trow=$doc->createElement('tr');
$thead->appendChild($trow);
$trow->appendChild($doc->createElement('th','Sunday'));
$trow->appendChild($doc->createElement('th','Monday'));
$trow->appendChild($doc->createElement('th','Tuesday'));
$trow->appendChild($doc->createElement('th','Wednesday'));
$trow->appendChild($doc->createElement('th','Thursday'));
$trow->appendChild($doc->createElement('th','Friday'));
$trow->appendChild($doc->createElement('th','Saturday'));
$endtime=mktime();
$tbody = $doc->createElement('tbody');
$table->appendChild($tbody);
      $trow=$doc->createElement('tr');
      $td=$doc->createElement('td');
      $h2=$doc->createElement('h2',date('F Y',$ts));
      $td->appendChild($h2);
      $td->setAttribute('colspan','7');
      $trow->appendChild($td);
      $tbody->appendChild($trow);
while($ts < $endtime) {
  $trow=$doc->createElement('tr');
  for($i=0; $i < 7; $i++) {
    $td=$doc->createElement('td');
    $td->appendChild($doc->createElement('h3',date('d',$ts)));
    $trow->appendChild($td);
    $pastday=$ts;
    $ts+=$day;
    if(date('d',$ts)      $tbody->appendChild($trow);
      $trow=$doc->createElement('tr');
      $td=$doc->createElement('td');
      $h2=$doc->createElement('h2',date('F Y',$ts));
      $td->appendChild($h2);
      $td->setAttribute('colspan','7');
      $trow->appendChild($td);
      $tbody->appendChild($trow);
      $trow=$doc->createElement('tr');
      for($j=0; $j < date('w',$ts);$j++) {
        $td=$doc->createElement('td');
        $trow->appendChild($td);
      }
    }
  }
  $tbody->appendChild($trow);
}
header('Content-Type: text/xml');
echo $doc->saveXML();
exit();

Is that the only way to do things?

No.

Is that the best way to do things?

Honestly probably not.

What would probably be the best is if I could bust out some awesome XPath and XSL 2.0 functions/elements - with the date-time/duration functions this would be trivial, so much so it would not make it to any trivia gameshow of any repute.

However, as your next trivia question - who's fighting this lameness - XQilla! Qilla on, dude, Qilla on.

So take it to your head, take it to your heart and remember Rand rocks. Goodnight Folks!

And God Bless.

Wednesday, November 4, 2009

Because even in the Calendar one can turn the page

Unless your on the web, unless you count turning the web page and I don't, but still here's a calendar:

2007

2008

February
Sun	Mon	Tues	Wed	Thurs	Fri	Sat
					01 Time to go down to the ground where the Buffulo Roam	02
03	04	05	06	07 And what of the Oriental in America?	08 Why plan when you can scheme? The End of Empires and other such tales	09
10	11 Quotes! Boats! And more of the Rand you love!	12 The Man from Tallahasse	13 Time keeps on Schemin' Istanbul once Constantinople	14 Happy St. Valentine's Day Happy St. Valentine's Day!	15 Here's a little sparkle	16
17 MC Esher be mathin' out	18	19	20	21	22 Cause the History don't stop coming	23 History repeats itself, first as tragedy, second as farce
24 India, a Chronology: Part I That's what I get for all that Scheming	25	26	27 India, A Chronology: Prologue	28	29

2009

April
Sun	Mon	Tues	Wed	Thurs	Fri	Sat
			01	02	03	04 Chasing down infinity
05 Space Oddity	06	07 Let not the sun be proud, lest I strike it from the sky	08	09	10	11 I follow he who has conquered death
12 Christ has Risen!	13 Twittering away the blues	14	15 Don't turn around again A little poetry for the emotionary	16	17	18
19	20	21 Sometimes not knowing is okay, Not this time though	22 Because this is my path	23 Ah, the savage winds of history	24	25 A good old fashioned shootout between me and my brain
26 When I get older... they'll call me freedom	27 Me, Eddy, and that Oriental-Looking Fellow	28 Time to be a sneaky oriental	29	30

June
Sun	Mon	Tues	Wed	Thurs	Fri	Sat
	01	02	03	04	05	06
07	08	09	10	11 Sooner or later everyone fails	12 Because the Rand Show is Comikier than yo'mama	13 Prithee, have thee heard of the Prism
14	15 What are you waiting for really	16	17	18 The Rand Show Comic : 300% More Comikier than Yo Mamma The Rand Show #1 The Rand Show #2 The Rand Show #3 The Rand Show #4 Even in defeat, one can reap the dividends of peace	19 DOOOOM!!!	20 Just a little lonely
21 Cause all is poetry... even the web	22	23	24	25 Here's to you Michael Jackson, Salute	26	27 Fool me once, shame on you, fool me twice, I the fool
28	29 Scar tissue that I wish you saw	30 Vacation is not quite what I wanted

July
Sun	Mon	Tues	Wed	Thurs	Fri	Sat
			01	02 Because I'm easy, easy like Sunday morning	03	04
05 Let me sing these songs of freedom... but not right now	06	07 Now to get	08 Unfortunately we need to sleep before Brooklyn	09 So what do you want?	10	11
12	13 Because Doom don't go away It's not just a crappy new Korn song	14	15	16 And when I am freedom will I be a waving flag?	17	18
19	20	21	22 Live and let doom	23 A meditation on Archimedes to satisfy the needy	24 To Africa, I think	25
26	27 I think there is a point here that you are missing	28	29 Waxing and waning before an upcoming storm	30 Character Runner Hex Converter	31

November
Sun	Mon	Tues	Wed	Thurs	Fri	Sat
01	02 Blaze ye stars, let me not see you whimper	03 Patience	04 Min Date Calendarier

The Rand Show