search

3liz / lizmap-web-client

Transfer a QGIS project on a server, Lizmap is providing the web interface to browse it
https://www.lizmap.com
Mozilla Public License 2.0
261 stars 144 forks source link

[Bug]: Adding additional fields to table from database view with Joins in QGIS #2515

Open immoblecher opened 3 years ago

immoblecher commented 3 years ago

What is the bug?

A database view is used to extract data from a database (e.g. PostgreSQL) with Date/Time as X-axis and different values on y-axis. So basically 4 fields: ID, sitenr, date, value. The Dataviz charts work perfectly, but: as soon as I add a join in QGIS with other tables to add additional value fields, the charts stop working. This route is chosen as the table I am joining is in another database. Copying the table to the same database and creating the view in the database with the additional fields does not produce the same issue and works flawlessly. The error logs do not give any hints.

Steps to reproduce the issue

  1. Create a database view or use a straight table with an ID, site_id, date and value field
  2. In Lizmap use the Dataviz function to create a scatter plot with Date as X-axis and value field as y-axis
  3. The Dataviz chart should work
  4. Add another table with site_id and data, same as y-axis of original table
  5. Create Join in QGIS and select the data field as joined field with the site_id as common field
  6. Dataviz chart not working any longer, even when adding the new joined field as second trace in dataviz

Lizmap version

3.5.0-rc.2

QGIS desktop version

3.16.11

QGIS server version

3.16.

Operating system

Ubuntu 20.04

Browsers

Firefox

Browsers version

Firefox 92

Relevant log output

No response

Gustry commented 3 years ago

As you are using a join in QGIS, the SQL query to postgresql is failing (missing the join in postgresql, available only in QGIS) Lizmap will fallback on WFS to query the data. Did you enable WFS on both layers in the QGIS Server properties ?

immoblecher commented 3 years ago

Yes, join in QGIS, I am sure I had both layers WFS enabled. Can't check now because I changed the postgreSQL view to include the other table for the two additional columns. Will try again later this afternoon and check.

Immo

On 2021/09/24 09:39, Étienne Trimaille wrote:

As you are using a join in QGIS, the SQL query to postgresql is failing (missing the join in postgresql, available only in QGIS) Lizmap will fallback on WFS to query the data. Did you enable WFS on both layers in the QGIS Server properties ?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/3liz/lizmap-web-client/issues/2515#issuecomment-926414576, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGD6UOH7BD4RA2A35HK6UCTUDQTKBANCNFSM5ET4GAZA. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

--

*Immo F. Blecher /Mobile:///+27 83 272 6791 |+491512639 6176 @. @.> | @. @.**> Internet:*https://www.blecher.co.za/ https://www.blecher.co.za/

Please consider your environmental responsibility. Before printing this e-mail message, ask yourself whether you really need a hard copy.

IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

immoblecher commented 3 years ago

Hi,

I tried again with join inside QGIS and made sure both layers are WFS enabled. As soon as I join with another layer the chart does not show any more.

I have, however, set up a foreign data wrapper and built the view with that inside PostGIS. That works.

Regards

Immo

On 2021/09/24 09:39, Étienne Trimaille wrote:

As you are using a join in QGIS, the SQL query to postgresql is failing (missing the join in postgresql, available only in QGIS) Lizmap will fallback on WFS to query the data. Did you enable WFS on both layers in the QGIS Server properties ?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/3liz/lizmap-web-client/issues/2515#issuecomment-926414576, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGD6UOH7BD4RA2A35HK6UCTUDQTKBANCNFSM5ET4GAZA. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

--

*Immo F. Blecher /Mobile:///+27 83 272 6791 |+491512639 6176 @. @.> | @. @.**> Internet:*https://www.blecher.co.za/ https://www.blecher.co.za/

Please consider your environmental responsibility. Before printing this e-mail message, ask yourself whether you really need a hard copy.

IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.