Hi,
is it possible to use Use addFoundsetFilterParam with more than one dataprovider?
I want to filter the data of a table and the pk of this table is «code, period_fraction_name, period_school_year, subject_code». Now I want to filter with 2 attributes or more! Is this possible with the filter param function?
Regards, Stef
Stefan,
Just add multiple foundset filters before the loadAllRecords() call.
Rob
var success = forms.customer.foundset.addFoundSetFilterParam('customerid', '=', 'BLONP', 'custFilter');//possible to add multiple
success &= forms.customer.foundset.addFoundSetFilterParam('city', '=', 'Amsterdam', 'custFilter');//possible to add multiple
forms.customer.foundset.loadAllRecords();//to make param(s) effective
Hi Rob,
this help. Now it is working!
Thanks.
I guess for the Function addTableFilterParam it is the same? Or am I wrong?
The Problem now is that it is OR linked in the SQL and it has to be an AND!
Like:
code = '4ma' AND period_fraction_name = 'HS' AND period_school_year = 2009 AND subject_code = 'AM'
But if I do this it is OR:
var queryCourses = "SELECT\
ta.course_code,\
ta.course_subject_code,\
ta.course_period_fraction_name,\
ta.course_period_school_year\
FROM\
teaching_allotments ta\
WHERE\
ta.person_id = " + security.getUserUID() + "\
AND ta.course_code IS NOT NULL";
var datasetCourses = databaseManager.getDataSetByQuery(globals.acrDataBaseServerName, queryCourses, null, -1);
databaseManager.addTableFilterParam(globals.acrDataBaseServerName,'courses','code','IN',datasetCourses.getColumnAsArray(1));
databaseManager.addTableFilterParam(globals.acrDataBaseServerName,'courses','course_subject_code','IN',datasetCourses.getColumnAsArray(2));
databaseManager.addTableFilterParam(globals.acrDataBaseServerName,'courses','course_period_fraction_name','IN',datasetCourses.getColumnAsArray(3));
databaseManager.addTableFilterParam(globals.acrDataBaseServerName,'courses','course_period_school_year','IN',datasetCourses.getColumnAsArray(4));
How can I do the AND instead of the OR.
Regards, Stef
Stef,
Table filters are AND-linked, where do you see the OR?
Rob
OK, I think, I have wrote it a littel bit to fuzzy. The Problem is the combinations in the where clause! The Problem is show in this SQL which is produced after setting the filter:
elect abstract, code, course_code, course_period_fraction_name, course_period_school_year, course_subject_code, creation_date, exists_next_period, from_date, maximum_participants, minimum_participants, modification_date, next_period_code, particular_subject_code, period_fraction_name, period_school_year, profile_positions_exist_next_period, subject_code, teaching_allotments_exist_next_period, title, to_date from courses where ((code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?) or (code = ? and period_fraction_name = ? and period_school_year = ? and subject_code = ?)) and code in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_subject_code in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_period_fraction_name in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_period_school_year in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
The data which is filtered is not that I want. Because all is with all combined!
It is the pk: code, period_fracton_name, period_school_year, subject_code. I want only some of them. So I want to filterd them with this SQL
var queryCourses = "SELECT\
ta.course_code,\
ta.course_subject_code,\
ta.course_period_fraction_name,\
ta.course_period_school_year\
FROM\
teaching_allotments ta\
WHERE\
ta.person_id = 1228\
AND ta.course_code IS NOT NULL";
Now I have all keys which should be displayed from courses. And I want to set the tableFilter that only the data with this keys are displayed.
What I have to do to get this in the TabelFilter?
Regards, Stef
Stef,
If you look at the sql carefully, you see that the filter is AND-ed in the sql.
The first bit (with the ORs) is the selection of the rows from the PKs, the last bit (and code in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_subject_code in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_period_fraction_name in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and course_period_school_year in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) is added for the filter.
If you have a single-column PK, the sql is a lot simpler and better performing and you could use a simple filter like:
var queryCourses = "SELECT course_id from courses JOIN teaching_allotments ta on \
courses.course_code = ta.course_code AND \
courses.course_subject_code = ta.course_subject_code AND \
courses.course_period_fraction_name = ta.course_period_fraction_name AND\
courses.course_period_school_year = ta.course_period_school_year
WHERE\
ta.person_id = " + security.getUserUID() + "\
AND ta.course_code IS NOT NULL"
databaseManager.addTableFilterParam(globals.acrDataBaseServerName,'courses','course_id','IN',queryCourses);
Rob
That is true that if i have a singel pk it would be much easier! But I have no singel pk so how do i have to use the filter to get it work? Is there no concept for this in the addFilterParam?
Regards, Stef
why don’t you use a pk which is doesn’t mean anything related to data its just a row identifier?
Because in my opinion it is wrong to define an artificially key if an natural key exists! But is there no concept for the addTabelFilterParam for multi column keys?
Is it possible to do something like concat for more then one dataprovider?
Regards, Stef
In my yes it is the opposite
I see pk as row identifiers which are only that, they don’t contain data, they are not for the human eye’s and it shouldn’t matter what it says it only should be at least unique for that table. (and they shouldn’t be update able either)
You can see it as object references what you have in Java or JavaScript…
if you have a person object and a address object and the person object points to that address object:
function Person()
{
this.address = new Address()
}
then the link between those 2 is not something that is human readable or is user defined data. No it is a id reference maintained by the system (the javascript runtime)
In your case your tables should have an rowident and the columns you now use should just have a unique key constraint over them
Natural Keys vs Surrogate Keys is always a nice topic for heated debate.
Here are 3 nice blog post from Josh Berkus from PostgreSQL Experts on this topic, also read the comments on it.
Primary Keyvil, Part 1
Primary Keyvil, Part 2
Primary Keyvil, Part 3
i agree that a country table you can use 1 data column as the pk.
I don’t agree with pk’s over multiply columns that just makes now sense to me what so ever
Also the argument that you have to have a unique check so you use it as a pk is in my eyes totally bogus, for that you have unique constrains those are not the same as pk’s (or must be implemented as pk’s)
stefbrt:
Because in my opinion it is wrong to define an artificially key if an natural key exists! But is there no concept for the addTabelFilterParam for multi column keys?Is it possible to do something like concat for more then one dataprovider?
Regards, Stef
Hi Stef,
FMI : I think in case of a natural key you use the same (human readable) key in other tables to reference that row.
What do you do when you discover (months later) a typo in that natural key and you want to correct that key ? Update all the related rows with the changed key ?
Regards,
I understand the concept of the row_ident but it is not the same as the concept of the pk has!
So if I interpret your answer right you want to say that the addTableFilterParam has no concept for multi column keys. Am I right?
PS Lambert: The concept of a key says that it is unique and never changes! Otherwise it is not the key!
Regards, Stef
lwjwillemsen:
FMI : I think in case of a natural key you use the same (human readable) key in other tables to reference that row.
What do you do when you discover (months later) a typo in that natural key and you want to correct that key ? Update all the related rows with the changed key ?
For that the onUpdate Cascade Foreign Key constraint exists.
jcompagner:
Also the argument that you have to have a unique check so you use it as a pk is in my eyes totally bogus, for that you have unique constrains those are not the same as pk’s (or must be implemented as pk’s)
Who is this addressed to ? Josh Berkus?
ROCLASI:
jcompagner:
Also the argument that you have to have a unique check so you use it as a pk is in my eyes totally bogus, for that you have unique constrains those are not the same as pk’s (or must be implemented as pk’s)Who is this addressed to ? Josh Berkus?
in general
OK back to my question!
Is there a concept for multi column key for addTableFilterParam? Like Putting dataprovider together (SQL or JavaScript) or something else?
Regards, Stef
I discussed the problem with birgit (a team member) and we think about two solutions for this problem.
The first is to have a syntax to make consciously OR-links. So I have to add four filter AND linked and then link the four by OR with the next four.
The second is to say something like concat(code,subject,…) IN (‘1faD…’, ‘2faE…’,…) in the addTableFilterParam Method.
Is one of these possible with the present Method?
Regards, Stef