NQUERY mode again

[DjwStone]

I've collected some more evidence in connection with this problem. This has
mostly been prompted by (1) a suspicion that the 5-table join might be the
cause, and (2) the fear that the 'using unique...' clause could be.

Experiment 1: Remove the joins completely from the layer definition so it
reads:

    DATA          "geom from (SELECT locationref, placename, qualifyingname,
locationtype, locationgridref, parishref, religiousorder, geom, oid FROM
loccoords WHERE placename='%locname%') as foo using unique oid using
srid=27700"

Result 1:
The layer displays OK, with 'locname' substituting and filtering as
expected. Clicking the map at the layer symbol caused the query template to
display with attributes from all the locations named 'locname'

Experiment 2: Re-introduce 2 joins to model a many-to-many relationship
between locations and dedications; note the addition also of the composite
key 'myuid' to use with 'using unique':

    DATA          "geom from (SELECT d.dedicationref, dedicationtype,
dedsaintsnamed, c.locationref, placename, qualifyingname, locationtype,
religiousorder, geom, c.oid, c.locationref||d.dedicationref as myuid  FROM
(dedication d INNER JOIN reftolocn r ON d.dedicationref = r.dedicationref)
INNER JOIN loccoords c ON r.locationref = c.locationref WHERE
placename='%locname%') as foo using unique myuid using srid=27700"

Result 2:
The layer displays OK as before. However, 'querying the map' produces a
completely blank query template (I posted this problem on MAPSERVER-USERS a
while back).

Experiment 3: Remove the 'using unique myuid' from the DATA definition for
Expt.2; this was the only resolution to the blank template problem found so
far.

    DATA          "geom from (SELECT d.dedicationref, dedicationtype,
dedsaintsnamed, c.locationref, placename, qualifyingname, locationtype,
religiousorder, geom, c.oid, c.locationref||d.dedicationref as myuid  FROM
(dedication d INNER JOIN reftolocn r ON d.dedicationref = r.dedicationref)
INNER JOIN loccoords c ON r.locationref = c.locationref WHERE
placename='%locname%') as foo using srid=27700"

Result 3:
The layer displays OK. Querying the map produces the right NUMBER of
results, but their CONTENT is incorrect. For example, when %locname% is set
to 'Leith', the database shows 5 dedications at 3 locations called Leith (1
location has 3 dedications). The query template, however, lists 5 sets of
results each with the correct location attributes, but for the location with
3 dedications, all 3 show the same dedication attributes:

Result no.: 3
Location Ref.: LO/JD/605
Location Type: Chapel
Place-name: Leith
Dedication Type: Church dedication
Dedication Ref.: DE/JD/693

Result no.: 4
Location Ref.: LO/JD/605
Location Type: Chapel
Place-name: Leith
Dedication Type: Church dedication
Dedication Ref.: DE/JD/693   -------------------- should be DE/JD/1584

What this appears to show is that NQUERY isn't handling the join results
correctly (or I'm not setting up the environment correctly). Either way, any
help would be most gratefully received.

Cheers,

Dave
-- 
View this message in context: http://www.nabble.com/NQUERY-mode-again-tf1848174.html#a5100344
Sent from the Mapserver - User forum at Nabble.com.