Questions related to SQL queries, used in a module to retrieve information in a database table.
20
votes
4answers
13k views
“OR” condition in db_select in drupal 7
I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query->condition(), but by ...
18
votes
4answers
7k views
Custom query in Views?
At some point I found the need to modify an SQL query generated by Views, in the end I overrode views_pre_execute and changed the query for that specific view.
This feels like an ugly hack to me and ...
14
votes
2answers
11k views
Print the query which is built using db_select()
I want to print the query which is built using db_select() in a programmatical way. Is there any API function provided by Drupal Abstraction Layer?
It is similar to query output in Views, but I want ...
12
votes
4answers
7k views
How do you make sortable tables with a pager with data from a custom table?
For Drupal 6 you could do something like this:
$header = array(
array('data' => t('Order id'), 'field' => 'order_id'),
...
array('data' => t('Transaction time'), 'field' => ...
9
votes
6answers
4k views
Excluding empty(Null) fields when using EntityFieldQuery query condition
Is it possible to select all of the entities who's xyz field is empty?
I tried soemthing like this:
->fieldCondition('field_name', 'value', NULL, 'IS NOT NULL');
However, this doesn't seem to ...
9
votes
5answers
5k views
Using OR with EntityFieldQuery
I have never had the need to do this before today, but it doesn't seem like you can make OR queries with EntityFieldQuery, since db_or is used for select queries.
An example would get to all entities ...
9
votes
3answers
5k views
How to define and use an external database connection in a custom module
I'm developing a module that will rely heavily on external database queries. Is there a best practice for defining and using an external database connection throughout a module?
This page tells me ...
9
votes
2answers
279 views
What is the most sustainable way to obtain aggregate information on fields?
EntityFieldQuery is not intended to be used to run aggregate functions (SUM, AVG, etc.) on field data because it is SQL-agnostic. That said, such operations legitimately need to be run from time to ...
7
votes
3answers
5k views
What does vid mean
I have designed a view and I see this query in the preview section:
SELECT node.nid AS nid,
node_data_field_crm_history_brokerid.field_crm_history_brokerid_value AS ...
7
votes
1answer
5k views
Trying to get COUNT(*) from custom content type with EntityFieldQuery
How can I retrieve the 'count(*)' from a query in drupal 7? The query needs to include a custom content type and a custom field.
Notes
Custom Content Type: employees
Custom Field Name: ...
7
votes
2answers
3k views
Using LIKE in db_query in D7
I have a query like this in Drupal 6:
$sql = 'SELECT sid, score FROM search_index WHERE word LIKE "%%%s%%"';
$result = db_query($sql,$search_term);
And it worked fine, but now I'm upgrading to ...
7
votes
1answer
1k views
Debugging custom database queries
Is there a convenient way to ask a particular database object to print its query?
Every now and then, I write a DBTNG db_op() in a module, and far to often, I don't quite get the queries right.
I ...
7
votes
3answers
972 views
Drupal as (powerful) relational database
I’d like to make a Drupalsite, providing a platform for people around the world who are working on the same research topic to collaborate and communicate. (I’m sure Drupal is therefore an excellent ...
7
votes
2answers
2k views
Views3 and subqueries?
I've got a view which generates a query that does multiple joins. This produces a cartesian join, and I need to "convert" the joins to subqueries.
I've looked through documentation, Google search ...
6
votes
2answers
5k views
Get the actual query executed by a view
I am using Drupal 7, and I need to find out the SQL query that is executed by a certain query. Views module can show the SQL when configuring the view but apparently the query is not the actual query ...
6
votes
2answers
5k views
how to extract values of field collection using Entityfieldquery?
I have created a content type 'customer' in which I have a field collection named 'field_group'. The field collection has 2 fields namely:
Title
Name
The Customer content ...
6
votes
1answer
1k views
How do I get computed column using db_select?
I require sum(points)? Any help on what to add in the sample code given below?
$query = db_select('digitaliq_question_points','n')
->fields('n',array('points'))
->condition("node_id", ...
6
votes
1answer
2k views
Difference between field_data and field_revision tables?
I have a lot of custom functions; I just want to make sure I'm doing this right.
If I want the most recent version—the live version—of a node, I go to the field_data_field_xyz table, right?
My ...
5
votes
3answers
3k views
How to check if a database query has results?
How do I check if the following code has results?
I tried the empty function but it always returns FALSE
$query = db_select('my_table', 't');
$query->condition('code', 10)
->fields('t', ...
5
votes
1answer
7k views
How to write a Drupal 7 query with “count” and “having” properties?
I am unable to make the following query work in drupal7 standards..Can anyone help me with this? Its little urgent...
SELECT n.nid AS nid, n.title AS title, count(n.title) AS ncount
FROM node n
...
5
votes
4answers
7k views
“WHERE NOT IN ( SELECT… )” with SelectQuery?
Is it possible to add a WHERE NOT IN (SELECT...) clause to a SelectQuery object? I was looking at SelectQuery::condition(), and it doesn't seem to allow for this syntax:
public ...
5
votes
1answer
618 views
How to set new AUTO_INCREMENT value in D7
I have the following statement in my module code:
db_query("ALTER TABLE {tripletex_invoice} AUTO_INCREMENT = :number", array(':number' => $start_value));
During Simpletes (D7) i get the ...
5
votes
2answers
7k views
General error: 2006 MySQL server has gone away
This is the error I am suddenly getting.
Additional uncaught exception thrown while handling exception.
Original
PDOException: SQLSTATE[70100]: Unknown error: 1317 Query execution was ...
5
votes
2answers
907 views
How do I add an OR expression between two ->addExpression() in Drupal 7
I am trying to return flagged content that is either flagged as a favorite or flagged as visited. I can get them to return but i believe Drupal is doing an and between the 2 addExpression() in the ...
5
votes
1answer
143 views
How to know which module is responsible for each database query?
I enable the devel module and set it to Display query log.
Then on every page load I get a list of the generated sql queries. You can see an example of that list in the capture I uploaded. So far so ...
4
votes
3answers
4k views
Using SQL functions in conditionals in Drupal 7 db_select()
I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since ...
4
votes
4answers
3k views
db_query not returning anything (or empty) - drupal 7
I am trying to perform a db_query but am unable to do so.
The query I am trying to perform is very simple, but to make things even simpler, the following query does not even work for me:
$result = ...
4
votes
2answers
2k views
Is it possible to join dynamic tables (subqueries) using db_select()?
I've got a complex query I'm trying to convert to db_select().
SELECT c.cid, c.pid, c.thread, count.replies, SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) as torder
FROM comment c
JOIN(
SELECT ...
4
votes
2answers
3k views
How do you build a query to get a series of nodes and their field values filtered by field values?
Hopefully someone with Drupal 7 experience can point me in the right direction.
I'm creating a custom module to query some nodes and group and sort them by their field values.
I tried to use Views ...
4
votes
2answers
1k views
What module will show me all sql queries being run on the loaded page?
My site is running slow and I suspect its a module, but not sure which one. Is there a module that can display all the sql queries being run on a loaded page? If there isn't maybe there is a command ...
4
votes
3answers
2k views
Changing an inner join to a left join in views 3.3?
Using views 3.3, I have a view in Drupal 7 where I am showing two content types. I have a filter criteria based on the value of a field that is attached only to one of the content types. So long as I ...
4
votes
3answers
3k views
Adding an OR condition to an existing query
I want to be able to search additional user fields instead of just the standard username. For instance, I would like users to be able to search an attached field with the users' Real Name. The user ...
4
votes
1answer
1k views
How to execute stored procedures in drupal?
I have a website which does lot of work on financial data. It involves operations on large volumes of data most of the time. Hence I find it useful to do those operations in mysql stored procedures. I ...
4
votes
3answers
1k views
How do I use “IGNORE” in a database query?
I would like to run a query similar to INSERT IGNORE INTO the_table (nid, language) VALUES (8, 'Chinese'), but I cannot find any documentation on how the "IGNORE" statement works with the Drupal DB ...
4
votes
1answer
2k views
Access database tables with Views
I want to show some fields of a database table with Views. There is no node type, fields, or something like that for this database table. How can I do that?
4
votes
1answer
344 views
Devel query log not showing all queries
I've enabled the Devel query log, and it's logging most queries as expected. However, I've got this module which connects to another database and executes a few queries against that one. All queries ...
4
votes
2answers
887 views
How do I optimize a Views “execute_count_query”?
So, I was able to optimize a particularly nasty query in one of my views using mymodule_views_query_alter(), bringing it from 13 seconds down to about 200 milliseconds. However, I see in my Devel ...
4
votes
2answers
341 views
SQL query the Drupal way, complex SELECT, conditional aggregate
I'm starting to invest time in learning custom Drupal entities like the ones explained here and I want to make sure that I am going to be able to do something in the future.
I am working with Drupal ...
3
votes
4answers
908 views
Will the PHP filter prevent SQL injection attacks?
I had trouble getting the form API to work though I am considering re doing all my forms with it. I have some custom html/php forms however I directly insert into the DB using pg_query. I am looking ...
3
votes
2answers
15k views
How do I write a left join query?
I want to get the value of "vid" knowing the node ID, and get the value of "name" for those rows where "vid" is equal to 2.
If it were a plain query, I would not know how to write it; I don't know ...
3
votes
2answers
1k views
Can you bulk enable comments?
I have a whole bunch of nodes (all the same type), and I'd like to turn comments on for all of them (the client changed their mind about commenting after all of the data was added).
I've found notes ...
3
votes
2answers
69 views
Multiple join query
i have a custom module and am using some queries inside, but am not getting the proper output
$query = db_select('node', 'N_en')->extend('PagerDefault');
$query->addField('N_en', 'nid', ...
3
votes
2answers
3k views
Using db_query() or db_select() to connect to multiple databases
How can I execute a query that connects to different databases?
I have the following query, and I need to rewrite it for Drupal.
SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b
...
3
votes
2answers
2k views
db_query() PDOException (no parameters were bound)
I have a relatively simple query that is executed via db_query()->execute;
For some reason this causes a PDO Error, which I assume has to do with the way drupal handles the input. Any ideas on what ...
3
votes
1answer
3k views
D7 bug with db_select, 'addExpression' and 'having' conditions?
As I asked in the drupal forum http://drupal.org/node/1393748 (no answers..)
I have an issue with a db_select query.
It seems that addExpression "GROUP_CONCAT" and "HAVING" is causing an issue.
...
3
votes
2answers
2k views
how to use EntityFieldQuery with dates
I need to select entities with specific dates.
How should the following be formatted. Do i need to parse 15-May-2010
Also how i get the error page.
function events2() {
$query = new ...
3
votes
1answer
109 views
How would I use DATEDIFF(), NOW(), and BETWEEN in a db_delete() query?
Is the something like the following possible?
db_delete('note_dates')
->condition("DATEDIFF('start', NOW()) BETWEEN 0 AND 30")
->execute();
3
votes
1answer
944 views
Use views to create a custom sql query?
I'm still not comfortable writing sql queries. As i'm pretty new to drupal module writing.
Are there any potential issues with using views to generate a sql query? In terms of security or future ...
3
votes
2answers
909 views
db_affected_rows() return “1” when the query is wrong
I have a query like UPDATE user SET creator = 'creator'". The "user" table has not the "creator" column, so db_affected_rows() shoud return −1, not 1.
Why?
Update: mysql_affected_rows() returns -1 ...
3
votes
1answer
3k views
How to clear cache using an sql query in Drupal 7
After a module update, my site has become un-usable, and only shows a php error message.
I tried circumventing the problem with drush cc, but that didn't help.
Also tried with a custom php script but ...
