68

SelectQuery implements SelectQuery::__toString(), which is called in the contexts where a string is required. Consider the following code. global $theme_key; $query = db_select('block') ->condition('theme', $theme_key) ->condition('status', 1) ->fields('block'); print $query; Its output is the following one. SELECT block.* FROM {block} ...


47

You can use dpq() to display the query, and dpr() to display the result. $query = db_select('users','u'); $query->fields('u'); $query->condition('u.uid', 1042); $result = $query->execute()->fetchAll(); dpq($query); // Display the query. dpr($result); // Display the query result.


31

You can use this For making OR between 2 conditions ByDefault it is AND $query=db_select('users','u')->fields('u',array('uid','title','created','uid')); $query->join('flag_content','fc' , 'u.uid = fc.content_id'); $db_or = db_or(); $db_or->condition('fc.fid', '5' , '='); $db_or->condition('fc.uid', $uid , '='); $query->condition($db_or); $...


30

Figured it out! When defining the field in the MigrateSQLMap I can set a table alias for the field: $this->map = new MigrateSQLMap($this->machineName, array( 'labelId' => array( 'type' => 'int', 'not null' => TRUE, 'alias' => 'l', // it's the letter small case "L", not the digit 1 ) ...


25

Drupal 7 tables that can be excluded Here is a list of tables in Drupal 7 that you can either clear (to reduce the database size) or safely exclude to do a migration (as in the question about How to reduce the locally exported database size to get around my server import limit?): accesslog batch all cache related tables, such as: cache* cache_block ...


25

To answer your question in short, db_query is the fastest! Here are some reasons, facts and figure compiled from different questions, sources: A simple googling of this question, come up with following results: For simple queries, db_query() is 22% faster than db_select() For simple queries, db_query() is 124% faster than EFQ For queries with two joins, ...


25

For simplicity check drush status with verbose and debug parameters: drush -vd status If you're using different profile, don't forget to include it as -l http://foo. If you still have a problem, it's an issue with bootstrapping your Drupal. First check your SQL connection if it's working: drush sql-cli If it does not, check your db credentials in your ...


24

The {cache_form} table is a little funny, and behaves in a slightly different manner than other cache tables. If you take a look at drupal_flush_all_caches() you will see that {cache_form} isn't cleared. This is to protect in-progress forms from being nuked. The system_cron() function does take care of pruning out old data from {cache_form} along with the ...


22

Use drush dre -y [module] which disables, uninstalls and then re-enables the module. The -y flag makes it even faster, avoiding you have to press y and enter three times.


22

Use two separate andConditionGroup(): $query = \Drupal::entityQuery('node') ->condition('status', NODE_PUBLISHED) ->condition('type', 'custom_type'); $and = $query->andConditionGroup(); $and->condition('custom_taxonomy', 2); $query->condition($and); $and = $query->andConditionGroup(); $and->condition('custom_taxonomy', 8); $query-&...


21

After digging through Drupal Documentation found a solution in db_like API documentation page and SelectQuery:condition handler doc page.. For example $result = db_select('field_data_field_name', 'f')->fields('f', array( 'entity_id', 'field_name_value' )) ->condition('entity_type', 'user') ->condition('bundle', 'user') ->condition('...


21

To inject the database service add/alter the following methods in your controller class: use Drupal\Core\Controller\ControllerBase; use Drupal\Core\Database\Connection; use Symfony\Component\DependencyInjection\ContainerInterface; class PetmdController extends ControllerBase { protected $database; public function __construct(Connection $database) { ...


20

function mymodule_update_7100() { $items = array(); _field_maxlength_fix('field_name'); return $items; } function _field_maxlength_fix($field_name, $maxlength = 255) { _alter_field_table($field_name, $maxlength); $data = _get_field_data($field_name); $data = _fix_field_data($data, $maxlength); _update_field_config($data, $field_name); } ...


20

Another option is: global $theme_key; $query = db_select('block') ->condition('theme', $theme_key) ->condition('status', 1) ->fields('block'); print strtr((string) $query, $query->arguments());


19

If you look on the fieldCondition documentation page you will see the following warning: Note that entities with empty field values will be excluded from the EntityFieldQuery results when using this method. Checking whether a field exists or not has been added to entityFieldQuery in Drupal 8, but unfortunately won't be backported to Drupal 7. There are ...


19

Try this. $sql = 'SELECT sid, score FROM {search_index} WHERE word LIKE :term'; $result = db_query($sql, array(':term' => '%' . db_like($search_term)));


19

I found clicking on the reduce duplicates checkbox converted my INNER JOINs to LEFT JOINs: Before I was getting something akin to the following injected into my query: INNER JOIN {field_data_field_random_field field_data_field_random_field} ON node.nid = field_data_field_random_field.entity_id AND (field_data_field_random_field.entity_type ...


18

Read and follow this post; it assumes InnoDB. In particular, see the part saying to add the following to the settings.php file. $databases['default']['default']['init_commands'] = array('isolation' => "SET SESSION tx_isolation='READ-COMMITTED'");


18

Use addField(). Here's an example: $query = db_select('super_long_table', 'slt'); $query->addField('slt', 'mys_super_long_field', 'mslf'); Arguments passed to addField() are: Table alias Field - your long field name will be here Alias - your short field alias will be here Read more about it here.


17

My procedure is to use drush to clone the field first, then copy the field data with DB queries into the new field table. After I verify the contents of the cloned field, I delete the original field. I do it this ways because I think the drush method of cloning fields will always be at least as reliable as any cloning code I might craft myself, the data ...


17

SelectQuery::join(), SelectQuery::leftJoin() etc. don't return the query (they return the alias to the created JOIN), so they can't be chained. Just separate your code out like this: $query = db_select('hd_lights', 'l') ->fields('l') ->fields('s', array('stamp', 'message')) ->orderBy('`order`', 'ASC'); $query->leftJoin('hd_status', 's', '...


17

I'm doing lots of conditions like that in entity queries, I haven't encountered a problem yet. The main thing you have to be aware of is the timezone the data is stored, which is UTC. You need to convert it to the storage timezone (there's a constant for that), or your query will be off by a few hours. Here's an example based on something I'm doing, if you ...


17

Since the field is stored in a single table, this is how i get all empty record from my entity where my custom field is empty : $query = \Drupal::entityQuery('test_entity'); $query->notExists('field_date'); $ids=$query->execute();


16

This is done in the same way as in Drupal 7, you can add the database credentials in your settings.php file. $databases['default']['default'] = array( 'database' => 'drupal8', 'username' => 'username', 'password' => 'password', 'prefix' => '', 'host' => 'localhost', 'port' => '3306', 'namespace' => 'Drupal\\Core\\Database\\...


16

I found it ! I was not far away, I've just to reverse the last two parameters of the condition. $query = \Drupal::entityQuery('node') ->condition('type', 'order') ->condition('field_multiple_choice', 'doe', '!='); $nids = $query->execute(); $entities = \Drupal::entityManager()->getStorage('node')->loadMultiple($nids); As the ...


16

See: https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Select.php/class/Select/8.6.x $query->range(0, 500);


15

Here's how to use the Database API in Drupal 7 without writing a MySQL query: $query = db_select('zipcodes', 'z') ->fields('z', array('zip')) ->condition('z.city', $city) ->range(0, 1) ->execute(); $result = $query->fetchObject(); print $result->zip;


15

For me, the cause was different: some errors are not caught by Drupal's error handler. I used a new DateTime() in my settings.php (probably very rare) It generated an error because the date.timezone setting in my CLI php.ini wasn't set. To find the real error (this is the interesting part of the answer for all of you, I think) : Edit your module.inc, line ...


15

I would use the following code. foreach ($user_emails as $value) { $query = db_insert('banned_users'); $query->fields(array('email' => $value))->execute(); } Alternatively, you could use the following code. $query = db_insert('banned_users')->fields(array('email')); foreach ($user_emails as $value) { $query->values(array('email' =>...


15

In Drupal 6 you would use code similar to the following. db_query("INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (%d, '%s', %f)", 5, 'hello world', 3.14); $id = db_last_insert_id('mytable', 'id_fieldname'); The Drupal 7 equivalent code is the following. $id = db_insert('mytable') ->fields(array( 'intvar' => 5, 'stringvar' =>...


Only top voted, non community-wiki answers of a minimum length are eligible