Navigation

    APPDRAG Community

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Popular

    REMINDER

    Please be respectful of all AppDragers! Keep it really civil so that we can make the AppDrag community of builders as embracing, positive and inspiring as possible.

    Advanced SQL Field Types

    Cloud Backend (Cloud DB, API Builder)
    4
    15
    890
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Daniel Mulroy
      Daniel Mulroy last edited by

      Hello Everyone,

      Can someone tell me where the definitions of the linked field/fields are?

      What is the difference of linking a field or multiple fields?
      What is the difference between linking by reference or by value?

      Thank you!

      Screenshot 2020-05-29 17.47.42.png

      1 Reply Last reply Reply Quote 0
      • Daniel Mulroy
        Daniel Mulroy last edited by

        Update:
        It seems the linking of multiple fields puts them in some sort of array, but I'm not sure if that's a SQL native method, or if that's just for recovering those id's, etc. in node.js or something later.

        Linking by Value links by value... duh.

        Linking by reference links using the Primary Key of the linked table.

        1 Reply Last reply Reply Quote 0
        • Wassim
          Wassim last edited by

          Hello Daniel,

          • Linked field by reference will store the id of the element in the selected table => 1
          • Linked fields by reference will store a JSON Array of ids => ["1","2"]
          • Linked field by value will store the value of the element in the selected table => 'Jean'
          • Linked fields by value will store a JSON Array of values => ["Frederico","Jessica"]

          The main criteria to decide whether you use by reference or by value is if you want it to be static or updated at each modification.

          If you use by reference, you will need to do join operations to get the linked element but you will always have the updated value.

          If you use by value, you will directly have access to the value but if it changed in the other table, it won't change here. Another concern is that you will only have access to this field from the other table. When you use by reference you can select other fields as well.

          T 1 Reply Last reply Reply Quote 1
          • T
            Thomas D @Wassim last edited by Thomas D

            @wassim said in Advanced SQL Field Types:

            • Linked fields by reference will store a JSON Array of ids => ["1","2"]

            If you use by reference, you will need to do join operations to get the linked element but you will always have the updated value.

            @wassim
            I'm trying to use this, but I'm unable to figure out how to retrieve the referenced column name for the ID's in one query.

            Tried searching for how to do it, but I find nothing on foreign keys and references related to how to query in-place.

            Any help with this would be greatly appreciated!

            1 Reply Last reply Reply Quote 0
            • Wassim
              Wassim last edited by

              Hi @ThomasD I'm not sure about what you want to achieve.
              Can you provide an example with two tables and what you tried with a query?

              Just to let you know what is store in reference is the ID of the element in the referenced table. The fact that it is related to a column is just to display a specific field in the UI (and we use join to fetch them based on the id)

              T 1 Reply Last reply Reply Quote 0
              • T
                Thomas D @Wassim last edited by Thomas D

                @wassim Maybe I'm just over-complicating things, but I'm getting JSON like this:

                { "Table": [{
                      "Published": 1,
                      "Person_relation": ["1","2","3"]
                  }, {
                      "Published": 1,
                      "Person_relation": ["1"]
                  }, {
                      "Published": 1,
                      "Person_relation": ["1"]
                  }]
                }
                

                So I wanted the ID to be replaced with the name value from that table relation column in one query, so it would return this instead.

                { "Table": [{
                      "Published": 1,
                      "Person_relation": ["Mike", "Steven", "August"]
                  }, {
                      "Published": 1,
                      "Person_relation": ["Mike"]
                  }, {
                      "Published": 1,
                      "Person_relation": ["Mike"]
                  }]
                }
                

                I could of course do this in code, but I wanted to make it with SQL only if possible.

                1 Reply Last reply Reply Quote 0
                • Joseph Benguira
                  Joseph Benguira last edited by

                  Hey Thomas, just use Linked Fields by Value instead of By Reference and that's exactly what you will get 😉

                  T 1 Reply Last reply Reply Quote 0
                  • T
                    Thomas D @Joseph Benguira last edited by

                    @Joseph-Benguira That's what I did at first, but that breaks the link entirely when the data changes in the source table.

                    1 Reply Last reply Reply Quote 0
                    • Joseph Benguira
                      Joseph Benguira last edited by Joseph Benguira

                      Ah I see, you can handle that in business logic when you rename through your UI a data to do the corresponding UPDATE in SQL ... or handle this at runtime with a dictionnary ... We can't return directly the merged structure because it would be a major hit to perf.
                      Database do that 100x faster with JOIN in SQL.
                      That way you can do a SQL query with JOIN that will return exactly that in JSON (and still storing by reference)

                      It's always a tradeoff and you have to choose the best solution for your use case

                      You can do basic things easily, you can also do advanced things with SQL or code
                      but you can't do Advanced things without Code and without SQL

                      T 2 Replies Last reply Reply Quote 0
                      • T
                        Thomas D @Joseph Benguira last edited by Thomas D

                        @Joseph-Benguira

                        Yeah, I'm trying with the reference ID and a query to use GROUP_CONCAT and join and group to do it and use an index to keep it fast, should work well enough..

                        I've only every used NoSQL my whole life, so MySQL is a bit of a challenge 😉

                        About time I learned it though

                        1 Reply Last reply Reply Quote 0
                        • T
                          Thomas D @Joseph Benguira last edited by

                          @Joseph-Benguira

                          I'm failing to produce what I need, the field doesn't seem to be a foreign key with a reference as it claims to be, but just a text field with the array ID's.

                          I'm guessing you have custom logic behind that, but I fail to know how to use the ID's other than as only static values.

                          1 Reply Last reply Reply Quote 0
                          • Joseph Benguira
                            Joseph Benguira last edited by

                            What you can do in the cloud function is to make 2 queries, one to get the main data, and another one to get the references from your ref table, then loop on the main results and enrich them based on what you have in the ref table. So this solution is few lines of node.js

                            There is another way we also use quite often, the idea is to return the data with the refs in an array and in the same api call also return the dictionnaries

                            E.g: in your node.js function

                            var result = {
                                rows: yourRowsHere,
                                dic: rowsFromAnotherQueryToYourRefTable
                            };
                            callback(null, result);
                            

                            Then in the frontend code use the dictionnary when interpreting the rows.

                            T 1 Reply Last reply Reply Quote 0
                            • T
                              Thomas D @Joseph Benguira last edited by Thomas D

                              @Joseph-Benguira

                              Ideas:

                              1. At first my idea was to use what you suggest, use node to make calls of each referenced table row, but I thought using joins would be faster. Plus, the automatic database GUI forms could not be used when using several separate tables.

                              2. The dictionaries idea is interesting, we could gather all the ref unique ID's and combine that into an array of ID's that is needed for the request and fetch that only once. This should be faster than a join because we'd not be fetching the rows multiple times for every item.

                              3. Seems like it's easy to choose a design that would be limiting though, a proper many-to-many relation would enable more features while persisting performance. But it would mean we'd have to make custom input forms/functions and write raw db queries.

                              I'll compile this into a how-to when I've tested it more.

                              (I managed to get a join query working on the arrays btw)

                              1 Reply Last reply Reply Quote 0
                              • T
                                Thomas D last edited by

                                @Joseph-Benguira By the way, is there any way I can test the query performance?

                                1 Reply Last reply Reply Quote 0
                                • Joseph Benguira
                                  Joseph Benguira last edited by

                                  You can run the in "New Query", this is returning the execution time in miliseconds of your query
                                  Another great thing you can do to improve query speed is to add indexes on your tables on the fields you are using in WHERE or JOIN conditions. You can get a 100x improvement with indexes 🙂

                                  a1108917-d540-49f3-9d80-0e15cb45597f-image.png

                                  It's accessible from the EDIT button when you are inside a table

                                  1 Reply Last reply Reply Quote 1
                                  • First post
                                    Last post