0

I currently have a query that compiles to

SELECT n.nid AS nid, field_data_field_date.field_date_value AS incident_date, field_data_field_tweeted_date.field_tweeted_date_value AS tweeted_date, n.nid AS n_nid, (SELECT source.field_source_value AS field_source_value
FROM 
field_data_field_sources sources
left JOIN field_data_field_source source ON source.entity_id = field_sources_value
WHERE  (sources.entity_id = n.nid) 
ORDER BY sources.delta DESC
LIMIT 1 OFFSET 0) AS most_recent_source
FROM 
node n
left JOIN field_data_field_incident_type incidenttype ON incidenttype.entity_id = n.nid
left JOIN field_data_field_date field_data_field_date ON field_data_field_date.entity_id = n.nid
left JOIN field_data_field_tweeted_date field_data_field_tweeted_date ON field_data_field_tweeted_date.entity_id = n.nid
WHERE  (n.type = 'incident') AND (n.status = 1) AND( (incidenttype.field_incident_type_tid IN  (9,25)) )
GROUP BY n.nid
ORDER BY field_data_field_date.field_date_value DESC

I want to add

HAVING COUNT(DISTINCT incidenttype.field_incident_type_tid) = 2

And I can get it to work in the query when I manually create it in my SQL gui, but I can't seem to add it to the query in drupal properly.

I added

$query->addExpression('HAVING COUNT(DISTINCT incidenttype.field_incident_type_tid) = 2');

But that didn't add it how I wanted to the query. The query compiled to

SELECT n.nid AS nid, HAVING COUNT(DISTINCT incidenttype.field_incident_type_tid) = 2 
AS expression FROM {node} n left JOIN {field_data_field_incident_type} incidenttype ON incidenttype.entity_id = n.nid 
left JOIN {field_data_field_date} field_data_field_date ON field_data_field_date.entity_id = n.nid 
WHERE (n.type = :db_condition_placeholder_0) AND (n.status = :db_condition_placeholder_1) AND (n.created < :db_condition_placeholder_2) AND( (incidenttype.field_incident_type_tid 
IN (:db_condition_placeholder_3, :db_condition_placeholder_4)) ) 
GROUP BY n.nid ORDER BY field_data_field_date.field_date_value 

I'd like to have it between GROUP BY and ORDER BY since that seems to be what worked in the query I manually wrote. Also it added 'AS expression' to the end of it. Is there a way I can specifically add 'HAVING COUNT(DISTINCT incidenttype.field_incident_type_tid) = 2'? Or should I be going about it a different way?

1 Answer 1

0

Instead of putting everything in one expression. I was able to achieve what I needed by creating an alias for the COUNT like so

$query->addExpression('COUNT(DISTINCT incidenttype.field_incident_type_tid)','typeCount');

and then add a having Condition afterwards using the alias I created like so.

$query->addExpression('COUNT(DISTINCT incidenttype.field_incident_type_tid)','typeCount');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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