Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Janneke van Dijk 
3.6, but will check when I have time later tonight and see if other 
versions show the same issue. And will create a sample project with bug 
report.

Thanks!
Janneke

On 19/09/2019 18:04, Alexandre Neto wrote:

It sounds like a bug...
Can you provide a data sample and project. Also, what QGIS version are 
you using?


Alexandre Neto

On Thu, Sep 19, 2019 at 3:57 PM Janneke van Dijk 
mailto:janneke.q...@gmail.com>> wrote:


Hi Alexandre,
Also thanks for the quick response!

What I can do with the DB manager is:



select location_point.geom, asset_point.assetid,
location_point.fid
from asset_point cross join location_point on
asset_point.locationid =
location_point.fid


This should work, but why the CROSS JOIN? a simple join should be
enough.

I first had a simple join, then saw an example of a cross join and
tried that in the hope that that would make a difference.


this query yields 170 records, corresponding with the number
of records
in the assets table. When I load as a new layer, the result
is a table
with 170 records, but 100 filtered (the total number of
locations). I
chose assetid to be the unique id (which it is).

Is that expected behaviour? How else can I display all the
records in
the assets table (instead of only the 100 that are filtered
now)? I know
I can do it in Postgres but for my purpose now I don't want
to go there.


An alternative is to create a relation between the two tables,
the assets will show in the location feature form. You can even
add more assets to a certain location.

I have a relationship, and the assets will show up in the form of
the location when you click on the location (including locations
with several). However, it doesn't allow me to label the location
with information from the assets, or create symbology based on
asset information.

What puzzles me is that the query results in 170 records, I would
have expected the geometry to be joined to each of the 170
attribute records and then be displayed - so where does the filter
with a 100 suddenly come from? (Is this a bug?)

As a work around I think I could get xy coordinates of the
location, then join them to the assets, export to csv and import
again as a delimited text layer for display purposes.

I was hoping someone could either explain why this result makes
sense, or that I just made a mistake somewhere...any more thoughts?

Thanks for sharing your thoughts!

Janneke



Alexandre Neto





___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Alexandre Neto 
It sounds like a bug...
Can you provide a data sample and project. Also, what QGIS version are you
using?

Alexandre Neto

On Thu, Sep 19, 2019 at 3:57 PM Janneke van Dijk 
wrote:

> Hi Alexandre,
> Also thanks for the quick response!
>
> What I can do with the DB manager is:
>
>
>> select location_point.geom, asset_point.assetid, location_point.fid
>> from asset_point cross join location_point on asset_point.locationid =
>> location_point.fid
>>
>>
> This should work, but why the CROSS JOIN? a simple join should be enough.
>
> I first had a simple join, then saw an example of a cross join and tried
> that in the hope that that would make a difference.
>
>
>
>> this query yields 170 records, corresponding with the number of records
>> in the assets table. When I load as a new layer, the result is a table
>> with 170 records, but 100 filtered (the total number of locations). I
>> chose assetid to be the unique id (which it is).
>>
>> Is that expected behaviour? How else can I display all the records in
>> the assets table (instead of only the 100 that are filtered now)? I know
>> I can do it in Postgres but for my purpose now I don't want to go there.
>>
>>
> An alternative is to create a relation between the two tables, the assets
> will show in the location feature form. You can even add more assets to a
> certain location.
>
> I have a relationship, and the assets will show up in the form of the
> location when you click on the location (including locations with several).
> However, it doesn't allow me to label the location with information from
> the assets, or create symbology based on asset information.
>
> What puzzles me is that the query results in 170 records, I would have
> expected the geometry to be joined to each of the 170 attribute records and
> then be displayed - so where does the filter with a 100 suddenly come from?
> (Is this a bug?)
>
> As a work around I think I could get xy coordinates of the location, then
> join them to the assets, export to csv and import again as a delimited text
> layer for display purposes.
>
> I was hoping someone could either explain why this result makes sense, or
> that I just made a mistake somewhere...any more thoughts?
>
> Thanks for sharing your thoughts!
>
> Janneke
>
>
> Alexandre Neto
>
>
>
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Janneke van Dijk 

Indeed

On 19/09/2019 17:56, DelazJ wrote:
You mean, it's the selected field in the primary key drop-down menu in 
DB manager when you want to add the layer?


Le jeu. 19 sept. 2019 à 16:42, Janneke van Dijk 
mailto:janneke.q...@gmail.com>> a écrit :


Hi Harrissou,
Thanks for the quick response - indeed, I indicated the assetid
which is a unique value. That did not solve the problem, alas...
Janneke

On 19/09/2019 17:31, DelazJ wrote:

Hi Janneke,

Maybe you need a field that would uniquely identify the 170 asset
rows (their primary key). You can use an ID field of your asset
layer or construct one with row_number function?


