Issue with pre-filter

[jfquestiaux]

I have a table with registration for classes (one class per form).

I want to pre-filter the table so only the teachers teaching the requested class can view the application.

In the registration form, the class is chosen in a table join drop down so the recorded data in the table is the id of the class.

In the registration table, I set up the pre-filter in the following manner :

WHERE name_of_the_class IN

'global $database, $my;'
'$database->setQuery("SELECT matieresformateurs FROM #__fabrik_formdata_8 WHERE username = '" . $my->id . "' ");'
'return $database->loadResult();'

(eval on 'Yes')

Applied to SuperAdministrator

In my test, I have a registration for class "11".
Since a teacher can reach several classes, I have teacher 1 with "matieresformateurs" set to "11//..*..//12//..*..//13//..*..//14//..*..//21" and teacher 2 set to "8//..*..//9//..*..//28"

So normally, only teacher 1 should see the registration.
But in fact none of them can see it.

I tried with "CONTAINS" instead of "IN" but then I get a syntax error (Warning: Invalid argument supplied for foreach() in /var/www/vhosts/www.betterliving.be/web/cogitodemo/components/com_fabrik/views/table/view.html.php on line 450)

 

[cheesegrits]

OK, first thing, you don't need to do the $database stuff. Instead, you want to use a 'subquery' (if 'subquery is not listed as a choice as well as 'eval', then you need to update Fabrik to latest SVN).

The subquery will need to be a little unwieldy, because we have to allow for the target ID being first or last, i.e. we can't just search for "LIKE //$my->id//".

I think the best approach would be to use MySQL's built in REGEX (regular expression) matching, so the filter would look something like this:

WHERE name_of_class IN

Value: SELECT id FROM class_table WHERE matieresformateurs REGEX '^{$my->id}//|//{$my->id}//|//{$my->id}$'

Eval: subquery

Substitute the primary key name for 'id', and you class table name for 'class_table'.

-- hugh

 

[cookn22]

A quick related question. I don't want to hijack the thread but it's (probably) not worth its own thread...

I've learned I'm pretty much stuck with MySQL 4.0 and so I can't do subqueries. Is it possible to do something like jfquestiaux is trying to do with an eval prefilter, where you filter a column by an array of values made by an SQL query (or something along these lines)?

I've made a table template with some PHP code that filters $this->row to get the results I want, but it's very cumbersome and hard to test for bugs...

 

[cheesegrits]

I've learned I'm pretty much stuck with MySQL 4.0 and so I can't do subqueries. Is it possible to do something like jfquestiaux is trying to do with an eval prefilter, where you filter a column by an array of values made by an SQL query (or something along these lines)?

I guess there's nothing to stop you using an eval'ed value, and run your own query in that, as long as you return the result from the PHP as a string like (1,2,3,4).

Something like ...

$database->setQuery('SELECT foo FROM bar WHERE whatever = something');
$results = $database->loadObjectList();
return '(' . explode(',',$results) . ')';

And have your filter set to test for "WHERE whatever IN". You might need some defensive coding to handle cases where your query returns no results, as I'm not use if "IN ()" is a valid query. You might also need to enclose those values in quotes, depending what data type they are.

-- hugh

 

[jfquestiaux]

Hello Hugh,

I have tested your solution, but I get a syntax error :

