Fork me on GitHub
  1. Select and having clauses in SQLiss

    I have just added ‘select’ support to query builder. The select statement works like you would expect in SQL but it is much more clever. Because it knows how to fetch external index tables to get attributes and it knows the current table context. For example:

    <div do='images select title as it from projects select title as pt in site' do='each' join=', '><r:pt/>: <r:it/></div>

    This small Zafu sample displays the project title and the image title for all images in the site. This is 1 SQL query: no version fetching, no project fetching and all titles are returned in the current visitor’s language.

    How does this piece of magic work ?

    Well, first of all, the “title” property is “multi-lingual indexed”. This means that we can search and sort records by title because there is a table containing “title, lang, node_id” triplets. Since QueryBuilder knows a lot of stuff about the application’s models, it knows that title is indexed and thus fetches the “title” field from the index table. This would work with any native column or indexed field (even with scope_index), not just for “title”.

    Next, the “select” clause is not attached to the whole query, but to the currently fetched items: “images” and then “projects”.

    Finally, the ZafuCompiler has access to the query object during compilation and therefore knows that we have selected these fields and <r:it/> is properly compiled to return the image’s title.

    I am pretty proud of this one… ;-)

    Having

    Now that we can select with grouping functions (max/min/count), it is interesting to use a “having” clause to only return interesting records. For example, we could return all customers with an invoiced total of more the 10’000.- for the last year.

    First the query to get all customers that have received one or more invoices in a given year (the customer is the “recipient” of an invoice):

    recipients
    from invoices
      where date.year = #{params[:year]}
      in site

    Note that we do not need to “group” because Zena does this automatically.

    Now we can compute the total and only return big customers:

    recipients
    from invoices
      select amount.sum as tot_amount
      where date.year = #{params[:year]}
      in site
    group by id
    having tot_amount > 10000

    Gaspard Bucher

    comments

    1. Tuesday, May 10 2011 13:30 John

      thx ! GG

    2. leave a comment