Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I have a simple merge query, with one key and three fields, something like:

$query = db_merge('product')
->key(array(
  'id' => $id_product,
))
->fields(array(
  'name'   => $form_state['values']['name'],
  'symbol' => $form_state['values']['symbol'],
  'weight' => $form_state['values']['weight'],
));

Usually it works all right, but some combinations of data passed to it and data already in database cause me headache. I'm sure I would be able to know what's wrong and why, if only I could grab actual SQL query that goes to my database.

Usually I would do:

  kpr((string)$query);

It works well for insert for example, because insert has __toString() well implemented. Sadly, implementation of MergeQuery::__toString is somewhat nonexistent:

public function __toString() {
}

It isn't even a proper implementation of this magic method, as it should always return string, but OK, I can assume it was on purpose. Anyway, this changes nothing: I can't simply cast it to see it.

What other options do I have, to see queries generated by db_merge() / MergeQuery for database interaction debugging?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

A MergeQuery just runs a SELECT and INSERT internally - there isn't a single query string available, so the __toString() method doesn't make sense (which is probably why it's short-circuited).

SelectQuery and InsertQuery both implement __toString() fully, so your best bet for debugging would be to temporarily add some code to MergeQuery::execute() to dump the $select and/or $insert query before they're run, so you can inspect the query string.

share|improve this answer
    
It seems it's working. I would prefer to avoid hacking core, but for now it's good enough. –  Mołot Apr 25 at 8:47
    
Problem is that InsertQuery doesn't invoke an alter hook. You could subclass a bunch of database classes and provide your own debuggable implementation of the relevant methods, but it seems overkill for one-time debugging –  Clive Apr 25 at 8:49
    
I fully agree... It worked and was straightforward enough, yet I feel reluctant to accept hacking core as an answer. I mean, upvoted of course, but I'll leave it unaccepted for now, in hope of attracting non-hackish answers. –  Mołot Apr 25 at 10:06
1  
@Mołot Temporary debug code is hardly 'hacking core'; logically the only solution without changing core files is to replace the database class with your own (quite a bit of work). I'm not sure another solution is physically possible, looking at the code (but yeah, of course, don't accept this if you're not happy with it :) –  Clive Apr 25 at 10:07
    
I have seen disastrous results of "temporary debug", when person who debugged accidentally "cleaned up" one line too much... I'm not sure if it's possible to do it any other way. But hooking variable_set() without hacking core seemed impossible, too, and yet it wasn't even that hard, it only needed one nice spark of craziness ;) –  Mołot Apr 25 at 10:11

Ok, I realise that this is going against the principle of OOP however...

function debug_merge_query($query, $show_placeholder_values = false) {

  // Unprotect object.
  $query_arr = (array) $query;
  $query_str = json_encode($query_arr);
  $query_str = str_replace('\u0000*\u0000', '', $query_str);
  $query_arr = (array) json_decode($query_str);
  //pdie($query_arr);

  if(!isset($query_arr['table'])) {
    drupal_set_message('You cannot execute a merge query whilst it is being debugged.');
    return false;
  }

  // Extract variables
  $table = $query_arr['table'];
  $fields = array();
  $first = true;
  foreach($query_arr['insertFields'] as $k => $v) {
    if($first) {
      $condition_key = $k;
      $condition_val = $v;
      $first = false;
    } else {
      $fields[$k] = $v;
    }
  }

  // Build select query.
  $select_query = db_select($table, $table)
    ->condition($condition_key, $condition_val);
  $select_query->addExpression('1', 'expression');

  if($show_placeholder_values) {
    dpq($select_query);
  } else {
    dpm((string)$select_query);
  }

  $row_exists = $select_query->execute()->fetchField();

  if($row_exists) {
    // Build update query.
    $update_query = db_update($table)
      ->fields($fields)
      ->condition($condition_key, $condition_val);

    $update_query_str = dpq($update_query, TRUE);

    if($show_placeholder_values) {
      $update_query_str = replace_placeholders($update_query_str, $fields);
    }

    dpm($update_query_str);

  } else {
    // Build insert query.
    $fields[$condition_key] = $condition_val;
    $insert_query = db_insert($table)
      ->fields($fields);
    $insert_query->preExecute();

    $insert_query_str = (string) $insert_query;

    if($show_placeholder_values) {
      $insert_query_str = replace_placeholders($insert_query_str, $fields);
    }

    dpm($insert_query_str);

  }

}

function replace_placeholders($query, $values) {
  $x = 0;
  foreach($values as $k => $v) {
    $replace = is_numeric($v) ? $v : '"'.$v.'"';
    $query = str_replace(':db_insert_placeholder_'.$x, $replace, $query);
    $query = str_replace(':db_update_placeholder_'.$x, $replace, $query);
    $x++;
  }
  return $query;
}

So to test this function you can use the following code:

$id_product = 1;

$query = db_merge('product')
  ->key(array(
    'id' => $id_product,
  ))
  ->fields(array(
    'name'   => 'test',
    'symbol' => 'apple',
    'weight' => 12,
  ))
  //->execute()
  ;

debug_merge_query($query, true);
share|improve this answer
1  
I think this proves my point - a patch to core is far better than reproducing all this code in procedural functions :) –  Clive Apr 25 at 12:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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