Execute Sql Query in drupal

Question

I have SQL Query and I want to execute it in Drupal. My SQL Query is like this.

select cat.tid as cat_tid, cat.name as cat_name, cat_parent.parent as cat_parent_tid, 
count(deals.nid) as no_of_deals
from 
taxonomy_term_data as cat

left outer join taxonomy_term_hierarchy as cat_parent on cat_parent.tid=cat.tid

left outer join field_revision_field_categories as store_categories on 
store_categories.field_categories_tid=cat.tid

left outer join field_revision_field_store as deal_stores on field_store_nid=store_categories.entity_id

left outer join (select * from node where type='deal' and status=1) as deals on nid=deal_stores.entity_id

where cat.vid=5 

group by cat.tid, cat.name, cat_parent.parent

I converted this query with the help of this module https://drupal.org/project/query_coder

and got the resulted query as

$query = db_select('taxonomy_term_data', 'cat');

$query->addExpression('count(deals.nid)', 'no_of_deals');

$query->fields('cat', array('tid', 'name'));

$query->fields('cat_parent', array('parent'));

$query->leftJoin('taxonomy_term_hierarchy', 'cat_parent', 'cat_parent.tid = cat.tid');

$query->leftJoin('field_revision_field_categories', 'store_categories', 'store_categories.field_categories_tid = cat.tid');

$query->leftJoin('field_revision_field_store', 'deal_stores', 'field_store_nid = store_categories.entity_id');

$query->leftJoin('', 'deals', 'nid = deal_stores.entity_id');

$query->condition('cat.vid', 5);

$query->groupBy('cat.tid');

$query->groupBy('cat.name');

$query->groupBy('cat_parent.parent');

$result = $query->execute();

Now how can I execute this query in drupal, I tried to import this query in views 3 but got some unexpected error, Also I tried to add this query into the block but again getting the same error. I also created a custom block in a custom module that too did not worked. Please help me how can I execute this query. Answer will be greatly appreciated.

Answer 1

You should better start debugging the query.Things that might be help you debugging the query.

1. Install devel module. Having logged in as admin visit "yoursitename.com/devel/php" here you can execute any php code.

2. Put your query there and add this dpm($result); single line of code in the last of query. Execute the query, see what you get as result.

3. If the query execution is throwing error visit "yoursitename/admin/reports/dblog" and try to figure out the reason for the error.

You can also cross check if the query returned with drupal wrapper functions is same as your original query by stringyfying the query object, to do that add

`dpm((string) $query);`

just before $result = $query->execute(); and also remember to comment $result = $query->execute(); as execution of query may cause error in your case.

Answer 2

I think you have to iterate loop the data which is coming from this query "select * from node where type='deal' and status=1".

Inside the loop you need to pass the data to the above db_select query you got. That will fix your problem I hope!!!!

Answer 3

I executed the successful query in my module, I am returning the result as a block by implementing hook_block_info().

Please check the module code below t('category block'),

);

}

function custom_category_block_view($delta='') {

$block = array(); switch ($delta) { case 'my-links': $nodes = db_query("select cat.tid as cat_tid, cat.name as cat_name, cat_parent.parent as cat_parent_tid, count(deals.nid) as no_of_deals from taxonomy_term_data as cat left outer join taxonomy_term_hierarchy as cat_parent on cat_parent.tid=cat.tid left outer join field_revision_field_categories as store_categories on store_categories.field_categories_tid=cat.tid left outer join field_revision_field_store as deal_stores on field_store_nid=store_categories.entity_id left outer join (select * from node where type='deal' and status=1) as deals on nid=deal_stores.entity_id where cat.vid=5 group by cat.tid, cat.name, cat_parent.parent");

$list = array(
    '#theme' => 'links',
    '#links' => array(),
    '#prefix' => '<div class="my-links"><h2>Related Links</h2>',
    '#suffix' => '</div>',
  );

foreach ($nodes as $record) {

$list['#links'][] = array('title' => $record-> cat_name .' ('. $record->no_of_deals . ')' );

  }

$block['subject'] = t('category block'); $block['content'] = $list; break; } return $block; }

Just Create a custom module and add this to your module file and you are done.