By the way, this does work with views. So my suggested syntax fix above is correct.
Example:
Create a view mdl_user_fullname with two columns, the user id as id and a concat of the first and last names into a single column as fullname.
Then:
SELECT u.username, fn.fullname
FROM prefix_user_fullname fn
JOIN prefix_user u ON u.id = fn.id
%%FILTER_SEARCHTEXT:fn.fullname:~%%
works as expected. (Assuming your view has the same prefix_ as your standard tables.)
Hello all
I use the configurable report block to make querys also in external database.
Exemplo :
database name: sgeead2
table name: escolas
field to search : razaosocial
ex query:
SELECT * from sgeead2.`escolas`
WHERE 1=1
%%FILTER_SEARCHTEXT:razaosocial:~%%
obs: is ver important : WHERE 1=1 in this case because code have : (moodle-block_configurablereports/components/filters/searchtext/plugin.class.php )
if ($operator == '~') {
$replace = " AND ".$field." LIKE '%".$filter_searchtext."%'";
}
id you dont put : WHERE 1=1 Will acknowledge an error
SELECT * from sgeead2.`escolas` AND razaosocial LIKE'%filter%';
and corret is:
SELECT * from sgeead2.`escolas` WHERE 1=1 AND razaosocial LIKE'%filter%';
Gisele,
This is a very useful tip.
So, your Moodle database user also has the rights to use the other database as well. Very nice.
Randy