Page 1 of 1
Modify the original DV SQL
Posted: 2019-10-07 16:29
by cesteban
Hello,
How can I modify the SQL statement of a detail view to add an additional search where condition?
I don't want to use filters because the user can remove them and I need him not to have access to other records
The option to access their own records does not help me because the user is not always who creates their records
Thank you
Re: Modify the original DV SQL
Posted: 2019-10-07 17:22
by jsetzer
Do you really mean "detail view"? Or do you want to restrict the visible records in table-view?
Maybe you can do additional auth checks in _dv function of hooks/TABLENAME.php and perhaps redirect the user conditionally.
Best
Jan
Re: Modify the original DV SQL
Posted: 2019-10-07 17:54
by cesteban
Sorry, the Table View
I need to modify the Query to add a Where condition!!!
Regards
Re: Modify the original DV SQL
Posted: 2019-10-07 18:41
by pbottcher
Hi,
you can use the TABLENAME_init function.
add:
Code: Select all
$options->QueryWhere="where YOUR CLAUSE";
Re: Modify the original DV SQL
Posted: 2019-10-07 20:02
by cesteban
Thanks
In which file I include the option?
Regards
Re: Modify the original DV SQL
Posted: 2019-10-07 20:08
by pbottcher
in the hooks/TABLENAME.php file
where TABLENAME is the table where you want to apply the where clause to the sql query.
Re: Modify the original DV SQL
Posted: 2019-10-07 20:51
by cesteban
Thaks
Re: Modify the original DV SQL
Posted: 2019-10-07 22:10
by cesteban
Sorry, It's not working
This is my test code:
function Pagos_init(&$options, $memberInfo, &$args){
$options->QueryWhere="WHERE Casa = 30";
return TRUE;
}
and I get nothing, if I run the statment:
Select * from Pagos WHERE Casa = 30
I got records
Regards
Re: Modify the original DV SQL
Posted: 2019-10-08 06:38
by pbottcher
Hi,
maybe you need to check if there is already a where statement for that table. In that case you would need
$options->QueryWhere=" AND Casa = 30";
Re: Modify the original DV SQL
Posted: 2019-10-08 17:57
by cesteban
Hi,
I already tried it but it doesn't work
Is there any way to see the SQL statement which is sent, in order to see how complete the SQL statement?
Thank you
Re: Modify the original DV SQL
Posted: 2019-10-08 20:42
by pbottcher
try to add this in the init part. you shall see the $option parameters
ob_start();
$xc=get_object_vars($options);
ksort($xc);
print_r($xc);
$c=ob_get_clean();
echo "<pre>".htmlspecialchars($c)."</pre>";
Re: Modify the original DV SQL
Posted: 2019-10-10 15:18
by cesteban
Thanks
The information I obtain before including the statement is:
[QueryWhere] =>
And after including the statement is:
[QueryWhere] => Casa = 30
But I still get the error that no records are found
The information does not show me the SQL statement to know how the request for records is being made
Best regards
Re: Modify the original DV SQL
Posted: 2019-10-10 15:23
by pbottcher
Hi ,
you need to add the "WHERE " string as well.
So after changing the QueryWhere should show:
[QueryWhere] => WHERE Casa = 30
Re: Modify the original DV SQL
Posted: 2019-10-10 15:53
by cesteban
Now I get this, but still without records
[QueryWhere] => WHERE Casa = 30
Regards
Re: Modify the original DV SQL
Posted: 2019-10-10 16:54
by pbottcher
hmmm, that is strange.
is it possible that you post all the Query fields?
Re: Modify the original DV SQL
Posted: 2019-10-10 18:05
by cesteban
Thanks, I could fix it with this:
$options->QueryWhere=" WHERE `Pagos`.`Casa` = 30";
It's working
Best regards
Re: Modify the original DV SQL
Posted: 2019-12-09 04:36
by peebee
Hi Cesteban,
Did you manage to get your WHERE clause in the [QueryWhere] => hook to work with standard Appgini filters?
I am trying to achieve a similar thing as you but I can manage to get filters to work when the WHERE clause is applied?
See here:
viewtopic.php?f=2&p=11686#p11654
Any advice/help welcome. Thanks.