-
Rendering null boolean values with Grid4PHP
I’m a big fan of Grid4PHP for adding a quick CRUD user interface to a MySQL database.
I have a lot of boolean columns, like “is_telehealth” that need to start as NULL until we make a determination about the field’s value. Grid4PHP renders null booleans as “0” in the table/edit form. This is misleading, given it’s not actually a 0 value. If you add the ‘isnull’ attribute to the column it lets you save the null value, but it still renders as a “0”.
Using a custom formatter I was able to render an empty string (nothing) instead of “0” for these null booleans:
1234$boolNullFormatter = "function(cellval,options,rowdata){ return cellval === null ? '' : cellval; }";$cols[] = ['name' => 'is_in_gmb', 'isnull' => true, 'formatter' => $boolNullFormatter];$cols[] = ['name' => 'is_property', 'isnull' => true, 'formatter' => $boolNullFormatter];$cols[] = ['name' => 'is_telehealth', 'isnull' => true, 'formatter' => $boolNullFormatter]; -
Setting a Specific External Port for MySQL in Lando
Lando sets an external MySQL port dynamically by default. This means every time you (re)start a lando app it gets (potentially) a different external MySQL port. This is an annoyance as you have to change your port in TablePlus, SequelPro, or whatever MySQL GUI you’re using.
There is a simple fix: use a local lando file to override the forwarded MySQL port. These steps assume you already have a working .lando.yml file.
Step 1: Create a local lando file: .lando.local.yml
123services:database:portforward: 3307 <-- Specify an available port hereStep 2: Run lando rebuild if you’ve already started the site in the past
Step 3: Verify the settings by running lando info
1234567891011121314151617181920212223242526272829adam@acmbp sandbox % lando info[ { service: 'appserver',urls:[ 'https://localhost:50126','http://localhost:50127','http://sandbox.lndo.site/','https://sandbox.lndo.site/' ],type: 'php',healthy: true,via: 'apache',webroot: '.',config: {},version: '7.3',meUser: 'www-data',hasCerts: true,hostnames: [ 'appserver.sandbox.internal' ] },{ service: 'database',urls: [],type: 'mysql',healthy: true,internal_connection: { host: 'database', port: '3306' },external_connection: { host: '127.0.0.1', port: '3307' },healthcheck: 'bash -c "[ -f /bitnami/mysql/.mysql_initialized ]"',creds: { database: 'main_db', password: 'main_pass', user: 'main_user' },config: {},version: '5.7',meUser: 'www-data',hasCerts: false,hostnames: [ 'database.sandbox.internal' ] } ]Step 4: Add .lando.local.yml to your .gitignore file
This site will always get the port you specified if it’s available when you start the app. You can use this port in your MySQL gui, scripts, etc.
-
Setting a LIMIT on an UPDATE query in Drupal 8 / 9
Here’s a quick tip regarding Drupal database manipulation. Specifically, I needed a way to flip a boolean value (from 1 to 0) on the first N rows in a database table that matched a specific set of conditions.
The Problem
It seems you cannot enforce a LIMIT on an UPDATE query using a static query in Drupal.
Here’s what I was trying to do:
1234567$db = \Drupal::database();$query = $db->query('UPDATE {mytable} SET ondemand=:zero WHERE ondemand=:one ORDER BY imported_at ASC LIMIT :limit', [':zero' => 0,':one' => 1,':limit' => $qtyToConvert,]);$query->execute();This throws an error. I believe the issue is caused by the variable substitution wrapping the limit value in quotes. The error message starts with:
1SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1...If you run this query manually in MySQL, it works fine with
LIMIT 10but doesn’t work withLIMIT '10'(it throws the same error shown above).The Solution
Given dynamic queries are favored over static queries, and that this static query doesn’t work, I ended up getting to the finish line with two dynamic queries.
I have tested the solution on 10,000 records and it took < 6 seconds to process the entire request on my local dev lando site.
“code” is a unique identifier column in the database table.
12345678910111213$db = \Drupal::database();$query = $db->select('mytable', 'mt')->condition('ondemand', 1)->orderBy('imported_at')->fields('mt', ['code'])->range(0, $qtyToConvert);$codesToReset = $query->execute()->fetchCol();$db->update('mytable')->fields(['ondemand' => 0])->condition('code', $codesToReset, 'IN')->execute();return count($codesToReset); -
Snippet: MySQL table sizes ordered by largest to smallest (in MB and Row Count)
Show All Tables By Size in MB
1SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC;Show All Tables By Size in MB (if > 1 MB)
1SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() AND (data_length + index_length) > 1048576 ORDER BY (data_length + index_length) DESC;Show All Tables By Number of Rows
1SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() ORDER BY table_rows DESC; -
Using GROUP_CONCAT to Combine Rows in a Drupal Query
Recently I was working on a D7 to D8 migration. I was trying to import news items and their taxonomy terms (among many other things). To make things simple I wanted the query results to have (for each node) a single field that contained a comma-separated list of taxonomy terms which I could then explode during the row processing. Using GROUP_CONCAT we can achieve this! Let’s break it down:
The Drupal 7 site has the following structure (focusing on the important bits for this blog post):
123-- News Item (news_item) <content type>---- Categories (field_categories) <field collection>------ Audience (field_term_audience) <taxonomy reference; unlimited values>The migration relies on the d7_node migrate source plugin, which basically queries for nodes of a specific type. The query object looks like this (simplified for this blog post):
-
Sorting ActiveCollab tasks
Our company has been using ActiveCollab for years. It’s a great project management tool and has improved considerably over the years. We have managed hundreds of projects and more than 12,000 tasks using ActiveCollab. Occasionally we want a little more from the system than it offers out of the box. I’ve built an importer (shameless plug!) to make it simple to add tasks in bulk. I’ve written some automated task creation scripts. I’ve even tried my hand at writing a 3rd party integration module. Sometimes, though, you just need a simple tweak to tailor the system to your needs. That’s what this post is about.
-
Copying Values Between Fields on Thousands of Entities
In a nutshell, I had a List (text) field that I needed to convert to a Text field (so that I wasn’t locked in to specific values anymore). Instead of trying to modify the field to convert it from one type to another, I decided to create a new Text field and populate it with the data from the List field (modifying it a bit first). Here’s the script I used to do this. Admittedly it could be cleaner, more efficient, etc. but it’ll show you a few things, and it got the job done for me.
If you choose to run with
drush scr update_nodes.phpyou can leave out the top 3 bootstrap lines. -
Proximity by City or Zip Code in Drupal 6 with Location and Views
The location module for Drupal 6 is a robust module. On most projects, it gives us 100 percent of what we need. Proximity searching based on zip code is built-in and fairly painless to setup. I’ve recently been tasked to also allow proximity searching based on City. As you might imagine, the request is very similar. Both methods require the use of latitude and longitude (decimals). The difference is that instead of querying the database for latitude and longitude based on zip code (it’s usually termed postal code in Views, Location, etc.), we’re asking for the coordinates of a city. You’ll find that many cities have multiple zip codes, each of which is a row in the location module’s zipcodes database table. In this example, I’m not giving a real honest attempt at solving this issue, but rather I just return the first coordinate that matches the city.
-
db_query() and db_placeholders() example #1
Have you ever wondered how to properly build a query like this in Drupal:
1SELECT nid, type, title FROM node n WHERE n.type IN('page','story');This requires the use of db_placeholders() to create the placeholder ‘ ‘, ‘ ‘, etc.
1$result = db_query('SELECT nid, type, title FROM {node} n WHERE n.type IN(' . db_placeholders($node_types, 'text') . ')', $node_types);where $node_types is an array of node types.
-
Views Taxonomy: Get Terms with Associated Nodes
This example serves as both an example of how to alter a Views2 query, as well as how to use the get_terms_by_count() function I’ve written.
Unfortunately there is not (at present) a Views2 taxonomy filter that lets you “Get only terms with at least X associated nodes.” We had a client request that terms without associated nodes be hidden. This was actually more complex than it sounds, but the solution led me to a whole new level of Views2 understanding. Views2 has a hook called hook_views_query_alter() that lets you alter a Views2 query before it is executed. This is exactly what we needed to do in order to only pull terms with associated nodes. Specifically, we needed to add an additional WHERE clause to the query.
