By saitanay on

SubQuery in db_query gives "The website encountered an unexpected error. Please try again later."

 $result = db_query(' select n.nid from {node} n where 
                           n.nid in 
                                   ( select sl.entity_id from {field_data_field_article_location} sl  where 
                                      sl.entity_type = :etype
                                      and sl.field_article_location_tid != :us_tid  
                                      
                                    )
                          and n.nid in
                                    ( select st.entity_id from {field_data_field_article_sub_terms} st where 
                                    st.field_article_sub_terms_tid in (16106,16340,16230,16154,16230,16460))
                          and n.created >= :from
                          and n.created <= :to
                          order by n.created desc', array(
        ':from' => $from_date_ts,
        ':to' => $to_date_ts,
        ':nodetype' => 'my_article',
        ':etype' => 'node',
        ':us_tid' => US_TID, 
          )
      );

do you see anything wrong with the code?

⋅ Categories: Drupal 7.x

Comments

da_solver’s picture

da_solver commented

Hi,
Here is the approach I use when dealing with database issues.

I first verify that I can run the query directly in the database engine. That means you need to select a test case (I..E. test data for input and an expected result).

Next, once I've established the query runs in the database and returns expected results, I then start diagnosing the integration issues. In this case, integration meaning Drupal's database methods (e.g. db_query). Also, verifying my application code is using valid data in the local method.

So for example, if you are using MySQL as your database engine, run the statement with MySQL command line. You'll need to replace the place holders (E.G. :from) with actual data.

I would start by verifying the SQL.

Hope that helps.

saitanay’s picture

saitanay commented

The query runs fine when executed from shell
The time stamp variables are perfect as well.

doesnt db_query support subqueries WHEN THERE ARE PLACEOLDERS IN THE SUBQUERY ?

axiom3279’s picture

axiom3279 commented

I am having the same problem on an old d6 site. Did you ever figure this issue out?