database

SQL joins as Venn diagrams alvin March 20, 2019 - 1:06am

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.

The three things a Business Analyst should think about during meetings alvin February 6, 2019 - 8:39am

When it comes to working as a business analyst, I’ve learned that there are just three things you need to keep in your mind when meeting with your customers (the project sponsor (gold owner) and domain experts (“goal donors”)) to gather requirements. These three thoughts will keep your meeting on track, lead you to the next question, and will help you know when your work is done.

Scala: Popular tools, libraries, and frameworks

Table of Contents1 - Build tools2 - Testing tools3 - Database4 - Functional Programming5 - Asynchronous/parallel/concurrent programming6 - Web frameworks7 - JSON8 - HTTP clients9 - Configuration/properties10 - Many more

This page is a collection of popular tools, libraries, and frameworks for the Scala programming language.

Back to top

Build tools

How to get the count of detail rows in a master/detail SQL SELECT query and subquery alvin April 28, 2018 - 4:03pm

As a brief note to self, I just used this SQL SELECT query to show both (a) master/summary information about each URL row in the urls database table, and also (b) detail information in the form of the number of rows in the url_clicks database table for each row in the urls table:

A database query for implementing paging in a Play Framework application

As a quick note to self, one way to implement paging in a Play Framework application is to use the limit and offset arguments in a MySQL query. This way you can show X number of objects on one web page, then give the user Next and Previous links so they can scroll backwards and forwards through the dataset.

To implement paging with a MySQL query, use the limit and offset query parameters, as shown in this example:

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.