query – AgileAdam.com

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:

$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();

$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:

SQLSTATE[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...

1	SQLSTATE[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 10 but doesn’t work with LIMIT '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.

$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);

$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);

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):

-- News Item (news_item) <content type>
---- Categories (field_categories) <field collection>
------ Audience (field_term_audience) <taxonomy reference; unlimited values>

-- 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):

Setting a LIMIT on an UPDATE query in Drupal 8 / 9

The Problem

The Solution

Using GROUP_CONCAT to Combine Rows in a Drupal Query