What would be the right way to pass an user field into SQL query for report?

Comments

metzlerd’s picture

metzlerd commented

If (as I assume you are) you are building your own reports, the sql statement needs to be of the form.

select * from table where
column = :made_up_parameter_name

Here's a real sample from some of the canned reports that are delivered with forena.

--ACCESS=administer permissions
SELECT * FROM {permission} p
WHERE p.rid = :role

You might consider looking at the files in repos/drupal, repos/sample and repos/reports that come with forena. I'm going try and do a documentation push this weekend.

janeks’s picture

janeks commented

Thanks for the answer.
I thought a bit different way:
F.ex. the user could have a field, that is used for where clause, so that user sees only the part of records.
I thought, that I need to get them it settings.php and then I can use a construct like of drupa user object:
$user_data->field_def_forum_subscribe...

Also in the similar way it would be good to extract and pass to external database access parameters.

The main thing, that I am slowed down is that I am not yet sure about logical sequence of steps to create a report for external database. But of course I'll will hack it soon ;-)

janeks’s picture

janeks commented

Ah, I found them (docs) in reports list.

janeks’s picture

janeks commented
Title: How to pass an user field into SQL query? » How to pass an user field value into SQL query?
Version: 7.x-1.1 » 7.x-2.x-dev
Category: support » feature

O'k I found how to get some Forena variables in data blocks SQL.
BTW: It would be good to have a list of them in docs.

But the idea for me was to achieve is:
-> user has a custom field editable/viewable just by an administrator, f.ex. user_district
-> in database there is a field f.ex. district, and I want that user can see just data that is according to district = user_district
-> so Forena reports could dig for that value f.ex. settings.php and set to a replacement variable for sql data block

metzlerd’s picture

metzlerd commented

Forena only sets one parameter currently for all reports and that is :current_user. In the default configuration, :current_user returns the drupal user id of the user, but when defining a repository's settings.php or in the settings.php file using the $_forena_repositories global you can define the "user callback" attribute of a repository which allows to define what :current_user returns for a data source/ repository.

Since forena currently uses $_GET variables to pass parameters, you could always alter the superglobal $_GET to hard code the users district. This could be done in hook_boot or hook_init for a module to make it independent of forena.

janeks’s picture

janeks commented

Since forena currently uses $_GET variables to pass parameters, you could always alter the superglobal $_GET to hard code the users district. This could be done in hook_boot or hook_init for a module to make it independent of forena.

This is not good approach for this case - I would not want to expose to $_GET that variable. I want to keep it hidden.

metzlerd’s picture

metzlerd commented

Modifying the $_GET variable via PHP prior to the report rendering does not expose the variable to the user, nor does it put it on the url, nor log it in the apache logs. It in fact doesn't expose the variable any more than creating a new variable in settings.php which is also a global.

That being said, I'm thinking the best and most flexible way to solve this is to leverage preloaded data contexts for a report. I'm also considering adding a hook. hook_parameters_alter that would be called just before the report. I'll keep this issue open until I solve that, but modifying a $_GET variable or defining queries in terms of :current_user is still a viable work around.

Fore example, assuming the users district were stored in a table in the database the data block could simply be written:

select dd.* from district_data_table dd JOIN user_districts ud ON ud.district_id=dd.district_id WHERE
ud.uid = :current_user

this is how I currently write all my "current user specific" data blocks.

hpang’s picture

hpang commented

Now that 2.0 is out, can you explain briefly how to implement hook_forena_parameters_alter? A quick demo would be greatly appreciated!

janeks’s picture

janeks commented

Read a beginner "how to" for writing modules.
In module put this function (modify for your needs accordingly). 

function ff_connector_forena_parameters_alter($report_name, &$parms) {
	global $user; //used to retrieve/link current user object
	global $language; //for language dependent reports if in DB has different languages
	$ff_user = user_load($user->uid); //used to retrieve current user full object
	$user_field_arr = field_get_items('user', $ff_user, 'field_user_db_params); //getting custom fields from user 
	$replace_val_arr = array();
	foreach ($user_field_arr as $a_item) {
		$replace_val_arr[] = $a_item['safe_value'];
	}
	$parms['user_vals']=$replace_val_arr; //this is what will be replaced in SQL data blocks
	$parms['lang']=strtoupper($language->prefix); //this is what will be replaced in SQL data blocks
}

In your data blocks specify (commonly) in where part:

...
where
     aTable.limitField = in (:user_vals)

It could be also in simple form aField = :aValue, if you know that you will have just one value.
Also you can use atable.limitField = COALESCE(:user_vals, atable.limitField), but then in case when you will have no values (null) you will get all the records (unfiltered).

metzlerd’s picture

metzlerd commented

I will definitely work on this during the weekend. Janeks has posted a good example. Basically what he's doing is altering to make sure all reports get a parameter of a particular value and then referencing those values in the data bloxk. The last two lines are the meat of the altering syntax. Note that the report name is included so that you can make that logic happen only for a limited set of reports.

There are other more advanced approaches that leverage forena's concept of data contexts which I'll cover in a screen cast on this topic.

metzlerd’s picture

metzlerd commented
Version: 7.x-2.x-dev » 7.x-3.x-dev
Assigned: Unassigned » metzlerd
Status: Active » Fixed

In 7.x-3.x this function behaves as desired. Arrays are space separated in displays by default.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

ausfragen’s picture

ausfragen commented

How did you guys solve it? I am Newbie at this. The default :current_user is NULL in my case.

metzlerd’s picture

metzlerd commented

Which version are you using? :current_user really shouldn't be null in any of the current versions, although I recently fixed a bug in the forena_query module that had this not quite working the the new query builder tool.

ausfragen’s picture

ausfragen commented

drupal 7.21. I might be doing something wrong. Is there a link for documentation for this module.
Also i am encountering "Notice: Array to string conversion in FrxReportGenerator->report() " errors. How do i get rid of these?
Thank You.

metzlerd’s picture

metzlerd commented

Actually I was wondering which Forena version you were using.... if you're using the 7.x-3.x branch, could you switch to the latest alpha release and see if this solves your problem?

Ajascosoft’s picture

Ajascosoft commented
Issue summary: View changes

Thank you very much Metzlerd. I have a CCK field on user profile i would like to use as the default parameter when is use :current_user.
I am primarily querying an external data source and so would like to pass that value as filter on almost every query.
So what i am doing is, i add this unique field (from external data source) as a field on user account. So when the user logs in, he can fetch reports from the external data source with that field as the filter.
This is kind of urgent and your assistance is highly needed.

Thank you

janeks’s picture

janeks commented

Your approach sounds right. I am unsure about details from your message.
Study example: https://www.drupal.org/node/1436552#comment-5949286 - it should tell what is needed.

Basicaly you need:
-> custom user field(s), accessible just for user admin,
-> module that reads that field(s) and adds them in $parms[] array (I am using this module also for finer access control, by using custom permissions)
-> WHERE clause in your SQL/datablock

What is not in example above, that for almost every parameter I am rather using reserved IF comments in sql template (data block):

--IF=:bound_region
	AND
	DC.REG in :bound_region
--END
metzlerd’s picture

metzlerd commented

Janeks is correct... best solution is a custom module. There is a hook (hook_forena_parameters_alter) that I use to load user data or other derived data into a custom context that I can then use in all of my SQL queries. See the docs for more information on this hook (http://forenasolutions.org/reports/help.development#hooks).