NAME

Fey::SQL::Select - Represents a SELECT query

VERSION

version 0.42

SYNOPSIS

  my $sql = Fey::SQL->new_select();

  # SELECT Part.part_id, Part.part_name
  #   FROM Part JOIN MachinePart
  #        ON Part.part_id = MachinePart.part_id
  #  WHERE MachinePart.machine_id = $value
  # ORDER BY Part.name DESC
  # LIMIT 10
  $sql->select( $part_id, $part_name );
  $sql->from( $Part, $MachinePart );
  $sql->where( $machine_id, '=', $value );
  $sql->order_by( $part_Name, 'DESC' );
  $sql->limit(10);

  print $sql->sql($dbh);

DESCRIPTION

This class represents a SELECT query.

METHODS

This class provides the following methods:

Constructor

To construct an object of this class, call $query->select() on a Fey::SQL object.

$select->select(...)

This method accepts a list of parameters, which are the things being selected.

The list can include the following types of elements:

• plain scalars, including undef

  These will be passed to Fey::Literal->new_from_scalar().

• Fey::Table objects

  If a table is passed, then all of its columns will be included in the SELECT clause.

• Fey::Column objects, and aliases

  This specifies an individual column (possibly aliased) to include in the select.

  The $column->is_selectable() method must return true for these objects.

  This method can be called multiple times with different elements each time.

• Fey::Literal objects

  Any type of literal can be included in a SELECT clause.

$select->distinct()

If this is called, the generated SQL will start with SELECT DISTINCT. You cannot call both $select->distinct() and $select->distinct_on() for the same query.

$select->distinct_on($column)

If this is called, the generated SQL will start with SELECT DISTINCT ON (Table.column). You cannot call both $select->distinct() and $select->distinct_on() for the same query.

$select->from(...)

This method specifies the FROM clause of the query. It can accept a variety of argument lists.

• ($table_or_alias)

  If called with a single Fey::Table or table alias object, that table is included in the FROM clause.

    FROM Part
  
    FROM Part as Part0

• ($select_query)

  If called with a single Fey::SQL::Select object, that object's SQL will be included in the FROM clause as a subselect.

    FROM (SELECT part_id FROM Part) AS SUBSELECT0

• ($table1, $table2)

  If two tables (or aliases) are passed to this method, these two tables are included and joined together. The foreign key between these two tables will be looked up in the Fey::Schema object for the tables. However, if the tables do not have a foreign key between them, or have more than one foreign key, an exception is thrown.

    FROM Part, MachinePart
         ON Part.part_id = MachinePart.part_id

• ($table1, $table2, $fk)

  When joining two tables, you can manually specify the foreign key which should be used to join them. This is necessary when there are multiple foreign keys between two tables.

  You can also use this to "fake" a foreign key between two tables which don't really have one, but where it makes sense to join them anyway. If this paragraph doesn't make sense, don't worry about it ;)

• ($table1, $table2, $where_clause)

• ($table1, $table2, $fk, $where_clause)

  If you want to specify a WHERE clause as part of an inner join, include this as the last argument when calling $select->from().

  To create a standalone WHERE clause suitable for passing to this method, use the Fey::SQL::Where class.

• ($table1, 'left', $table2)

  If you want to do an outer join between two tables, pass the two tables, separated by one of the following string:

  • left

  • right

  • full

  This will generate the appropriate outer join SQL in the FROM clause.

    FROM Part
         LEFT OUTER JOIN MachinePart
         ON Part.part_id = MachinePart.part_id

  Just as with a normal join, the <$select-from() >> will attempt to automatically find a foreign key between the two tables.

• ($table1, 'left', $table2, $fk)

  Just as with a normal join, you can manually specify the foreign key to use for an outer join as well.

• ($table1, 'left', $table2, $where_clause)

  If you want to specify a WHERE clause as part of an outer join, include this as the fourth argument when calling $select->from().

    FROM Part
         LEFT OUTER JOIN MachinePart
         ON Part.part_id = MachinePart.part_id
         AND MachinePart.machine_id = ?

  To create a standalone WHERE clause suitable for passing to this method, use the Fey::SQL::Where class.

• ($table1, 'left', $table2, $fk, $where_clause)

  You can manually specify a foreign key and include a where clause in an outer join.

The $select->from() method can be called multiple times with different join options. If you call the method with arguments that it has already seen, then it will effectively ignore that call.

$select->where(...)

See the Fey::SQL section on WHERE Clauses for more details.

$select->group_by(...)

This method accepts a list of elements. Each element can be a Fey::Column object, a column alias, or a literal function or term.

$select->having(...)

The $select->having() method accepts exactly the same arguments as the $select->where() method.

$select->order_by(...)

See the Fey::SQL section on ORDER BY Clauses for more details.

$select->limit(...)

See the Fey::SQL section on LIMIT Clauses for more details.

$select->sql($dbh)

Returns the full SQL statement which this object represents. A DBI handle must be passed so that identifiers can be properly quoted.

$select->bind_params()

See the Fey::SQL section on Bind Parameters for more details.

$select->select_clause_elements

Returns a list of objects, one for each element in the SELECT clause. These can be Fey::Columns, Fey::Column::Aliases, or any type of Fey::Literal.

These items are returned in the order in which they will be included in the SELECT clause.

$select->select_clause()

Returns the SELECT clause portion of the SQL statement as a string.

$select->from_clause()

Returns the FROM clause portion of the SQL statement as a string.

$select->where_clause()

Returns the WHERE clause portion of the SQL statement as a string.

$select->order_by_clause()

Returns the ORDER BY clause portion of the SQL statement as a string.

$select->group_by_clause()

Returns the GROUP BY clause portion of the SQL statement as a string.

$select->having_clause()

Returns the HAVING clause portion of the SQL statement as a string.

$select->limit_clause()

Returns the LIMIT clause portion of the SQL statement as a string.

$select->sql_or_alias()

$select->sql_with_alias()

Returns the appropriate SQL snippet. See Fey::Role::HasAliasName.

$select->id()

Returns a unique identifier for the select.

ROLES

• Fey::Role::SQL::HasBindParams

• Fey::Role::SQL::HasWhereClause

• Fey::Role::SQL::HasOrderByClause

• Fey::Role::SQL::HasLimitClause

• Fey::Role::SQL::HasAliasName

• Fey::Role::SQL::Comparable

  This role allows a Fey::SQL::Select object to be used as a subselect in WHERE clauses.

• Fey::Role::SQL::Cloneable

BUGS

See Fey for details on how to report bugs.

AUTHOR

Dave Rolsky <autarch@urth.org>

COPYRIGHT AND LICENSE

This software is Copyright (c) 2011 - 2015 by Dave Rolsky.

This is free software, licensed under:

  The Artistic License 2.0 (GPL Compatible)