Getting Data From The Database

There are several approaches to getting data from the database. Depending on the complexity of your app and how you like to organize things.

The following three setups are identical as far as delivering results. We will get a list of people and the company they work for.

Given the following SQL:

CREATE TABLE person (
  id serial primary key,
  company_id int,
  first_name varchar(32),
  last_name varchar(32),
  email varchar(254)
);

CREATE TABLE company (
  id serial primary key,
  name varchar(64)
);

INSERT INTO company (name) VALUES 
  ('ePark Labs'),
  ('Met Gallery'),
  ('OneAero');

INSERT INTO person (company_id, first_name, last_name, email) VALUES
  (1, 'Dan', 'Fitzpatrick', 'dan@eparklabs.com'),
  (1, 'Kim', 'Jones', 'dan@eparklabs.com'),
  (2, 'David', 'Bowie', 'david@met.gallery'),
  (2, 'Danny', 'Elfman', 'danny@met.gallery'),
  (3, 'Barry', 'White', 'bary@met.gallery'),
  (3, 'Jane', 'Dawson', 'jane@one.aero'),
  (3, 'Li', 'Wong', 'li@one.aero');

The results of a request for each URL should be:

Get Everyone

Return all records sorted by the default (last_name).

http://localhost:7777/person/list

Get a Filtered Set

Return a set with first name, last_name, or company matching 'da'.

http://localhost:7777/person/list?q=da

Get a Filtered and Sorted Set

Same as above sorted by first name.

http://localhost:7777/person/list?q=da&order=first_name

Option One - Direct SQL Query - No View. Everything in a single file.

app/person/person.rb

module App::Person
  extend Waxx::Pg
  extend self

  # Write your own get method that includes the company name
  def list_with_company(x, q: nil, order: nil)
    # Guard against SQL injection by only allowing certain sort params
    order = %w(id last_name first_name company_name).include? order ? order : 'last_name'
    if q.to_s.empty?
      sql_where = ['',[]]
    else
      sql_where = ['
        WHERE first_name ILIKE $1
        OR    last_name ILIKE $1
        OR    company_name ILIKE $1',
        ["#{filter}%"] # Doing a starts with query
      ]
    end
    x.db.app.exec("
      SELECT  person.id, person.first_name, person.last_name, 
              person.email, company.name as company_name
      FROM    person LEFT JOIN company ON person.company_id = company.id
      #{sql_where[0]}
      ORDER BY #{order}",
      sql_where[1]
    )
  end

  runs(
    default: "list",
    list: {
      desc: "List people with their company name",
      get: -> (x) {

        # Return HTML
        if x.ext == 'html'
          # Output the table header
          x << %(<table>
            <tr>
              <th>ID</th>
              <th>First Name</th>
              <th>Last Name</th>
              <th>Company</th>
              <th>Email</th>
            </tr>
          )
          # Output the data rows
          list_with_company_name(x, x['q'], x['order']).each{|rec| 
            x << %(
            <tr>
              <td>#{ rec['id'] }</td>
              <td>#{ rec['first_name'].h }</td>
              <td>#{ rec['last_name'].h }</td>
              <td>#{ rec['company'].h }</td>
              <td>#{ rec['email'].h }</td>
            </tr>
            )
            # NOTE: The '.h' method will escape the html
          }
          # End the table
          x << %(</table>)

        # Return JSON
        else
          x << list_with_company_name(x, x['q'], x['order']).map{|rec| rec}.to_json
        end

      }
    }
  )

end

Some things to note:

• This is simple and everything is in one file. Might be good for an app with one or two purposes.
• This makes for some large object files. Not good for app with many purposes.
• Not great for documentation. (See below.)

Option Two - Use A View. Three files required.

The app will have two objects (company and person) and one view (person list).

Company object -- define the columns of the company table.

app/company/company.rb

module App::Company
  extend Waxx::Pg
  extend self

  # Define what columns/fields/attributes this object has
  has(
    id: {pkey: true},
    name: {label: "Company Name"},
  )

  # Normally you would have a runs method here. For this example it is not needed.
  # runs(
  # ...
  # )
end

Person object -- Define the columns the person table has and the relationship to the company table. Add what external interfaces the person object runs.

app/person/person.rb

module App::Person
  extend Waxx::Pg
  extend self

  # Define what columns/fields/attributes this object has
  has(
    id: {pkey: true},
    company_id: {is: "company: company.id+"}, # Left join company
    first_name: {},
    last_name: {},
    email: {}
  )

  runs(
    default: "list",
    list: {
      desc: "List people with their company name",
      get: -> (x) {
        # 'List.run' is a 'Waxx::View' method and will call 'List.get' to get the data and 'List::Html.get' to lay it out as html.
        # 'get' because we are doing a 'get' request and 'Html' because the extension of this request is 'html'.
        # 'List' here is 'App::Person::List' because we are in the 'App::Person' module.
        x << List.run(x) 
      }
    }
  )
end

# Require the view
require_relative 'list'

Person list view -- Define the columns on the view. Add the Html layout. The get method is injected into the view by extend Waxx::View.

app/person/list.rb

