I've been having problems doing the following:

  • Create a view of taxonomy terms
  • Create a relationship to nodes using that term
  • Two fields with aggregation:
    • Term name (Group by)
    • NIDs (Count) that use that term in a field (via relationship)
  • Filter the view by node posted date (via a contextual filter -- I want to use the URL as an argument)

Whenever I try to create the date filter and use an argument, I get this message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), SEC_TO_TIME(-14400)), '%Y-%m') >= '2012-08' AND DATE_FORMAT(ADDTIME(FROM_UNIX' at line 4

Here's the SQL query that views produces:

SELECT taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, COUNT(field_company_taxonomy_term_data.nid) AS field_company_taxonomy_term_data_nid
FROM 
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {field_data_field_company} field_data_field_company ON taxonomy_term_data.tid = field_data_field_company.field_company_tid AND (field_data_field_company.entity_type = 'node' AND field_data_field_company.deleted = '0')
LEFT JOIN {node} field_company_taxonomy_term_data ON field_data_field_company.entity_id = field_company_taxonomy_term_data.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(.created), SEC_TO_TIME(-14400)), '%Y-%m') >= '2012-08' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(.created), SEC_TO_TIME(-14400)), '%Y-%m') <= '2012-08') )AND(( (taxonomy_vocabulary.machine_name IN  ('company')) )))
GROUP BY taxonomy_term_data_name, taxonomy_term_data_vid, tid, taxonomy_vocabulary_machine_name
ORDER BY field_company_taxonomy_term_data_nid DESC
LIMIT 10 OFFSET 0

Not sure I understand where the issue is -- is something wrong with the query?
Any ideas? Thanks!

Comments

MustangGB’s picture

Status: Active » Closed (outdated)

Closing this as outdated, feel free to re-open with updated details if it's still a problem in the latest release.