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! 

I dislike Donald Trump

 I hate that this is my first post in a long while, but if you wait for the perfect post you may very well wait forever.

I really dislike Donald Trump. I hesitate to use the word hate, partly on general habit, but partly because hate is an intimate personal feeling, ultimately, my dislike is pointed at a political persona, and it is a persona that is neither unique geographically nor historically. So hate seems inappropriate. However, I strongly dislike Donald Trump's hyper-nationalist anti-instutionalist populist persona fiercely.

I am myself a passionate nationalist, I believe in America and I have a passionate love for my country. I do not mind hyper-nationalism by itself, I find it excessive but I can sympathize with the feelings behind it. But I dislike how Donald Trump focuses the nationalism of himself and his followers on an exclusionary vision of America, one that must hide itself from the tired, poor, huddled masses yearning to breathe free, lest the troubles overseas infect us. I like to subscribe to a vision of America that welcomes the oppressed and takes the demons chasing them as challenges to be conquered rather to be appeased.

But that is a matter of ideals. In concrete terms, no one wants no immigration, very few are willing to realistically grapple with the challenges of open borders, everyone can admit that some people need asylum and that there are some people who will fake asylum and there are wide swaths of gray areas. Most disagreements in immigration come down to differences in degrees of policies, while adding and removing special cases from a system riddled with special cases. I dislike that Trump throws away the nuance of this in his rhetoric (although he is hardly unique in this, the degree and frequency to which he disregards nuance are exceptional, and degree and frequency matter), and is unwilling to wrestle with the full complexity of the situation in his policies.

I dislike Trump's disregard for the long term, his policies focused on short term goals while never asking how will these policies be followed by the other party when the other party inevitably comes to power in the future, because this is not the end of history. It irritates me in profound ways how Trump acts like this is the end of history, that the fixes in corruption and deep economic issues are something that can be fixed in four years without caring what the dismantling of walls between politics and the administration of government do to the long term. Again, Trump is not unique in this, but he does this more frequently and to a larger degree, and frequency and degree matter.

I dislike the emptiness of the winner takes all philosophy he pursues, when that does not look at the costs to the institutions of democracy, which while not perfect in this country, are priceless.

I could go on, but this is probably longer than most people like to read already. Donald Trump is not the devil, not every single thing he does is wrong or every single policy incorrect. In many ways, Donald Trump is the outgrowth of bad tendencies among politicians that have been growing for a long time here and overseas, however, his degree and frequency of those tendencies are exceptional and that matters.

History is long. Despite my nationalism I am not naive, one day the United States of America will become a fading memory. But just as I cherish the lives of my loved ones despite their inevitable return to the dust, I am concerned with the fate of America. Donald Trump did not ruin America in his first term and will not destroy America in his second, however, he may do lasting damage to the institutions and ideals of America that may set the stage for further damages down the line. I think he is bad for America, and that concerns me.

I could be wrong, and I really dislike how Trump rarely seems to consider the possibility, but I admit I cannot see into his head, nor can I predict the future. But I have my feelings and have stated them, and I will hope for the best, knowing the future will inevitably be different and stranger than I can imagine. In the end, all I can say is God bless America and I hope, whether I am right or wrong, America makes the right choice.

On the road again

Heading out on the road again, nice thing about being confined some place is it gives you time to do things, not the things you really want to do, but some subset of things

Another nice thing is it reminds me of that Bob Seaver song (although I first heard it via Metallica, and to be honest I still probably prefer that version, I still like my metal): Turn the Page

An excellent song that really lends itself to AMVs:

https://youtu.be/Jl6u6u8LEHM

Of course Metallica’s video for this is also highly recommended 

https://youtu.be/qPOTEs_yTJo

Excuse me if the video embeds don’t work, trying out a new blogging app.

Also, Excuse me as I kiss the sky!

Anywho, moving on, it is important to remember that I woke up at 4:45 AM and that is not a good time and it is starting to crush me with the weight of time and sleep deprivation.

So… there are quite a few good song segways there, but Rand tired

The 4:45 AM is really starting to hit me now.

Bobba booy

Anyways, take it to your head, take it your heart, and remember Rand rocks.

Goodnight Folks!

And God Bless.