Erreur de syntaxe pr?s de \'REGEX \'^//|////|//$\') LIMIT 0, 10\' ? la ligne 10 SQL=SELECT jos_fabrik_formdata_15.matiere AS `jos_fabrik_formdata_9.matieresetudiants`, jos_fabrik_formdata_9.matieresetudiants AS `jos_fabrik_formdata_9.matieresetudiants___value`,
jos_fabrik_formdata_9.villecours AS `jos_fabrik_formdata_9.villecours`,
jos_fabrik_formdata_9.usernameetudiant AS `jos_fabrik_formdata_9.usernameetudiant`,
jos_fabrik_formdata_9.lastnameetudiant AS `jos_fabrik_formdata_9.lastnameetudiant`,
jos_fabrik_formdata_9.emailblocusetudiant AS `jos_fabrik_formdata_9.emailblocusetudiant`,
jos_fabrik_formdata_9.fabrik_internal_id AS `jos_fabrik_formdata_9.fabrik_internal_id`,
jos_fabrik_formdata_9.fabrik_internal_id AS __pk_val
FROM jos_fabrik_formdata_9
LEFT JOIN jos_fabrik_formdata_15 ON jos_fabrik_formdata_15.fabrik_internal_id = jos_fabrik_formdata_9.matieresetudiants
WHERE jos_fabrik_formdata_9.matieresetudiants IN (SELECT fabrik_internal_id FROM jos_fabrik_formdata_8 WHERE matieresformateurs REGEX \'^//|////|//$\') LIMIT 0, 10Erreur de syntaxe pr?s de \'REGEX \'^//|////|//$\') LIMIT 0, 10\' ? la ligne 10 SQL=SELECT jos_fabrik_formdata_15.matiere AS `jos_fabrik_formdata_9.matieresetudiants`, jos_fabrik_formdata_9.matieresetudiants AS `jos_fabrik_formdata_9.matieresetudiants___value`, \n jos_fabrik_formdata_9.villecours AS `jos_fabrik_formdata_9.villecours`, \n jos_fabrik_formdata_9.usernameetudiant AS `jos_fabrik_formdata_9.usernameetudiant`, \n jos_fabrik_formdata_9.lastnameetudiant AS `jos_fabrik_formdata_9.lastnameetudiant`, \n jos_fabrik_formdata_9.emailblocusetudiant AS `jos_fabrik_formdata_9.emailblocusetudiant`,\n jos_fabrik_formdata_9.fabrik_internal_id AS `jos_fabrik_formdata_9.fabrik_internal_id`,\n jos_fabrik_formdata_9.fabrik_internal_id AS __pk_val\n FROM jos_fabrik_formdata_9 \n LEFT JOIN jos_fabrik_formdata_15 ON jos_fabrik_formdata_15.fabrik_internal_id = jos_fabrik_formdata_9.matieresetudiants \n WHERE jos_fabrik_formdata_9.matieresetudiants IN (SELECT fabrik_internal_id FROM jos_fabrik_formdata_8 WHERE matieresformateurs REGEX \'^//|////|//$\') LIMIT 0, 10
Warning: Invalid argument supplied for foreach() in /var/www/vhosts/www.betterliving.be/web/cogitodemo/components/com_fabrik/views/table/view.html.php on line 450

Thanks to ccokn22, I got that these "subqueries" are a MySQL 5 features. I suppose I can more information about that on mysql.com since it is not documented in your manual ?

 

[cheesegrits]

My bad, should be REGEXP, not REGEX.

Also I notice that $my->id hasn't been expanded to your userid. Were you logged in when you tried this?

-- hugh

 

[jfquestiaux]

I think I am making progress but it is still not working.

I tried the SQL request directly in phpMyAdmin and I get a result if I use this request for the IN value (subquery) :

SELECT matieresformateurs FROM #__fabrik_formdata_8 WHERE username = ' . "$my->username" . '

Of course, in phpMyAdmin I use a real username. In Fabrik, the above request gives me a syntax error.

So I tried what you suggested to cookn22, and tried the following query (with eval instead of subquery) :

$database->setQuery('SELECT matieresformateurs FROM #__fabrik_formdata_8 WHERE username = ' . "$my->username" . ');
$results = $database->loadObjectList();
return '(' . explode(',',$results) . ')';

But I still get a syntax error.
However I think this should work, because I noticed that if "matieresformateurs" is "11//..*..//12//..*..//13//..*..//14//..*..//21", I get a COUNT of 0 as result, while if it is only "11" I get a COUNT of 1. So I suppose that the string is posing a problem.

My guess is that there is a problem with the syntax of " username = ' . "$my->username" . ' " but I don't know what it is.

 

[cheesegrits]

Sorry, that should be implode, not explode.

The subquery you were trying won't work, because it'll just return the entire strings, like ""11//..*..//12//..*..//13//..*..//14//..*..//21". Whereas what we need is a list of primary keys for that table where the userid was contained in one of those strings.

Have you tried the REGEXP subquery again, this time using REGEXP instead of my REGEX typo?

-- hugh




-- hugh

 

[cookn22]

Hugh -

Check out my post at the bottom of this thread. It looks like jfquestiaux is having a problem similar to mine, where the prefilters aren't working correctly (there's a NULL value in $afilterEval[0]) and so the prefilters are screwing up and the string is being put in the SQL statement instead of the values returned by the query.

This could be just a bug with my revision of fabrik (although I think I'm using the latest), but I figured I'd mention it since I think it could be the problem here.

 

[cheesegrits]

Noted. Although I don't think that's the problem here, as I can see the 'correct' SQL in the queries he pasted.

JF - do you have more than one pre-filter?

-- hugh

 

[jfquestiaux]

Hello Hugh,

I tried your corrected subquery and I don't have any syntax error any more, but no result either.

In fact, if I test the query directly, it gives me no result so I guess that's where the problem is.

I tried this :

SELECT `fabrik_internal_id` FROM jos_fabrik_formdata_8 WHERE matieresformateurs REGEXP '^profquestiaux//|//profquestiaux//|//profquestiaux$'

The correct answer (11 in this case) is obtained with the following "traditional query" :

SELECT matieresformateurs
FROM jos_fabrik_formdata_8
WHERE username = 'profquestiaux'

So I tried to write it in the format you indicated :

SELECT matieresformateurs
FROM jos_fabrik_formdata_8
WHERE username REGEXP '^profquestiaux//|//profquestiaux//|//profquestiaux$'
LIMIT 0 , 30

but again, no result.

Any thought ?

 

[cheesegrits]

You are searching for the wrong thing. We're looking for userid's, not usernames. So this subquery should do it:

SELECT fabrik_internal_id FROM jos_fabrik_formdata_8 WHERE matieresformateurs REGEXP '^{$my->id}//|//{$my->id}//|//{$my->id}$'

Test that by running that in phpMyAdmin, substituting some valid userid for {$my->id}, so it might look like:

SELECT fabrik_internal_id FROM jos_fabrik_formdata_8 WHERE matieresformateurs REGEXP '^64//|//64//|//64$'

 

[jfquestiaux]

Sorry, but still no result.
I tested it with 67 (the id of the test teacher).
But in fact I was trying with "username" since I use the User Name instead of the ID (I know you recommend the other way around, but I use JUser as registration component and it keeps you from changing your username, so they remain unique).

 

[cheesegrits]

OK, I think I'm just hopelessly confused as to your table structure.

If you want to PM me a backend login and phpMyAdmin login, I'll see if I can sort this out for you. Remember to include a link back to this thread in your PM.

-- hugh

 

[jfquestiaux]

I sent you the informations this afternoon by MP.

 

[cheesegrits]

It'll probably be tomorrow before I can get time to take a look. Am trying to squeeze in some family time this weekend.

-- hugh