82

That's not correct, the default is AND. To use or, you need to use: $or = db_or(); $or->condition(); $or->condition(); $query->condition($or); But the main conditions on the query are definitely joined with AND.


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.


40

You have to use hook_views_pre_execute and with Devel installed use dpq to view the SQL String: function hook_views_pre_execute(&$view) { dpq($view->build_info['query']); }


38

In the specific example, you should simply write the condition as: $query->condition('n.language', 'ab', '<>'); In the generic case, where you need to select the rows in a database basing on the values returned from a sub-query, you should consider what follows: "NOT IN" is accepted as operator from SelectQuery::condition(). In fact, the ...


38

If you wish to fetch only one result you could use fetchField with db_query to fetch the result, e.g.: $zip_code = db_query("SELECT zip from {zipcodes} WHERE city = :city LIMIT 1", array(":city" => $city))->fetchField(); You could also fetch the values retrieved from the query's returned result source using options like fetchObject() similar to ...


31

The database API actually has a preferred way to add expressions of this type, namely the SelectQuery::isNull() and SelectQuery::isNotNull() functions. You can use them like this: $query->isNull('f.fid'); $query->isNotNull('q.field_question_value');


31

9 times out of 10 this error is caused by an insufficiently large max_allowed_packet setting in your MySQL server's my.cnf file. The standard default for MAMP in my experience is 1M, which isn't usually enough for Drupal (especially considering the size of some of the cache strings it generates). Try increasing that setting gradually to see if the error ...


30

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

You can't add extra joins to an EntityFieldQuery directly (it's not supported), but you could add a tag to the query, implement hook_query_TAG_alter(), and add the join manually when the query is converted down to a standard db query. This isn't tested but will probably get you most of the way there: $query = new EntityFieldQuery; $query->...


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

This is off the top of my head so use caution... $query = db_select('node', 'n') ->fields('n', array('nid', 'title')) ->condition('n.type', 'test') ->condition('tn.tid', array(23, 37)) ->condition('n.title', '%' . db_like('name') . '%', 'LIKE') ->condition('n.status', 1) ->groupBy('n.nid'); // Add the COUNT expression $query-&...


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


24

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, ...


23

According to drupal 7 Database documentation the default is AND condition, but if you want to use OR condition then use this; $db_or = db_or(); $db_or->condition('n.type', 'event', '='); $db_or->condition('n.type', 'article', '='); $query->condition($db_or);


23

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


22

I have seen a solution of this problem. The idea is to use addTag() in query and implement hook_query_TAG_alter(), where you have good old SelectQuery object.


22

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


21

Use the backup & migrate module, it comes with good defaults for skipping not necessary data. By default it generates a DB backup without cache, watchdog and some other tables. If this does not help have a look with phpMyAdmin and tell us which tables have a lot of entries.


21

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.


21

Using db_query() is a Drupal 6 method, which is not used in Drupal 7. To achieve your job I suggest this code (without any manual query). // load the node object $node = node_load($nid); // set status property to 1 $node->status = 1; // re-save the node node_save($node); If you have more node ID, and you want to publish all those nodes, you can use the ...


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('...


20

You can do it by adding an expression to the query: db_update('a_table') ->expression('count', 'count + 1') ->condition('field1', $some_value) ->execute();


19

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); } ...


19

In my experience, I purge all of the "cache_*" tables. plus "watchdog" if I don't care about past Drupal logs plus "accesslog" if I don't care about logged-in users plus "search" if I don't care about indexed nodes contents


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

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

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) { ...


18

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


18

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


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