How to select objects by N:M join?

Question

I have a layer with some geometries and a data layer that defines an M:N relation. Is there any way I could select (on the map) objects that are in relation with specific id? Basically I would like to select by expression like

object_id in (select object_id from relations where relative_id = 12345)

I can do that from the db manager select * from objects where object_id in..., but that means creating new layer each time, which is extra work and needs to be deleted or there would soon be hundreds of them. And the qgis joins don't seem to support M:N relations. Is there a way to select (in qgis, not sql sense) by such query?

The layers happen to be in spatialite database. They look something like

create table objects (object_id integer primary key, geometry, attributes...);
create table relations (object_id integer references objects (object_id),
                        relative_id integer references something);

Answer 1

Why not put your complex query in the database as a view?

Technically your question is about M:N relations in QGIS, and it sounds like between your and GrantD71's comments you've concluded that it is not possible.

So for your case, and as an answer to your actual question, I'm not sure that this is a better solution, it's just what I'd do.

I would either create a view, or load the following query as a layer. Personally, I like views better because I'm usually in PostGIS and I can re-use the same view I use in QGIS in other systems (like web maps and GeoServer).

Assuming tables:

objects
--------
object_id, geometry, attribute1, attribute2,...

relations
--------
object_from_id,object_to_id

Create a view that includes ALL the references (or use this query to define the layer in QGIS).

view
----
SELECT 
    from.*,
    to.object_id AS relative_id
FROM 
    relations,
    object from,
    object to
WHERE
    relations.object_from_id = from.object_id AND
    relations.object_to_id = to.object_id

Create a layer from the view in QGIS (if you're using a view)

Filter based on the relative_id field in QGIS

Filter query: relative_id = 12345

Answer 2

It is possible to do a join and then select by attribute purely in QGIS (without accessing the DB Manager).

Add both of the tables you want to join to QGIS. Right click on the layer with geometry and then go to Properties -> Joins -> click the plus sign to bring up the "Add vector join" dialog -> select the layer and field you want to join on.

You should have an option to keep the join cached in memory, so you can avoid clutter.

With the join created you should be able to open the layer with geometry's attribute table and do selections based on expression.

Answer 3

there is an extension "select by relationship" that is very helpful when i use complex relationships. Maybe in your case it could help too...