Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.I've just started using Forena Reports and have so far managed to generate a report from querying an external database. I'd like the report to generate information specific only to the authenticated user. However, the Drupal user name does not match the user name in the external database. What does match is the email address. I can query the external database to produce the required information where mail=user@somewhere.com. Can I parameterize the Forena SQL query so that the currently authenticated user's email address, as stored in Drupal, is passed to the query?
Many thanks!
| Comment | File | Size | Author |
|---|---|---|---|
| #3 | 2620900-3-current-user-email.patch | 879 bytes | metzlerd |
Comments
Comment #2
metzlerd CreditAttribution: metzlerd commentedThe short answer is that forena cannot currently do this directly without some custom code. If you have a bit of time, it won't take me long to roll a patch that will accomplish this. For some reason I didn't think of this important use case. Forena has a pluggable system for current user name generation, but it's a bit tricky to configure.
In the meantime you could theoretically nest calls to your data block inside another data block. But that is something you'd want some experience in manually creating .frx files. Is that something you are comfortable with yet?
Comment #3
metzlerd CreditAttribution: metzlerd commentedWell that patch was easier than I thought. it hasn't been tested, but if you'd be willing to test it, that would be most helpful.
Comment #4
ishore CreditAttribution: ishore commentedWow! Thanks very much. I'm still very new to Forena, and pretty new to Drupal, but I'll certainly give the patch a try and get back to you.
Comment #5
ishore CreditAttribution: ishore commentedI have applied the patch. Apologies for the ignorance, but I can't find the answer in the documentation. How do I reference the authenticated user's email address within the SQL? I'm imagining something like:
SELECT id,title from monthly_stats WHERE mail = :current_user{mail}
Comment #6
metzlerd CreditAttribution: metzlerd commentedNo, there is an options in your data source configuration (where you specify database connection string, etc.) that determines what value the :current_user parameter gets for data blocks within that data source. You should be able to change it to use email there. You will still use the :current_user paraameter in your data blocks.
Comment #7
ishore CreditAttribution: ishore commentedWorks beautifully, thanks! I do have another problem in that the specified sort order is not obeyed, which may be related to ticket 2610950. I do have DataTables installed, although I haven't tried to use it yet. But it (DataTables) is producing an error message right now when I click on the Forena Data Block that I have created. I will try and fix that now. Thanks for the patch!
Comment #9
metzlerd CreditAttribution: metzlerd commentedGlad it worked. Committed.