By Alvin Alexander. Last updated: January 29, 2022
As a brief “note to self,” here’s an SQL query that I can use to generate a “What’s Related” block for a Drupal 8 website:
select tn.nid, count(tn.nid), u.alias, n.title from taxonomy_index tn, url_alias u, node_field_data n where tn.tid in (select tid from taxonomy_index where nid = 9423) and tn.nid = u.nid and tn.nid = n.nid and n.status = 1 and n.nid != 9423 group by tn.nid order by count(tn.nid) desc limit 8;
That query returns a dataset like this:
+-------+---------------+------------------------------------------------------------------------+------------------------------------------------------------------------------+ | nid | count(tn.nid) | alias | title | +-------+---------------+------------------------------------------------------------------------+------------------------------------------------------------------------------+ | 9332 | 6 | /misc/scala-functional-programming-simplified-contents | Functional Programming, Simplified (current contents) | | 9335 | 6 | /photos/functional-programming-simplied-kindle-sale | Functional Programming, Simplified (Kindle sale) | | 9570 | 6 | /scala/whats-easiest-way-to-learn-functional-programming | What’s the easiest way to learn functional programming? | | 10858 | 6 | /misc/functional-programming-simplified-pdf-sale-12-08-2019 | The PDF of Functional Programming, Simplified is currently on sale for $15 | | 10128 | 6 | /scala/hello-scala-introduction-to-scala-book | Hello, Scala book (an introduction to Scala book) | | 9492 | 6 | /photos/functional-programming-simplied-free-pdf-preview | A free preview of Functional Programming, Simplified | | 9624 | 6 | /scala/hello-scala-swift-introduction-to-scalable-programming-language | “Hello, Scala” - A swift introduction to a scalable programming language | | 10199 | 6 | /misc/cyber-monday-sale-functional-programming-simplified | Cyber Monday sale: Functional Programming, Simplified | +-------+---------------+------------------------------------------------------------------------+------------------------------------------------------------------------------+
I’m putting that query here for myself as I write a Scala replacement for Drupal, but if it helps anyone else, that’s great.