Skip to content

Instantly share code, notes, and snippets.

@gautiermichelin

gautiermichelin/objects_list_items.sql

Created August 14, 2015 05:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gautiermichelin/930492cbbbb7997204a3 to your computer and use it in GitHub Desktop.
Save gautiermichelin/930492cbbbb7997204a3 to your computer and use it in GitHub Desktop.
Download ZIP
CollectiveAccess SQL request : used list items inside object records
Raw
objects_list_items.sql
SELECT ca_list_labels.list_id,
ca_list_labels.`name`,
ca_attribute_values.item_id,
ca_list_item_labels.name_singular,
group_concat(distinct ca_attributes.row_id) as objets
FROM ca_list_labels LEFT OUTER JOIN ca_lists ON ca_list_labels.list_id = ca_lists.list_id
LEFT OUTER JOIN ca_list_items ON ca_lists.list_id = ca_list_items.list_id
LEFT OUTER JOIN ca_attribute_values ON ca_list_items.item_id = ca_attribute_values.item_id
LEFT OUTER JOIN ca_list_item_labels ON ca_list_item_labels.item_id = ca_attribute_values.item_id
LEFT OUTER JOIN ca_attributes ON ca_attribute_values.attribute_id = ca_attributes.attribute_id
WHERE table_num = 57
GROUP BY ca_attribute_values.item_id
ORDER BY list_id, name_singular ASC
@gautiermichelin
Copy link
Author

capture d ecran 2015-08-14 a 07 34 05

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment