List - Displaying left join result in list

 
1
0
-1

Hello,

I'm trying to create datalist for notice form. Every notice's record can have at least one recipient
I modeled this as many-to-many realization (notice - notice_recipient - recipent).

I created list component with "advanced form data" source where i'm joining notice with recipent. Notce is a major table (left side of join) Of course this causes multiplication rows with more than one recipents.

I want to displying unique records in list with column containing recipent in some kind aggregated form (for example joined recipent names by comma).

I'm trying to find some tip in knowlead base but i can't find anything helpful for my case.

How i can display a result of left join in aggregated form on the list ? Any suggestion or tips? 

    CommentAdd your comment...

    1 answer

    1.  
      1
      0
      -1

      I threw yr questions copied exactly into CHATGPT and here is the reply:

      To display the result of a left join in aggregated form on a list, you can use group and aggregate functions in your data source or manipulate the data after retrieving it. Here's a general approach using SQL-like syntax for clarity:

      Assuming you have the following tables:

      1. notice with columns: notice_id, notice_text, etc.
      2. recipient with columns: recipient_id, recipient_name, etc.
      3. notice_recipient with columns: notice_id, recipient_id.

      You can use the following query to retrieve unique notice records with aggregated recipient names:SELECT n.notice_id, n.notice_text, GROUP_CONCAT(r.recipient_name SEPARATOR ', ') AS aggregated_recipients FROM notice n LEFT JOIN notice_recipient nr ON n.notice_id = nr.notice_id LEFT JOIN recipient r ON nr.recipient_id = r.recipient_id GROUP BY n.notice_id;

      In this query, GROUP_CONCAT is used to concatenate recipient names with a comma separator for each notice. Adjust the column names and table aliases based on your actual schema.

      Now, in your list component with "advanced form data" source, use the above SQL query as your data source. The resulting list will contain unique notice records with aggregated recipient names.

      The key is to use grouping and an appropriate aggregate function to achieve the desired result.

      1. kwisniewski

        I considered to use native sql query.
        Unfortunately there is a few problem that disqualify this solution 

        For first, all tables are creating by Joget system and can by modyfied by app creator. My forms have many elements(column). Only few database engine support "group by all" clouse . For my database engine i have to list all colume with select in "group be". Each time as i want to modify form (for example add new element) i will have tu modify query manually. With out modyfication query, userview for this list will be stop working base on exception. That will be genereting many problems in future

        For second, native query have to use native database function. For example Concat are not standarized across all database engine. So by starting using native sql query I lose the transferability between database engine

        For third, Joget provide many functionaly directly connecting with the form. I don't know that ChatGpt can consider the impact on other list and view functionality (filters, sorting mechanism, formatting etc). 

        I don't think so that ChatGpt known all joget functionalty and relation bettwen function to answer correctly for my question. I want too do it in most elastic/proper way (by using joget funtions prepared for this if there exist). I think, native query is the last resort

      CommentAdd your comment...