select

Play Framework: Anorm SQL query syntax and examples alvin April 18, 2018 - 2:35pm
Table of Contents1 - Anorm2 - More background ...3 - A note about my coding style4 - SQL SELECT queries5 - A brief intermission6 - SQL INSERT with primary key (auto increment)7 - SQL INSERT with no primary key8 - SQL UPDATE9 - SQL DELETE10 - Complete example methods11 - A complete class12 - More information

In general the online Play Framework documentation is excellent, but one area where I needed more help was in seeing more examples of the Anorm syntax. To that end, here are some Anorm query examples, taken from a Play Framework application I worked on recently. But first, a little background about Anorm.

Creating a drop-down list in Play Framework 2.6 alvin April 11, 2018 - 10:37am

As a brief note to self before I delete this code, this is how you create a drop-down list in Play Framework 2.6:

@* adding 'size to helper.select creates a select/option area (single or multi-select) *@
@helper.select(
    form("category"),
    categories,
    'id -> "category",
    '_help -> "Select one, any one"
)

In that code, categories is passed into the Play template like this:

SQL select, group by, order by, and count (all in one) alvin April 4, 2018 - 7:57pm

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.

Scala: A ScalikeJdbc example (including SQL select and update statements) alvin April 1, 2018 - 8:53pm

If you want to see a relatively simple ScalikeJdbc example that performs SQL SELECT and UPDATE commands, I hope this example is helpful.

The background for this example goes like this:

SQL joins as Venn diagrams alvin March 24, 2018 - 2:58pm

I saw this “SQL joins as Venn diagrams” image on this Twitter page. To give attribution to the original author, it appears to have been created by C.L. Moffatt and documented in this Visual Representation of SQL Joins article. However, this article makes the case that Venn diagrams are not an accurate description of SQL joins.

How to make a color transparent in Gimp

I was recently working on some images of a mobile phone, where the mobile phone was surrounded by a solid color that I wanted to make transparent. Unfortunately the phone was black, and that color was dark gray, so when I made the dark gray a transparent color in Gimp using my usual approach — clicking Colors > Color to Alpha, then choosing dark gray — it had the effect of bleeding a lot of color out of the black phone. This was wrong.

A simple Java JDBC example that shows SQL insert, update, delete, and select alvin August 10, 2016 - 10:53am

Here’s a short Java/JDBC example program where I show how to perform SQL SELECT, INSERT, UPDATE, and DELETE statements with JDBC:

How to connect to a MySQL database with Scala and JDBC

This is an excerpt from the Scala Cookbook (partially modified for the internet). This is Recipe 16.1, “How to connect to a MySQL database with Scala and JDBC.”

Problem

You want to connect to a MySQL database (or any other database with a JDBC driver) from a Scala application using “plain old JDBC.”

Solution

Use JDBC just like you would in a Java application. Download the MySQL JDBC driver, and then access your database with code like this:

How to return a single record from a Play Framework Anorm SELECT query alvin April 14, 2014 - 8:13pm

I don’t know if there is a better way to do this yet, but if you want to select one record from a database table using the Play Framework and Anorm, I do know that this code works:

Drupal CCK form field - US states list

If you're ever creating a Drupal form using the CCK module and need to show a list of states (the United States) in a combo box (also known as a "select list" or "drop down" field), you'll want to have that list of states in the right format.

Fortunately (for you) I just ran into this problem, and created two different versions of CCK form state fields. This first one displays the full name of the state, and stores the two-digit state code in the database: