[mapguide-users] querying/filtering feature source

[Franco Caloni]

Hi all
I'm developing my first application (MGOS 2.5.2 + Fusion + MS SQL server
2008 R2) and everything goes fine, I also created custom widgets to manage
features (to move and rotate them; right now my features are just point
features).
Next step is to implement a querying/filtering system where end user sets
the appropriate filter and on the map will appear the features that meet the
criteria.

At the moment I have 1 layer that get features from a SQL table (via odbc),
but I need to set features source to a query that involves multiple tables
joined in a classic master-details relationship because the filter must be
applied also on the details table, which may cause duplicated primary key in
the resulting set.
This cause multiple features displayed on the map for the same primary key,
which is not good.

I made some try, googled about this, and didn't find a solutions for this
kind of problem, I mean:

- sql server doesn't allow parametrized view
- it does not allow "select distinct" for geometry columns
- mgos doesn't recognize sql table-valued functions which can be the way to
implement such thing in sql server
- mgos apply filter just on the resulting data set (which may have
duplicated primary keys)

It seems there isn't a solution!

I ended up on think that I have to create 2 views in sql server (1 to use
when no filter on details tables is set up, the other to use when a filter
on the details tables is set up) and at runtime choose the one to use as
features source

what are the best practice in this cases?
I thought that setup a filter was easyer than create custom widget  :)

thanks

Franco