Kicking it up with InfiniteRand

Long time since I've posted anything, something of a long time since I've written anything. This prompts the question of who would have any interest in reading what I have to write. My brother gave me a good suggestion, thinking about writing for my daughters. While I try to be open with them about a lot of things, there are some things I keep to myself, and even more that I just do not have the time or occasion or expression to share. At the very least, what I write might be useful for them to have, even though I suspect even if they have it they are probably not likely to read it, after all how many go through all of the family photos except once in a blue moon, but in their lives they are likely to have a blue moon once and again.

Of course, that makes me think it's probably best to have some backup set up in case Google gets tired of this blogspot abandonware or gives some opt-in prompt that I neglect in my old age, consigning my words to oblivion.

Anyways, that is the minimum, I have two intelligent and curious little girls, and whether or not they read my words having the words would be a comfort, and it might be a discomfort for them to think that I stopped writing around the time I had them (the timelines do not line up for that, in fact my last post was before I met their mother, but people have a way of overlooking blurry timelines in these type of things, although I hope that is something I can teach them to avoid). So there is that minimum. And perhaps there are more that might enjoy my words, perhaps, perhaps, perhaps... (I want to say that is from some British sitcom but I can't recall the name, maybe Coupling?)

Why I stopped or at least paused my writing? Many reasons, if I'm honest sloth is among them, fear of writing something bad, fear my creativity might be gone, apathy, feeling that my writing is worthless (so rather than giving up my writing for the sake of my kids, my kids have given me a counteraction against one of my hindrances, and yes kids, I expect you to learn all of those big words), questioning my place in the big scheme of things. None of that is especially new (well me not writing is not especially new at this point).

Perhaps what it is, and there is a little supporting evidence of this, is at the time of my life when I decided to get serious about being married, I decided to put focus on the rituals, procedures, and habits necessary to help me find a wife. I can go into this more a different day, but much of this rubbed against my anxieties, social and otherwise, and it took a considerable mental effort to push on this.

Essentially, to make it short, making a concerted effort to find a wife was stressful for me.

This is also why I resigned as a member of the national board of the MCYM, despite that being one of my preferred social and to some degree creative outlets (more on that a different day, in fact pretty much all of these sentences could be fleshed out into a paragraph or two, but I have promises to keep...), the stress of the obligations of my duties as a national board member (which perhaps I took too seriously) was a weight that I knew would be difficult to me to bear in addition to these new stresses.

While I did not make a decision to stop writing them, that stress did decrease my output (although it would be interesting to go into my notebooks and to see to what degree at what point, of course for that be accurate I would need dates on everything which I don't have). Then the stress of marriage, married life, eventually parenthood, all vital things I wanted in my life, these did not directly work against me writing but what they did do is leave less energy to fight against the old fears and anxieties associated with writing, which as always grew the longer I stayed away from writing. While this can be looked at as some unfortunate accident, I do remember at times reflecting on the trade-off between the married/fatherly life I was pursuing and the effort needed to sustain me as a writer, ultimately I made a choice. Not a choice to stop writing, but a choice to pursue the family life I wanted and believed was a vital part of my life's vocation, even if my writing life suffered.

I made a choice and I still believe it was the correct one. I look at my wife and my girls, and we have built something beautiful together, something blessed by God and sacred.

There's some simplification there, and so other factors that I know played a role, but I think this assessment is true enough to let it stand.

This leads me to say, there is no reason why I shouldn't write now, although it shouldn't be at the expense of this life I have built, if the two do not work against each other, why not write? (From the assessment above, you might say well it sounds like they do work against each other, but the relationship between different responsibilities, hobbies, duties and stress is more complex and subtle than that, they will sometimes work against each other, sometimes they will help each other, and sometimes each must be given a certain amount due, and sometimes some must be pushed to the background)

And it is worth coming back to the minimum reasons for me to write, in addition to my girls, there is a certain feeling of sacred duty to God. I often think of the story of the king who gave his servants different sums of money to invest, God has given me some talents of creativeness and some tendency toward writing, and so I ought to use it if I can, and share it if I can. God has given me other blessings more precious to me in the form of my family, and that sacred duty comes first. But if the clouds of my anxiety and the mysterious inner workings of my mind align just right, so that writing is possible without the neglect of family, then I ought take the chance, and I think that it is God's will for me to do so.

More could be written about this, but time conspires against me here. I do have other duties related to my family life (to be less vague, I have to do my job), while I have taken time away from that to write this, in some ways writing has cleared my mind more to help me tackle those duties. If I used that as an excuse to spend all my time away from my work, it would be counter-productive, and so I must set a limit and come to an end. (Although the story never ends...)

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

And God Bless.

And a goodbye to India for this year

And so my India trip comes to an end. Doom, doom, doom!

As I remarked on Twitter (I really should make fewer purely personal posts on Twitter, they tend to be well, a little lame, or at least irrelevant to those who dwell on Twitter, least by my calculus of interest, but it is hard to resist the temptation, I could elaborate on this quite a bit, but I don't feel like it), my trip to India is coming to an end, and it was less than I may have hoped for, but well more than good enough.

That's life most of the time. It's a fact that's not often appreciated enough, at least I don't (see this is what the personal Twitter posts lack, a meandering introspective rant loosely connected to personal events). I can cite opportunities missed, imperfect aspects of my life, but all and all I have it pretty good, and things could very well be otherwise.

I have good friends, good family, and a good God who loves me, that's a pretty good life.

But even though this makes discontent unnecessary, discontent is not necessarily a bad thing. I can do better, I can do more, and I think I ought to, I think... although I am not sure of this, I think God has more planned for me, that is, I think God has plans to make me a better me which would do good stuff in His name, etc., etc., etc.

Now that I've gone well past sentimental, let me continue. My stated goal for my trip in India was to spend time with my grandmother (my Umachi - people have told me that Umachi should be spelled with an "A" in front, but I prefer the "U" and I'm contrarian that way), visit family, relax, write some, work on a family tree website, other stuff, and ultimately spiritual renewal.

Spiritual renewal... I feel a little closer to God, I suppose I feel I am more energized and determined to do God's work (that sounds more grandiouse than it is, and while I do enjoy dabbling in the grandiouse, I feel anything done keeping the service of God in mind, I have and will elaborate on this elsewhere). But do I feel that full sense of spiritual renewal that I've felt during other trips to India. No, not really. Ultimately this is my fault, I did not take enough opportunity to pray or read the Bible. That is something I need to correct in my life.

Writing... I did a little, not very much though. Again, not as much as in other trips. It's not so much writer's block as... sloth? Maybe, maybe a fear of writing (I'll go into this elsewhere) coupled with an inadequate sense of purpose and drive.

I did get some of my to-do list done. I worked on some websites I wanted to work on, sent some emails that I wanted to send, talked to people more than I have been lately. So overall I built up a little more momentum in my life toward doing the things I want to do. And I did spend a lot of time with my grandmother and with my family, and even though I often did not understand the language of my family members, I tried to treat them with love. In deepening my love of family, in that aspect, I did achieve a degree of spiritual renewal, for I believe God dewells in love (I say that in the poetic sense, the theological dimensions of this, I'll leave to St. Paul). Perhaps it's best I didn't go into overdrive and go deeply into a dozen different projects. Doing so has not served me well in the past, over-commitment has usually been prelude to the fall, perhaps because I commit the sin of pride in imagining I can do everything, often not trusting God to take care of the world, instead trying to grapple with it on my shoulders.

So a good trip, not a perfect one, but a very good trip.

But there was a question hanging over it, one which I must grapple with. When am I going to get married? But that is perhaps better left for a different post.

By the way, Mery Christmas and Happy New Year! (I link here to a rendition of "Auld Lang Syne" (By the way, this video seems to repeat the song, and although it's a terrific performance and the video itself has a bunch of factoids about the song, you may want to cut out after the first 4 min or so) - which is apparently based on a poem by Robert Burns which can be found here - http://www.carols.org.uk/auld_lang_syne_burns.htm).

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

And God Bless.

This is your brain on sleep

Stupid sleep and me not writing with the stuff and the lack of sleep!

Doom I say Doom!!!!!!!!!

Flying

Flying, flying, flying
Got to keep on flying
Fly some more
Fly some more
Fly some more, rawhide!