2

I have data in a table that looks like this:

|-------------------------------------------------------|
|ID|              Data                                  |
|--|----------------------------------------------------|
| 1|{"key":"value",...,"image":"1234567",..."key:value"}|
| 2|{"key":"value",...,"image":"",..."key:value"}       |
| 3|{"key":"value",...,"image":"9876543",..."key:value"}|

In my migration I only want to import items that have an image ID in the Data column.

Previously I was doing this in my migration class query() method:

class myMigration extends SqlBase {

  public function query() {
    return $this->select('data_table', 'd')
      ->distinct()
      ->fields('d', ['ID', 'Data'])
      ->condition('Data', '%' . db_like('"image":') . '%', 'LIKE');
  }

  public function fields() {

    $fields = [
      'ID' => t('ID'),
    ];

    return $fields;
  }

  public function getIds() {
    return [
      'ID' => [
        'type' => 'string',
      ],
    ];
  }

This was also selecting rows where the image value was empty in the Data column.

This query in SQL works for selecting the correct data, but I cannot get it to work with the query() method in my migration:

SELECT ID, Data    
FROM data_table
WHERE Data REGEXP '"image":"\d*[^>"]';
3

I was able to use REGEXP operator in a normal condition() method, although it is not stated in the documentation.

The answer was found on this example.

Solution:

  public function query() {
    return $this->select('data_table', 'd')
      ->distinct()
      ->fields('d', ['ID', 'Data'])
      ->condition('Data', '"image":"\d*[^>"]', 'REGEXP');
  }
1
  • The documentation wasn't necessarily wrong, but it wasn't very explicit; I've added a sentence calling out REGEXP as an example of a database-specific operator that will be passed through. – Brad J Aug 1 '19 at 21:17
2

You should be able to use the where() method:

return $this->select('data_table', 'd')
    ->distinct()
    ->fields('d', ['ID', 'Data'])
    ->where('Data REGEXP \'"image":"\d*[^>"]\'');
0

Don't use SqlBase Class and do yourself the query. Your Class can extends SourcePluginBase.

  use Drupal\Core\Database\Database;

  $database = Database::getConnection('default', $your_database_key_in_settings_php);
  $query = $database->query($sql_query);
  $data = $query->fetchAll(\PDO::FETCH_ASSOC);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.