4

I would like a link in each of my organic groups where Forena would replicate some of my views and create a pdf document with a page per OG user.

I've looked through the documentation and module but my SQL knowledge is quite primitive and I don't see anything related to using the current NID in queries.

I think the Views Show Query module would allow me to copy the relevant queries into Forena while I learn the SQL.

So my question is, does the above sound possible and would copying the SQL queries that views produces be enough to get me started?

Below is a view using field collections and a screenshot of the views_show_query pasted into Heidi. There are two arguments given to the view. "1422" and "1243".

SELECT field_sts_grades_fc_field_collection_item.nid AS field_sts_grades_fc_field_collection_item_nid, field_collection_item.item_id AS item_id, field_sts_grades_fc_field_collection_item.title AS field_sts_grades_fc_field_collection_item_title, field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference.field_exam_class_reference_target_id AS field_sts_grades_fc_field_collection_item__field_data_field_, field_sts_grades_fc_field_collection_item__field_data_field_grade_date.field_grade_date_value AS field_sts_grades_fc_field_collection_item__field_data_field__1, field_sts_grades_fc_field_collection_item.created AS field_sts_grades_fc_field_collection_item_created, users_field_data_field_sts_grades_ref__realname.realname AS users_field_data_field_sts_grades_ref__realname_realname, 'node' AS field_data_field_grade_date_node_entity_type, 'field_collection_item' AS field_data_field_sts_grades_ref_field_collection_item_entity, 'field_collection_item' AS field_data_field_grade_decimal_field_collection_item_entity_, 'node' AS field_data_field_exam_class_reference_node_entity_type
FROM 
field_collection_item field_collection_item
LEFT JOIN field_data_field_sts_grades_fc field_data_field_sts_grades_fc ON field_collection_item.item_id = field_data_field_sts_grades_fc.field_sts_grades_fc_value
LEFT JOIN node field_sts_grades_fc_field_collection_item ON field_data_field_sts_grades_fc.entity_id = field_sts_grades_fc_field_collection_item.nid
LEFT JOIN field_data_field_admin_students_class_ref field_sts_grades_fc_field_collection_item__field_data_field_admin_students_class_ref ON field_sts_grades_fc_field_collection_item.nid = field_sts_grades_fc_field_collection_item__field_data_field_admin_students_class_ref.field_admin_students_class_ref_target_id AND (field_sts_grades_fc_field_collection_item__field_data_field_admin_students_class_ref.entity_type = 'entityform' AND field_sts_grades_fc_field_collection_item__field_data_field_admin_students_class_ref.deleted = '0')
LEFT JOIN entityform field_admin_students_class_ref_node ON field_sts_grades_fc_field_collection_item__field_data_field_admin_students_class_ref.entity_id = field_admin_students_class_ref_node.entityform_id
LEFT JOIN field_data_field_sts_grades_ref field_data_field_sts_grades_ref ON field_collection_item.item_id = field_data_field_sts_grades_ref.entity_id AND (field_data_field_sts_grades_ref.entity_type = 'field_collection_item' AND field_data_field_sts_grades_ref.deleted = '0')
LEFT JOIN users users_field_data_field_sts_grades_ref ON field_data_field_sts_grades_ref.field_sts_grades_ref_target_id = users_field_data_field_sts_grades_ref.uid
LEFT JOIN field_data_field_grades_exam_set field_sts_grades_fc_field_collection_item__field_data_field_grades_exam_set ON field_sts_grades_fc_field_collection_item.nid = field_sts_grades_fc_field_collection_item__field_data_field_grades_exam_set.entity_id AND (field_sts_grades_fc_field_collection_item__field_data_field_grades_exam_set.entity_type = 'node' AND field_sts_grades_fc_field_collection_item__field_data_field_grades_exam_set.deleted = '0')
LEFT JOIN field_data_field_exam_class_reference field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference ON field_sts_grades_fc_field_collection_item.nid = field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference.entity_id AND (field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference.entity_type = 'node' AND field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference.deleted = '0')
LEFT JOIN field_data_field_grade_date field_sts_grades_fc_field_collection_item__field_data_field_grade_date ON field_sts_grades_fc_field_collection_item.nid = field_sts_grades_fc_field_collection_item__field_data_field_grade_date.entity_id AND (field_sts_grades_fc_field_collection_item__field_data_field_grade_date.entity_type = 'node' AND field_sts_grades_fc_field_collection_item__field_data_field_grade_date.deleted = '0')
LEFT JOIN realname users_field_data_field_sts_grades_ref__realname ON users_field_data_field_sts_grades_ref.uid = users_field_data_field_sts_grades_ref__realname.uid
WHERE (( (field_sts_grades_fc_field_collection_item__field_data_field_grades_exam_set.field_grades_exam_set_target_id = '1422') AND (field_sts_grades_fc_field_collection_item__field_data_field_exam_class_reference.field_exam_class_reference_target_id = '1243') ))
ORDER BY field_sts_grades_fc_field_collection_item__field_data_field_ ASC, field_sts_grades_fc_field_collection_item__field_data_field__1 ASC, field_sts_grades_fc_field_collection_item_created ASC, users_field_data_field_sts_grades_ref__realname_realname ASC, field_data_field_sts_grades_ref.field_sts_grades_ref_target_id ASC

