0

I have a view and I need to add a groupby at the end of the SQL query but, for some bugged module the interface do not save the 'Grouping field Nr.' option and I don't know how to achieve this view -.-

view page

SELECT DISTINCT taxonomy_term_data.tid AS tid,
  field_testata_taxonomy_term_data.product_id AS field_testata_taxonomy_term_data_product_id,
  field_magazine_commerce_product.nid AS field_magazine_commerce_product_nid,
  field_magazine_commerce_product.LANGUAGE AS field_magazine_commerce_product_language,
  taxonomy_term_data.NAME AS taxonomy_term_data_name,
  taxonomy_term_data.vid AS taxonomy_term_data_vid,
  taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name,
  'taxonomy_term' AS field_data_field_mondi_azzurra_taxonomy_term_entity_type,
  'taxonomy_term' AS field_data_field_avatar_taxonomy_term_entity_type,
  'taxonomy_term' AS field_data_field_logo_slider_taxonomy_term_entity_type,
  'taxonomy_term' AS field_data_field_image_taxonomy_term_entity_type,
  'commerce_product' AS field_data_field_image_commerce_product_entity_type,
  'taxonomy_term' AS field_data_field_call_to_action_taxonomy_term_entity_type
FROM {taxonomy_term_data} taxonomy_term_data
  LEFT JOIN {field_data_field_testata} field_data_field_testata ON taxonomy_term_data.tid = field_data_field_testata.field_testata_tid AND (field_data_field_testata.entity_type = 'commerce_product' AND field_data_field_testata.deleted = '0')
  LEFT JOIN {commerce_product} field_testata_taxonomy_term_data ON ield_data_field_testata.entity_id = field_testata_taxonomy_term_data.product_id
  LEFT JOIN {field_data_field_magazine} field_testata_taxonomy_term_data__field_data_field_magazine ON field_testata_taxonomy_term_data.product_id = field_testata_taxonomy_term_data__field_data_field_magazine.field_magazine_product_id
  LEFT JOIN {node} field_magazine_commerce_product ON field_testata_taxonomy_term_data__field_data_field_magazine.entity_id = field_magazine_commerce_product.nid
  LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (((taxonomy_vocabulary.machine_name IN ('testate'))))

I have to pick the last node connected to the taxonomy term [connected to another taxonomy term filtered by context] and now for each term and I have all the taxonomy-node coupple.

How can I achieve this?

There is a way to add 'Groupby tid' at the end of the query? Or I have to build the query diffrently?

UPDATE: If I active 'Use aggregation' I have this query

SELECT DISTINCT field_data_field_avatar. AS field_data_field_avatar_, 
field_data_field_logo_slider. AS field_data_field_logo_slider_, field_data_field_image. AS field_data_field_image_, field_magazine_commerce_product.nid AS field_magazine_commerce_product_nid, field_magazine_commerce_product.language AS field_magazine_commerce_product_language, field_data_field_call_to_action.field_call_to_action_value AS field_data_field_call_to_action_field_call_to_action_value, taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid_1, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, COUNT(DISTINCT field_data_field_mondi_azzurra.field_mondi_azzurra_tid) AS field_data_field_mondi_azzurra_field_mondi_azzurra_tid, COUNT(DISTINCT field_data_field_mondi_azzurra.delta) AS field_data_field_mondi_azzurra_delta, COUNT(DISTINCT field_data_field_mondi_azzurra.language) AS field_data_field_mondi_azzurra_language, COUNT(DISTINCT field_data_field_mondi_azzurra.bundle) AS field_data_field_mondi_azzurra_bundle, MIN(taxonomy_term_data.tid) AS tid, 'taxonomy_term' AS field_data_field_avatar_taxonomy_term_entity_type, 'taxonomy_term' AS field_data_field_logo_slider_taxonomy_term_entity_type, 'taxonomy_term' AS field_data_field_image_taxonomy_term_entity_type, MIN(field_testata_taxonomy_term_data.product_id) AS field_testata_taxonomy_term_data_product_id, 'commerce_product' AS field_data_field_image_commerce_product_entity_type, MAX(field_testata_taxonomy_term_data__field_data_field_image.bundle) AS field_testata_taxonomy_term_data__field_data_field_image_bun, 'taxonomy_term' AS field_data_field_call_to_action_taxonomy_term_entity_type
FROM 
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {field_data_field_testata} field_data_field_testata ON taxonomy_term_data.tid = field_data_field_testata.field_testata_tid AND (field_data_field_testata.entity_type = 'commerce_product' AND field_data_field_testata.deleted = '0')
LEFT JOIN {commerce_product} field_testata_taxonomy_term_data ON field_data_field_testata.entity_id = field_testata_taxonomy_term_data.product_id
LEFT JOIN {field_data_field_magazine} field_testata_taxonomy_term_data__field_data_field_magazine ON field_testata_taxonomy_term_data.product_id = field_testata_taxonomy_term_data__field_data_field_magazine.field_magazine_product_id
LEFT JOIN {node} field_magazine_commerce_product ON field_testata_taxonomy_term_data__field_data_field_magazine.entity_id = field_magazine_commerce_product.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
LEFT JOIN {field_data_field_mondi_azzurra} field_data_field_mondi_azzurra ON taxonomy_term_data.tid = field_data_field_mondi_azzurra.entity_id AND (field_data_field_mondi_azzurra.entity_type = 'taxonomy_term' AND field_data_field_mondi_azzurra.deleted = '0')
LEFT JOIN {field_data_field_avatar} field_data_field_avatar ON taxonomy_term_data.tid = field_data_field_avatar.entity_id AND (field_data_field_avatar.entity_type = 'taxonomy_term' AND field_data_field_avatar.deleted = '0')
LEFT JOIN {field_data_field_logo_slider} field_data_field_logo_slider ON taxonomy_term_data.tid = field_data_field_logo_slider.entity_id AND (field_data_field_logo_slider.entity_type = 'taxonomy_term' AND field_data_field_logo_slider.deleted = '0')
LEFT JOIN {field_data_field_image} field_data_field_image ON taxonomy_term_data.tid = field_data_field_image.entity_id AND (field_data_field_image.entity_type = 'taxonomy_term' AND field_data_field_image.deleted = '0')
LEFT JOIN {field_data_field_image} field_testata_taxonomy_term_data__field_data_field_image ON field_testata_taxonomy_term_data.product_id = field_testata_taxonomy_term_data__field_data_field_image.entity_id AND (field_testata_taxonomy_term_data__field_data_field_image.entity_type = 'commerce_product' AND field_testata_taxonomy_term_data__field_data_field_image.deleted = '0')
LEFT JOIN {field_data_field_call_to_action} field_data_field_call_to_action ON taxonomy_term_data.tid = field_data_field_call_to_action.entity_id AND (field_data_field_call_to_action.entity_type = 'taxonomy_term' AND field_data_field_call_to_action.deleted = '0')
WHERE (( (taxonomy_vocabulary.machine_name IN  ('testate')) ))
GROUP BY field_data_field_avatar_taxonomy_term_entity_type, field_data_field_logo_slider_taxonomy_term_entity_type, field_data_field_image_taxonomy_term_entity_type, field_data_field_image_commerce_product_entity_type, field_data_field_call_to_action_taxonomy_term_entity_type

and this SQL error

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 'AS field_data_field_avatar_, field_data_field_logo_slider. AS field_data_field_l' at line 1

As you can see this query is not correctly written by drupal: there are some dots after the fields name in the frist part of the query..

Do you think I can fix this Views's core error?

  • If you suspect the module is buggy, you should file a bug report, which sort of makes the question off-topic. In the mean time though, you could hook_views_alter the query. – Letharion Oct 2 '13 at 11:28
  • I tryed to build an hook_views_alter() but it do not seem to work. I use add_groupby($clause) but the result just work before the groupby add.. – Shyghar Oct 4 '13 at 9:02
3

You need to enable aggregation under Advanced > Use aggregation. You can then select how you want your data grouped using the settings under Fields, Sort criteria and Filter criteria.

Views screen grab of field aggregation settings

  • If I try to use aggregation the query return me an SQL error: 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 'AS field_data_field_avatar_, field_data_field_logo_slider. AS field_data_field_l' at line 1. I tryed that. – Shyghar Oct 2 '13 at 16:40
  • Try turning on the SQL view and debugging it using SQL directly for comparision. You should be able to detect the cause of the error. Possibly a missing field or a grouping in the wrong place. Visit Views settings example.com/admin/structure/views/settings and check the Show the SQL query checkbox under Live Preview settings – Queenvictoria Oct 3 '13 at 0:33
  • I have SQL debbugging already active but i didn't read the query with the aggregation mode on.. question updated – Shyghar Oct 3 '13 at 8:12
  • I think you need to try getting the query working how you want it in SQL first then try any track down what you have done incorrectly in Views that causes a bad query. I've noticed a couple of times that unless you have specified a Sort criteria the query will always fail. Try sorting on term ID or something. If that stops the query from failing then you can continue to debug. – Queenvictoria Oct 3 '13 at 12:53

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.