database

Play Framework: An Anorm ‘singleOpt’ query example

It’s embarrassing how long it took to get this to work, but if you ever need to select a single row from a database using Play Framework (version 2.6), Anorm, and singleOpt, I hope this example is helpful, and will save you time and frustration:

How to use SQL SELECT, GROUP BY, ORDER BY, and COUNT (all in one)

Don’t tell anyone, but my SQL skills are pretty average these days, at best, mostly because I haven’t had to do anything hard in a while. But just now I was happy to write this little SQL SELECT query that does a GROUP BY, an ORDER BY, and a COUNT, yielding the results shown in the image:

select nid, count(nid) from term_node
where tid in (3,1,11,10,9,8,7)
group by nid
order by count(nid) DESC

I’m going to use this query — or one very similar to it — to get a list of nodes (nid) that have the most tag ids (tid) from the list of tid in the query. In theory, the nodes (blog posts) that have the most tags in common should be the most related to each other. So, in my Scrupal6 replacement for Drupal 6, this query is a way to get “related” content for a given blog post. (The tid list shown comes from node id 4, so I need to also exclude nid=4 from the results. I also need to add a limit clause to the query.)

If you ever need to do a group by, order by, and count in one SQL query, I hope this example is helpful.

Android Room, database I/O, and Java 8 threads

I just started working with the Android Room database persistence library, and since you’re not supposed to run things like database queries on the main thread (the UI thread), I was looking at other ways to run them.

In general, you probably won’t want to run database queries using a Thread, but just to see how Room works, I wrote this Java Thread code, and confirmed that it works as expected:

An example of Android StrictMode output (with improper database access)

I was just working with an example of how to use Android’s new Room Persistence Library, and the example I was working with ran some of its code on the main Android thread, also known as its “UI thread.” I knew this was bad, but I wanted to start with someone’s example, and then figure out a good way to get the Room method calls to run on a background thread, such as using an AsyncTask. (The Android docs don’t specify a “best practice” for this atm.)

How to search for a string in all fields of every table in a MySQL database

Here’s a cool tip: if you want to search for a text string in all fields of all tables of a MySQL database, you can use phpMyAdmin to do this very easily. Here are the steps to search every MySQL/MariaDB database table.

1) Select the desired database

The first step is to select the database you want to search. Don’t select a table — just select the database you want to search. (If you select a table you’ll get a different search form in Step 2.)