The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
SQL-Builder-0.03
/ SQL::Builder::Select

NAME

SQL::Builder::Select - Represent a SQL SELECT statement

SYNOPSIS

        In the following examples I'll be using figurative database tables that
        look like:

        users
        ---------
        user_id
        name
        age
        salary_id

        salaries
        ---------
        salary_id
        salary_amount

Most of the functionality provided by this module is provided by the various classses is uses. This module actually does very little.

        # new select object

        my $sel = SQL::Builder::Select->new;

        # add a table

        $sel->tables->add_table(table => "users", alias => "u");

        # SELECT * FROM users AS u
        print $sel->sql;

We can add more tables to the table list like so:

        $sel->tables->add_table(table => "salaries", alias => "sals");

        # SELECT * FROM users AS u, salaries AS sals
        print $sel->sql;

And of course add a WHERE clause

        $sel->where->list_push("u.salary_id = sals.salary_id");

Suppose the database supports JOINs, we can use those to:

        my $sel = SQL::Builder::Select->new();

        $sel->tables->add_table(table => "users", alias => "u");

        $sel->tables->add_join(
                table => "salaries",
                'using->list_push' => "salary_id"
        );

        # SELECT * FROM users AS u JOIN salaries USING (salary_id)

Since we care about state, we can do the same thing, except more statefully, and instead of using the USING operator, we'll use ON for the join:

        # I'm using globals for simplicity. Please don't do it in code that
        # matters


        # define a users table
        our $tbl_users    = SQL::Builder::Table->new(
                                name => "users",
                                alias => "u"
                        );
        
        # define the salaries table
        our $tbl_salaries = SQL::Builder::Table->new(
                                name => "salaries",
                                alias => "sals"
                        );


        sub get_user_pay        {
                
                # declare a new SELECT object
                my $sel = SQL::Builder::Select->new();
                
                
                # add the users table to the SELECT list
                $sel->tables->list_push($tbl_users);
                
                # join against the salaries table
                $sel->tables->joins->list_push(
                        SQL::Builder::Join->new(
                                table => $tbl_salaries,
                                'on->list_push' => SQL::Builder::BinaryOp->new(
                                        op  => "=",
                                        lhs => $tbl_salaries->col(name => 'salary_id'),
                                        rhs => $tbl_users->col(name => 'salary_id')
                                )
                        )
                );

                return $sel
        }

Now that we have a stateful representation of our query, it's easy to reuse and modify:

        my $query = get_user_pay();

        # suppose we only want the user_id and salary_amount columns returned

        $query->cols->list_push(
            $tbl_users->col(name => 'user_id'),
            $tbl_salaries->col(name => 'salary_amount')
        );

        # SELECT u.user_id, sals.salary_amount
        # FROM users AS u
        # JOIN salaries AS sal ON sal.salary_id = u.salary_id

We can also filter the results by using a WHERE clause. The following will return users who earn more than $20,000

        $query->where->list_push(
                SQL::Builder::BinaryOp->(
                        op  => ">",
                        lhs => $tbl_salaries->col(name => 'salary_amount'),
                        rhs => 20_000
                )
        );

        # SELECT u.user_id, sals.salary_amount
        # FROM users AS u
        # JOIN salaries AS sal ON sal.salary_id = u.salary_id
        # WHERE u.salary_amount > 20000

But that's not all, we use a "macro" to modify the query to return all users who have a salary of less than $20,000. We'll just flip all instances of ">" with "<":

        # find all objects in the WHERE clause with an op()
        # method that returns ">"

        my $iterator = $query->where->look_down(
                op => ">"
        );

        while($iterator->pull)  {
                my $binary_op = $iterator->current;
                
                # flip the sign
                $binary_op->op("<");
        }

        # SELECT u.user_id, sals.salary_amount
        # FROM users AS u
        # JOIN salaries AS sal ON sal.salary_id = u.salary_id
        # WHERE u.salary_amount < 20000

For databases that support them, we can easily limit the amount of results also:

        $query->limit(10);

        # SELECT u.user_id, sals.salary_amount
        # FROM users AS u
        # JOIN salaries AS sal ON sal.salary_id = u.salary_id
        # WHERE u.salary_amount > 20000
        # LIMIT 10

pagination is also useful:

        $query->offset(10);

        # SELECT u.user_id, sals.salary_amount
        # FROM users AS u
        # JOIN salaries AS sal ON sal.salary_id = u.salary_id
        # WHERE u.salary_amount > 20000
        # LIMIT 10 OFFSET 10

There is also support for the other common constructs:

        # ORDER BY u.name, sals.salary_amount
        $query->orderby->list_push(
                $tbl_salaries->col(name => 'salary_amount')
        );

        # GROUP BY u.user_id, sals.salary_amount
        $query->orderby->list_push(
                $tbl_users->col(name => 'user_id'),
                $tbl_salaries->col(name => 'salary_amount'),
        );

        # DISTINCT 
        $query->distinct(1);

        # HAVING
        $query->having(SQL::Builder::BinaryOp->new(
                op  => ">",
                lhs => SQL::Builder::AggregateFunction->new(
                        name => 'COUNT',
                        args => "*"
                ),
                rhs => 0
        ))

DESCRIPTION

SQL::Builder::Select is a child of SQL::Builder::Base(3). Its functionality is provided by the objects it implements. These objects can be changed and even removed; any replacements will be passed through SQL::Builder::Base::dosql() before SQL serialization (sql()). It's possible to use these objects without provided a state (using strings instead of objects), but that would defeat much of the purpose of SQL::Builder.