module App::Person::List
  extend Waxx::View
  extend self

  has(
    :id,
    :first_name,
    :last_name,
    "company_name: company.name",
    :email
  )

  # Define what columns are searchable with the 'q' parameter
  search_in %w(first_name last_name company_name)

  module Html
    extend Waxx::Html
    extend self

    # Get the Html layout. Note that the 'view' variable is a pointer to App::Person::List
    # The 'h' method escapes the Html.
    def get(x, data, message:{})
      %(
      <table>
        <tr> #{view.columns.map{|n, c| %(<th>#{h c/:label}</th>)}.join} </tr>
        #{rows(x, data)}
      </table>
      )
    end

    def rows(x, data)
      # Output the data rows
      data.map{|rec| 
        %(<tr>#{ view.columns.map{|n, c| %(<td>#{h rec/n}</td>) }.join }</tr>)
      }.join
    end
  end
end

If you are not outputting HTML, and just have a Json web service, you can shrink the view to:

module App::Person::List
  extend Waxx::View
  extend self

  has(
    :id,
    :first_name,
    :last_name,
    "company_name: company.name",
    :email
  )

  # Define what columns are searchable with the x['q'] parameter
  search_in %w(first_name last_name company_name)

  # This will create the code to layout the data as a Json array of hashes
  as :json

end

Some things to note:

• The object file is much smaller as all the view complexity is in the view file.
• If the view gets more complex as the app evolves it is easy to add the complexity to the view.
• The standard view has a lot of built-in functionality but is not the be-all end-all. (See option 3 to customize the view functionality.)

See the docs about everything a Waxx::View can do.

Option 3 - The Hybrid

In some cases you need more power than the standard view. For example if you need to call database functions or build a hairy SQL statement. For this scenario, you still use a view but you write your own get method.

Define the object. Have the list run method get the data from the view and then pass it to the layout.

app/person/person.rb

module App::Person
  extend Waxx::Pg
  extend self

  # Define what columns/fields/attributes this object has
  has(
    id: {pkey: true},
    company_id: {is: "company: company.id+"}, # Left join company (not needed for option 3)
    first_name: {},
    last_name: {},
    email: {}
  )

  runs(
    default: "list",
    list: {
      desc: "List people with their company name",
      get: -> (x) {
        # Get the data from the view. View.get can return any enumerable data type.
        data = List.get(x, q: x['q'], order: x['order'])
        # Return not_found if the layout does not exist
        begin
          layout = List.const_get(x.ext.capitalize)
        rescue NameError => e
          return App.not_found(x)
        end
        # Send the data to the layout in the requested format (Html, Json, etc.)
        x << layout.get(x, data)
      }
    }
  )
end

For this example we will use a database function to get the data we want. This is may not be the approach I would take for this situation but you'll get an idea how the function is used in the view.

The PLPGSQL function (PostgreSQL-specific) is defined as:

CREATE FUNCTION company_people(q varchar, sort varchar) RETURNS
  TABLE(id int, first_name varchar, last_name varchar, company_name varchar, email varchar) AS $$
    DECLARE
      starts_with varchar := q || '%';
      contains varchar := '%' || q || '%';
    BEGIN
      RETURN QUERY EXECUTE 'SELECT person.id, person.first_name, person.last_name, company.name as company_name, person.email
        FROM person LEFT JOIN company ON person.company_id = company.id
        WHERE person.first_name ilike $1
        OR    person.last_name ilike $1
        OR    company.name ilike $1
        OR    person.email ilike $2
        ORDER BY ' || quote_ident(sort)
        USING starts_with, contains;
    END;
  $$ LANGUAGE plpgsql;

Then in the View we will make our own get method to call the function.

app/person/list.rb

module App::Person::List
  extend Waxx::View
  extend self

  as :json

  has(
    :id,
    :first_name,
    :last_name,
    "company_name: company.name",
    :email
  )

  # Get can return any enumerable object
  def get(x, q: nil, order: nil)
    x.db.app.exec("SELECT * FROM company_person($1, $2)",[q, order])
  end

  # This is the same as the Html in Option Two above
  module Html
    extend Waxx::Html
    extend self

    def get(x, data, message:{})
      %(
      <table>
        <tr> #{view.columns.map{|n, c| %(<th>#{h c/:label}</th>)}.join} </tr>
        #{rows(x, data)}
      </table>
      )
    end

    def rows(x, data)
      # Output the data rows
      data.map{|rec| 
        %(<tr>#{ view.columns.map{|n, c| %(<td>#{h rec/n}</td>) }.join }</tr>)
      }.join
    end
  end
end

Advantages of Using A View

1. It is easy to just open the view file and start working on it because everything for the view is in one file and you know where it is based on the URL: A request to /person/list is edited in app/person/list.rb.
2. Documentation! Views can be introspected. App::Person::List.columns (or App::Person::List.has with no arguments) is a hash of the columns on the view. See Documenting your Waxx app.
3. Dynamically building your GUI using the view.columns hash. You can add a 'renderer' attribute to the column and call that to render that field/column.
4. If you need similar functionality in a second view, you can copy and paste and make a few edits (or if you like to rack your brain, you can extend one view with another view).