1

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.

  • In $query->leftJoin('', 'deals', 'nid = deal_stores.entity_id'); there is no table definition. First argument should be table name. – Jayendra Kainthola Dec 18 '13 at 7:27
0

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.

| improve this answer | |
  • Dear Arpitr thanks, I tried all the steps as you said In step 2 I got the unexpected error then I tried the step 3, I got this error... "PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'whatsonsale.deals' doesn't exist:"... I added the line dpm($result) also.... but I could not understand one thing if I am executing my SQL query in database it shows the results correctly, but after converting why its showing this type of error. – Mudasir Dec 18 '13 at 7:02
0

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

| improve this answer | |
0

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.

| improve this answer | |
  • I have one issue now how can I add the corresponding anchor tags to the category links which I retrieved with this query. I tried with this but no results it prints the <a href=""> as a string.----------------------------------------- $list['#links'][] = array('title' => '<a href="' . $path . '">' . $record-> cat_name . ' ('. $record->no_of_deals . ')' ); ---------------is this the right way I am doing embedding the html within php. – Mudasir Dec 19 '13 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.