Some of the example code in the SYNOPSIS is rather wordy, but the future interface will expected to be a little more lightweight. At the very least I expect to have exportable functions that wrap to the object constructors. The more intelligent way of removing wordiness is to subclass the objects you use. This will allow you to apply more useful defaults and states to the objects or constructs used in SQL queries. In the above example, it would have been wise to subclass SQL::Builder::Table for the "users" and "salaries" table. Through these we could encapsulate table name, alias, and write convenience methods. Instead of writing

        $tbl_users->col(name => 'user_id')

one can write

        Users->user_id

Future docs should cover this in more depth.

METHODS

_distinct()

A private method used to get/set the object used for managing the SELECT columns and toggle the DISTINCT keyword. When called with arguments, the value is set and current object ($self) is returned. Otherwise the value (or SQL::Builder::Distinct(3) object by default) is returned. distinct(), sql(), cols(), and distinct_on() rely on this method to act like SQL::Builder::Distinct(3) does.

_having()

A private method used to get/set the object used for manaing the HAVING clause. When called with arguments, the value is set and current object is returned; otherwise the current value (SQL::Builder::Having(3) by default) is returned. having() and sql() rely on this method to be like SQL::Builder::Having(3)

_limit()

A private method used to get/set the object used for manaing the LIMIT/OFFSET clauses. When called with arguments, the value is set and current object is returned; otherwise the current value (SQL::Builder::Limit(3) by default) is returned. limit(), offset() and sql() rely on this method to be like SQL::Builder::Limit(3)

children()

Return a SQL::Builder::Iterator(3) object to iterate over the values returned by _distinct(), tables(), where(), groupby(), _having(), orderby(), and _limit()

cols(@args)

This is a wrapper to _distinct()->cols(@args) which represents the columns used in a SELECT clause. See SQL::Builder::Distinct::cols()

distinct([0|1])

This can be used to turn on/off the DISTINCT keyword in a SELECT statement. When called with arguments, the keyword is turned on and distinct_on() values are effective... See SQL::Builder::Distinct(3). This method is wrapped using the object returned by _distinct()

distinct_on()

This is a wrapper to SQL::Builder::Distinct::on() which maintains a SQL::Builder::List(3) object by default. See SQL::Builder::Distinct(3). The object on which on() is called is obtained through _distinct()

groupby()

Get/set the groupby() object. This probably won't need to be called with arguments, but if it is, the value is set to the first argument and current object is returned; otherwise the current value (a SQL::Builder::GroupBy(3) object by default) is returned. Typical usage is like:

        $select->groupby()->list_push("user_id");

which yields

        GROUP BY user_id

See SQL::Builder::GroupBy(3) and SQL::Builder::Group(3) for more information

having()

This is a wrapper to SQL::Builder::Having::expr() which is populated with an "AND" SQL::Builder::BinaryOp(3) object. The object on which expr() is called is obtained through _having(). Typical usage is:

        $select->having->list_push("foo > 10");
        $select->having->list_push("bar > 20");

which should return something like:

        HAVING foo > 10 AND bar > 20

See SQL::Builder::Having(3) and _having()

joins()

This is a wrapper to SQL::Builder::FromList::joins() which is called on tables(). This is used to add JOINs to the SELECT statement

        $select->joins->list_push("JOIN salaries AS sal USING (salary_id)");

init()

This calls init() on the parent class and sets up the various members with default objects. See the rest of the documentation...

limit()

This is a wrapper to SQL::Builder::Limit::limit(), which is called on _limit()

        $select->limit(10);

yields

        LIMIT 10

See SQL::Builder::Limit(3)

offset()

This is a wrapper to SQL::Builder::Limit::offset(), which is called on _limit()

        $select->offset(10);

yields

        OFFSET 10

See SQL::Builder::Limit(3)

orderby()

Get/set the object used to manage the ORDER BY clause. By default this is populated with a SQL::Builder::GroupBy(3) object, but can be replaced by passing a new value, in which case the current object ($self) is returned. If no no arguments are passed, the current value is returned.

        $select->orderby->list_push("user_id DESC");

yields

        ORDER BY user_id DESC

See SQL::Builder::OrderBy(3) and SQL::Builder::Order(3)

sql()

This method generates the SQL serialization of the object by passing the values of _distinct(), tables(), where(), groupby(), _having(), orderby(), and _limit() through SQL::Builder::Base::dosql() and assembling the results to construct a SELECT statement. SQL::Builder makes few assumptions about what is valid SQL, so expect bad code to generate bad SQL. If no tables are set, this method will throw an exception. The SQL string is returned

tables()

By default this method returns a SQL::Builder::FromList(3) object, but can be replaced by passing an argument; if an argument is passed, the value is set and current object returned - otherwise the current value is returned. Typically this method is used like

        $select->tables->list_push("users");
        $select->tables->joins->list_push("JOIN salaries USING (salary_id)");

which generates something like

        ...
        FROM users JOIN salaries USING (salary_id)
        ...

where()

This is the container for the WHERE clause. By default it's populated with a SQL::Builder::Where(3) object so manipulating the WHERE clause is easy. The WHERE object can be replaced by passing the replacement as an argument, in which case the value is set and current object is returned, otherwise the current value is returned. Typically

        $select->where->list_push("foo > 10")

will generate

        WHERE foo > 10

SEE ALSO

SQL::Builder(3) SQL::Builder::Distinct(3) SQL::Builder::Limit(3) SQL::Builder::GroupBy(3) SQL::Builder::Group(3) SQL::Builder::Having(3) SQL::Builder::Base(3) SQL::Builder::FromList(3) SQL::Builder::Table(3) SQL::Builder::Join(3) SQL::Builder::OrderBy(3) SQL::Builder::Order(3) SQL::Builder::Where(3)