Paging

This page is part of the magic.data.common documentation.

Paging

To page your [sql.read] results, use [limit] and [offset], such as the following illustrates. Even though we use “limit” and “offset”, the correct syntax will be applied for your database type, depending upon which database type you’re using - Implying for Microsoft SQL Server, it will inject MS SQL dialect, and not MySQL dialect. But the syntax for your lambda object still remains the same, making it simpler to create SQL dialect valid for your specific database type 100% transparently.

sql.read
   table:table1
   offset:5
   limit:10

The above will return the following SQL select * from 'table1' limit 10 offset 5. If you run the above lambda towards Microsoft SQL server, SQL syntax specific for MS SQL will be generated.

Aliasing column results

You can also extract columns with an alias, “renaming” the column in its result, such as the following illustrates.

sql.read
   table:table1
   columns

      table1.foo1
         as:howdy

      table1.foo2
         as:world

The above Hyperlambda will result in the following SQL.

select 'table1'.'foo1' as 'howdy','table1'.'foo2' as 'world'
   from 'table1' limit 25

Effectively resulting in that you’ll have two columns returned after executing the above SQL, which are howdy and world. Combining this with the join features from this project, allows you to create any type of “projections” you wish.

Joins

The project supports joins by parametrizing your [sql.read] invocation with [join] arguments, beneath your [table] argument(s). You can only add [join] beneath [table] for [sql.read] invocations though. If you have created the Sakila example database from Oracle, and you’re using MySQL as your default database type, you can execute the following MySQL join SQL statement to see a fairly complex recursive join.

data.connect:sakila
   data.read

      columns
         title
         description
         last_name
         first_name

      table:film

         join:film_actor
            type:inner
            on
               and
                  film.film_id:film_actor.film_id

            join:actor
               type:inner
               on
                  and
                     film_actor.actor_id:actor.actor_id

The above lambda assumes you’ve got Oracle’s Sakila database in your MySQL instance. If you only wish to see its resulting SQL, add a [generate] argument to the above root invocation, and set its value to “true”. All specialised slots, dynamically building and executing some SQL towards your database, supports the [generate] argument, allowing you to easily “debug” your SQL statements, and see what they actually do. Below is an example of the SQL created by the above invocation if you add a [generate] argument to it and set its value to boolean true.

select `film`.`title`, `film`.`description`, `actor`.`last_name`, `actor`.`first_name` from `film`
   inner join `film_actor` on `film`.`film_id` = `film_actor`.`film_id`
      inner join `actor` on `film_actor`.`actor_id` = `actor`.`actor_id`
   limit 25

You can recursively join as many levels as you wish, in addition to also supplying multiple join conditions for the same join. An example of the latter can be found below.

sql.read
   generate:true
   limit:-1
   table:table1

      join:table2
         on
            and
               fk1:pk1
               fk2:pk2

The above lambda will result in the following SQL being generated.

select * from 'table1'
   inner join 'table2' on 'table1'.'fk1' = 'table2'.'pk1' and
      'table1'.'fk2' = 'table2'.'pk2'

Joining tables works almost the exact same way as using a [where] argument, allowing you to supply an operator for your join, such as we illustrate below, where we’re using the != operator, instead of the (default) equality comparison. However, when you create your [join] segments you can only have columns from tables reference columns from tables, and not add static arguments to your join. If you need static arguments you have to add these into your [where] parts. See the [where] criteria for details about comparison operators. You can also explicitly choose a [type] of join, such as we illustrate below.

sql.read
   limit:-1
   table:table1

      join:table2
         type:inner
         on
            and
               fk1.neq:pk1

The above results in the following SQL.

select * from 'table1' inner join 'table2' on 'table1'.'fk1' != 'table2'.'pk1'

The [type] argument to your [join] arguments, can be “inner”, “full”, “left” or “right”, resulting in the equivalent type of join for your SQL.


← Aggregate results all of magic.data.common Differences between [join] and [where] →