Horton

Thread

  1. ORDER BY elements in ARRAY

    Andreas Joseph Krogh <andreas@visena.com> — 2024-08-27T11:22:49Z

    
Hi, I have this query:


SELECT comp.entity_id as company_id, comp.companyname AS company_name , ARRAY( 
SELECT (pers.entity_id , pers.firstname , pers.lastname )::PersonTypeTest FROM 
onp_crm_person AS pers WHERE pers.relation_id = comp.entity_id ) as 
person_array FROM onp_crm_relation comp ORDER BY person_array ;


And I'd like to ORDER BY (conceptually) lower(pers.firstname), 
lower(pers.lastname). I realize there might be more than 1 person in the array, 
so I'd like to order by “firstname and lastname of first element, the same for 
second etc.”



Any way to do this?






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  2. Re: ORDER BY elements in ARRAY

    Guillaume Lelarge <guillaume@lelarge.info> — 2024-08-27T11:44:02Z

    Hi,

Le mar. 27 août 2024 à 13:23, Andreas Joseph Krogh <andreas@visena.com> a
écrit :

> Hi, I have this query:
>
>
>
> SELECT
>     comp.entity_id as company_id, comp.companyname AS company_name
>      , ARRAY(
>         SELECT
>             (pers.entity_id
>                 , pers.firstname
>                 , pers.lastname
>                 )::PersonTypeTest
>         FROM onp_crm_person AS pers
>         WHERE pers.relation_id = comp.entity_id
>        ) as person_array
>
> FROM onp_crm_relation comp
> ORDER BY person_array
> ;
>
>
>
> And I'd like to ORDER BY (conceptually) lower(pers.firstname),
> lower(pers.lastname). I realize there might be more than 1 person in the
> array, so I'd like to order by “firstname and lastname of first element,
> the same for second etc.”
>
>
>
> Any way to do this?
>
> array_agg might be what you're looking for, but you'll have to extract the
subquery, and transform it to a join on the main query.

Regards.


-- 
Guillaume.

  3. Re: ORDER BY elements in ARRAY

    David G. Johnston <david.g.johnston@gmail.com> — 2024-08-27T12:39:34Z

    On Tuesday, August 27, 2024, Andreas Joseph Krogh <andreas@visena.com>
wrote:

> Hi, I have this query:
>
>
>
> SELECT
>     comp.entity_id as company_id, comp.companyname AS company_name
>      , ARRAY(
>         SELECT
>             (pers.entity_id
>                 , pers.firstname
>                 , pers.lastname
>                 )::PersonTypeTest
>         FROM onp_crm_person AS pers
>         WHERE pers.relation_id = comp.entity_id
>
>
> Add order by here

>
>
>        ) as person_array
>
> FROM onp_crm_relation comp
> ORDER BY person_array
> ;
>
>
David J.

  4. Re: ORDER BY elements in ARRAY

    Andreas Joseph Krogh <andreas@visena.com> — 2024-08-27T14:59:41Z

    
På tirsdag 27. august 2024 kl. 14:39:34, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
[…]

Add order by here  ) as person_array FROM onp_crm_relation comp ORDER BY 
person_array ;

David J.


This doesn't really do it. I'm looking for something analogous to Java's 
Comparator-interface so the custom datatype knows how to sort itself.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>

  5. Re: ORDER BY elements in ARRAY

    David G. Johnston <david.g.johnston@gmail.com> — 2024-08-27T16:03:37Z

    On Tuesday, August 27, 2024, Andreas Joseph Krogh <andreas@visena.com>
wrote:

> På tirsdag 27. august 2024 kl. 14:39:34, skrev David G. Johnston <
> david.g.johnston@gmail.com>:
>
> […]
> Add order by here
>
>>        ) as person_array
>>
>> FROM onp_crm_relation comp
>> ORDER BY person_array
>> ;
>>
>>
> David J.
>
>
>
> This doesn't really do it. I'm looking for something analogous to Java's
> Comparator-interface so the custom datatype knows how to sort itself.
>

Ah.  Then either just put those two values into the first two field
positions of your custom type and live with an easy, but also
self-contained, hack, or figure out what incantations of create operator
and/or create operator family are need to install custom behavior for the
b-tree < and > operators.  I’m 80% sure it’s doable, 60% in plpgsql…

David J.

  6. Re: ORDER BY elements in ARRAY

    Andreas Joseph Krogh <andreas@visena.com> — 2024-08-27T16:27:26Z

    
På tirsdag 27. august 2024 kl. 18:03:37, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
On Tuesday, August 27, 2024, Andreas Joseph Krogh <andreas@visena.com 
<mailto:andreas@visena.com>> wrote:
[…]



This doesn't really do it. I'm looking for something analogous to Java's 
Comparator-interface so the custom datatype knows how to sort itself.


Ah. Then either just put those two values into the first two field positions 
of your custom type and live with an easy, but also self-contained, hack, or 
figure out what incantations of create operator and/or create operator family 
are need to install custom behavior for the b-tree < and > operators. I’m 80% 
sure it’s doable, 60% in plpgsql…

David J.


Ah, seems CREATE OPERATOR is worth pursuing.

Thanks.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>