Regards,
Harrissou

Le jeu. 19 sept. 2019 à 16:10, Janneke van Dijk
mailto:janneke.q...@gmail.com>> a écrit :

Dear list,

I have a parent - child relationship set up between a table
location (1,
the parent) and a table assets (many, the child). The
location table
contains geometry, the asset table does not. I would like to
be able to
display each record in the asset table with the corresponding
geometry
of the location table - so if there are 3 assets at a certain
location,
I would like to display 3 points (each with identical geometry).

What I can do with the DB manager is:

select location_point.geom, asset_point.assetid,
location_point.fid
from asset_point cross join location_point on
asset_point.locationid =
location_point.fid

this query yields 170 records, corresponding with the number
of records
in the assets table. When I load as a new layer, the result
is a table
with 170 records, but 100 filtered (the total number of
locations). I
chose assetid to be the unique id (which it is).

Is that expected behaviour? How else can I display all the
records in
the assets table (instead of only the 100 that are filtered
now)? I know
I can do it in Postgres but for my purpose now I don't want
to go there.

Any insights deeply appreciated!

Janneke


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org 
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user





___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Janneke van Dijk 

Hi Alexandre,
Also thanks for the quick response!

What I can do with the DB manager is:



select location_point.geom, asset_point.assetid, location_point.fid
from asset_point cross join location_point on
asset_point.locationid =
location_point.fid


This should work, but why the CROSS JOIN? a simple join should be enough.
I first had a simple join, then saw an example of a cross join and tried 
that in the hope that that would make a difference.


this query yields 170 records, corresponding with the number of
records
in the assets table. When I load as a new layer, the result is a
table
with 170 records, but 100 filtered (the total number of locations). I
chose assetid to be the unique id (which it is).

Is that expected behaviour? How else can I display all the records in
the assets table (instead of only the 100 that are filtered now)?
I know
I can do it in Postgres but for my purpose now I don't want to go
there.


An alternative is to create a relation between the two tables, the 
assets will show in the location feature form. You can even add more 
assets to a certain location.
I have a relationship, and the assets will show up in the form of the 
location when you click on the location (including locations with 
several). However, it doesn't allow me to label the location with 
information from the assets, or create symbology based on asset information.


What puzzles me is that the query results in 170 records, I would have 
expected the geometry to be joined to each of the 170 attribute records 
and then be displayed - so where does the filter with a 100 suddenly 
come from? (Is this a bug?)


As a work around I think I could get xy coordinates of the location, 
then join them to the assets, export to csv and import again as a 
delimited text layer for display purposes.


I was hoping someone could either explain why this result makes sense, 
or that I just made a mistake somewhere...any more thoughts?


Thanks for sharing your thoughts!

Janneke



Alexandre Neto



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread DelazJ 
You mean, it's the selected field in the primary key drop-down menu in DB
manager when you want to add the layer?

Le jeu. 19 sept. 2019 à 16:42, Janneke van Dijk  a
écrit :

> Hi Harrissou,
> Thanks for the quick response - indeed, I indicated the assetid which is a
> unique value. That did not solve the problem, alas...
> Janneke
>
> On 19/09/2019 17:31, DelazJ wrote:
>
> Hi Janneke,
>
> Maybe you need a field that would uniquely identify the 170 asset rows
> (their primary key). You can use an ID field of your asset layer or
> construct one with row_number function?
>
>
> Regards,
> Harrissou
>
> Le jeu. 19 sept. 2019 à 16:10, Janneke van Dijk 
> a écrit :
>
>> Dear list,
>>
>> I have a parent - child relationship set up between a table location (1,
>> the parent) and a table assets (many, the child). The location table
>> contains geometry, the asset table does not. I would like to be able to
>> display each record in the asset table with the corresponding geometry
>> of the location table - so if there are 3 assets at a certain location,
>> I would like to display 3 points (each with identical geometry).
>>
>> What I can do with the DB manager is:
>>
>> select location_point.geom, asset_point.assetid, location_point.fid
>> from asset_point cross join location_point on asset_point.locationid =
>> location_point.fid
>>
>> this query yields 170 records, corresponding with the number of records
>> in the assets table. When I load as a new layer, the result is a table
>> with 170 records, but 100 filtered (the total number of locations). I
>> chose assetid to be the unique id (which it is).
>>
>> Is that expected behaviour? How else can I display all the records in
>> the assets table (instead of only the 100 that are filtered now)? I know
>> I can do it in Postgres but for my purpose now I don't want to go there.
>>
>> Any insights deeply appreciated!
>>
>> Janneke
>>
>>
>> ___
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Janneke van Dijk 

Hi Harrissou,
Thanks for the quick response - indeed, I indicated the assetid which is 
a unique value. That did not solve the problem, alas...

