[Qgis-user] Cross layer filtering

Mon Apr 1 03:36:52 PDT 2024

Hi,

So, after playing around with this I discovered that whilst it is still slow the solution is to split up my virtual layer selection. Here is an example:
select a.* from TABLE_WITH_MANY a LEFT JOIN (SELECT * FROM TABLE_WITH_ONE WHERE SelectedRow = 1) b ON a.SHARED_REF = b. SHARED_REF WHERE b.SelectedRow IS NOT NULL;

This means that the initial nested select statement will return a small number of results, so the second outer select statement has less to do.

A few have asked if I can use a predefined filter, theme, or some other method but as I think you have spotted that is not the case in my scenario as the user is selecting and I'm running the report off that selection. They may have selected any number of records, but I will likely put in a limit warning of about 50 features to ensure that performance is not too slow.

My goal, as is often the case it seems, is to automate as much as possible to avoid the users needing to delve into the powerful but often not the most intuitive aspects of QGIS.

Thanks for the suggestions though,
Paul

-----Original Message-----

Message: 1
Date: Fri, 29 Mar 2024 22:05:29 +0100
From: Bernd Vogelgesang <mail at berndvogelgesang.de>
To: qgis-user at lists.osgeo.org
Subject: Re: [Qgis-user] Cross layer filtering
Message-ID: <22270100-8539-48cd-9c17-c643e4f3f7b1 at berndvogelgesang.de>
Content-Type: text/plain; charset="utf-8"; Format="flowed"

Hi,

idea #1: Do not create a virtual layer (tried this way for years and its still somehow a nightmare), but just duplicate you layer and switch it to rule based rendering with a CASE WHEN clause for the selected property.

idea #2: I do not know what you exactly mean with "bring in an associated layer" and how much of it you have to bring in, but maybe aggregate functions in other virtual fields will do the job?

I just recently dived into the aggregate stuff, and there are not too many good examples around. I find it pretty stable and did not encounter any crashes like with virtual layers (it just works or not). Might take a while for calculations (but that is just a question of hardware upgrades ;) ) When this is just for layout purposes in the composer, rendering time should not be as significant as well.

#3: Use themes!

When running into troubles, just ping me.

Cheers,
Bernd

p.s. maybe just bad ideas, but this just popped into my mind after my third beer

Am 28.03.24 um 14:39 schrieb Paul Wittle via QGIS-User:
> Hi,
>
> I?ve been filtering a layer by adding a virtual column populated with
> is_selected() and then creating a virtual layer from it using the filter:
>
> WHERE virtualField = 1
>
> This is working well and allows you to have a copy of the layer that
> only shows the selected records when you are creating a layout.
>
> I now need to bring in an associated layer, but I can?t find a
> reliable way to cross layer filter because QGIS just hangs for ages and crashes.
>
> I?ve searched around and can see a lot of people talk about making the
> database do the work but of course it can?t if you are trying to use a
> selection filter.
>
> What is the most reliable way of setting up a filter based on a join
> because QGIS tells me that I must use a virtual layer and even creates
> it for me; but then it just hangs and crashes.
>
> Is there a better way?
>
> Info required to replicate:
>
>   * Create a layer (mine is from Oracle DB) and add a virtual field
>     called ?SelectedRow? with the formula ?to_int(is_selected())?
>   * Bring in a second layer that has a one-to-many relationship based on
>     an attribute
>   * I?m trying to filter the second layer based on the first one where
>     the second layer must only include rows that match a selected key.
>
> Thank in advance for any tips,
>
> Paul
>
> This e-mail and any files transmitted with it are intended solely for
> the use of the individual or entity to whom they are addressed. It may
> contain unclassified but sensitive or protectively marked material and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use
> it, or disclose it to anyone else. If you have received this
> transmission in error please notify the sender immediately. All
> traffic may be subject to recording and/or monitoring in accordance with relevant legislation.
> Any views expressed in this message are those of the individual
> sender, except where the sender specifies and with authority, states
> them to be the views of Dorset Council. Dorset Council does not accept
> service of documents by fax or other electronic means. Virus checking:
> Whilst all reasonable steps have been taken to ensure that this
> electronic communication and its attachments whether encoded,
> encrypted or otherwise supplied are free from computer viruses, Dorset
> Council accepts no liability in respect of any loss, cost, damage or
> expense suffered as a result of accessing this message or any of its
> attachments. For information on how Dorset Council processes your
> information, please see www.dorsetcouncil.gov.uk/data-protection
>
> _______________________________________________
> QGIS-User mailing list
> QGIS-User at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

--
Dipl.-Geogr.
Bernd Vogelgesang
Kappel 17
91355 Hiltpoltstein
Tel: 09192-3499427
mobil: 0163-1860160
mail at berndvogelgesang.de
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mail.vcf
Type: text/vcard
Size: 254 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20240329/73207e97/attachment-0001.vcf>

------------------------------

Subject: Digest Footer

_______________________________________________
QGIS-User mailing list
QGIS-User at lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

------------------------------

End of QGIS-User Digest, Vol 217, Issue 59
******************************************
This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/data-protection