enter image description here

Views Screenshot

2

IMO the approach to use the Views Show Query module as you wrote is definitely a valid approach to get you started. Even though I've not (yet) used this module to produce such queries, this part from the module's project page may need an extra hint about how to further "massage" your query (to further improve it):

... the module adds 'User ID to show final query' field in views preview form. You must enter numeric user ID in this field to view the real SQL query.

More specifically, I would try to transform that "user id" in that query in a "query parameter" to be used by Forena, similar to what you can already see in this SQL query that comes with the Forena samples (note the (:state) in this sample query):

select * from user_distribution WHERE 
  state in (:state)
  order by city, state

In your own site where you have Forena installed, you can find this query at relative path admin/structure/forena/data/sampledb.distribution/edit.

And to see the power of this query with Forena parameters, go have a look at the Users in cities in state Illinois sample, which in your own site you can find via relative path /reports/sample.user_distribution_simple?state=IL.

The interesting/relevant part here is the ?state=IL at the end of it: change it to any other (valid) state like CA, FL, etc, and see what happens (answer: similar report, for the state you asked for).

With that, I think your question (challenge) is reduced to finding a way to find a way to use these kinds of relative paths (with the query strings to indicate report parameter values, and use those parameters in your query parameters also.

Ps: If you refine your question with more specific paths, a samples SQL, the parms you have in mind etc, I might be able to further refiine my answer about also.

  • 1
    I actually looked at those samples and saw the ?state= but it didn't click to use that method to input my parameters.. Makes a lot more sense now! I will hopefully look at this later today and tomorrow and report back. Thanks. – Niall Murphy Mar 26 '16 at 8:57
  • 1
    OK, great, so it looks like my answer somehow helped already. I look forward to your extra feedback later on! I'd still be interested to see your actual SQL you are starting from (via that Views Show Query), by adding it as an update (edit) to the bottom of your question. – Pierre.Vriens Mar 26 '16 at 9:29
  • 1
    When you see the screenshot, you'll notice the text "field_collection_item" instead of numbers. I'm just wondering if that creates a limitation. Edit: You're right, you have answered the question asked so if this is an issue, I'll create a new thread in the future. I just wanted to give an example query. – Niall Murphy Mar 26 '16 at 10:17
  • 1
    Wow, that is an impressive query. I'll need some time to further digest it (I'll add an extra update if appropriate later on). About the "... There are two arguments given to the view. "1422" and "1243".": it seems those 2 args should become report parameters (at least). PS: would you mind marking this answer as accepted also (as per your "... you have answered the question ..."? – Pierre.Vriens Mar 26 '16 at 10:35
  • 2
    I think a lot of people would be delighted with a sample OG Forena report for either users or nodes. Good luck with it! – Niall Murphy Mar 26 '16 at 10:57

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.