Janneke

On 19/09/2019 17:31, DelazJ wrote:

Hi Janneke,

Maybe you need a field that would uniquely identify the 170 asset rows 
(their primary key). You can use an ID field of your asset layer or 
construct one with row_number function?



Regards,
Harrissou

Le jeu. 19 sept. 2019 à 16:10, Janneke van Dijk 
mailto:janneke.q...@gmail.com>> a écrit :


Dear list,

I have a parent - child relationship set up between a table
location (1,
the parent) and a table assets (many, the child). The location table
contains geometry, the asset table does not. I would like to be
able to
display each record in the asset table with the corresponding
geometry
of the location table - so if there are 3 assets at a certain
location,
I would like to display 3 points (each with identical geometry).

What I can do with the DB manager is:

select location_point.geom, asset_point.assetid, location_point.fid
from asset_point cross join location_point on
asset_point.locationid =
location_point.fid

this query yields 170 records, corresponding with the number of
records
in the assets table. When I load as a new layer, the result is a
table
with 170 records, but 100 filtered (the total number of locations). I
chose assetid to be the unique id (which it is).

Is that expected behaviour? How else can I display all the records in
the assets table (instead of only the 100 that are filtered now)?
I know
I can do it in Postgres but for my purpose now I don't want to go
there.

Any insights deeply appreciated!

Janneke


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org 
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Alexandre Neto 
Hi


On Thu, Sep 19, 2019 at 3:10 PM Janneke van Dijk 
wrote:

>
> What I can do with the DB manager is:
>
> select location_point.geom, asset_point.assetid, location_point.fid
> from asset_point cross join location_point on asset_point.locationid =
> location_point.fid
>
>
This should work, but why the CROSS JOIN? a simple join should be enough.


> this query yields 170 records, corresponding with the number of records
> in the assets table. When I load as a new layer, the result is a table
> with 170 records, but 100 filtered (the total number of locations). I
> chose assetid to be the unique id (which it is).
>
> Is that expected behaviour? How else can I display all the records in
> the assets table (instead of only the 100 that are filtered now)? I know
> I can do it in Postgres but for my purpose now I don't want to go there.
>
>
An alternative is to create a relation between the two tables, the assets
will show in the location feature form. You can even add more assets to a
certain location.

Check more info here:

https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/attribute_table.html?highlight=relation#introducing-1-n-relations

Hope it helps.

Alexandre Neto
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread DelazJ 
Hi Janneke,

Maybe you need a field that would uniquely identify the 170 asset rows
(their primary key). You can use an ID field of your asset layer or
construct one with row_number function?


Regards,
Harrissou

Le jeu. 19 sept. 2019 à 16:10, Janneke van Dijk  a
écrit :

> Dear list,
>
> I have a parent - child relationship set up between a table location (1,
> the parent) and a table assets (many, the child). The location table
> contains geometry, the asset table does not. I would like to be able to
> display each record in the asset table with the corresponding geometry
> of the location table - so if there are 3 assets at a certain location,
> I would like to display 3 points (each with identical geometry).
>
> What I can do with the DB manager is:
>
> select location_point.geom, asset_point.assetid, location_point.fid
> from asset_point cross join location_point on asset_point.locationid =
> location_point.fid
>
> this query yields 170 records, corresponding with the number of records
> in the assets table. When I load as a new layer, the result is a table
> with 170 records, but 100 filtered (the total number of locations). I
> chose assetid to be the unique id (which it is).
>
> Is that expected behaviour? How else can I display all the records in
> the assets table (instead of only the 100 that are filtered now)? I know
> I can do it in Postgres but for my purpose now I don't want to go there.
>
> Any insights deeply appreciated!
>
> Janneke
>
>
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] 1 to many relationship does not allow to display the many

2019-09-19 Thread Janneke van Dijk 

Dear list,

I have a parent - child relationship set up between a table location (1, 
the parent) and a table assets (many, the child). The location table 
contains geometry, the asset table does not. I would like to be able to 
display each record in the asset table with the corresponding geometry 
of the location table - so if there are 3 assets at a certain location, 
I would like to display 3 points (each with identical geometry).


What I can do with the DB manager is:

select location_point.geom, asset_point.assetid, location_point.fid  
from asset_point cross join location_point on asset_point.locationid = 
location_point.fid


this query yields 170 records, corresponding with the number of records 
in the assets table. When I load as a new layer, the result is a table 
with 170 records, but 100 filtered (the total number of locations). I 
chose assetid to be the unique id (which it is).


Is that expected behaviour? How else can I display all the records in 
the assets table (instead of only the 100 that are filtered now)? I know 
I can do it in Postgres but for my purpose now I don't want to go there.


Any insights deeply appreciated!

Janneke


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user