Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SQL::Maker::Select(3) User Contributed Perl DocumentationSQL::Maker::Select(3)

NAME
       SQL::Maker::Select - dynamic SQL	generator

SYNOPSIS
	   my $sql = SQL::Maker::Select->new()
					 ->add_select('foo')
					 ->add_select('bar')
					 ->add_select('baz')
					 ->add_from('table_name')
					 ->as_sql;
	   # =>	"SELECT	foo, bar, baz FROM table_name"

DESCRIPTION
METHODS
       "my $sql	= $stmt->as_sql();"
	   Render the SQL string.

       "my @bind = $stmt->bind();"
	   Get the bind	variables.

       "$stmt->add_select('*')"
       "$stmt->add_select($col => $alias)"
       "$stmt->add_select(\'COUNT(*)' => 'cnt')"
	   Add a new select term. It's automatically quoted.

       "$stmt->add_from($table :Str | $select :SQL::Maker::Select) :
       SQL::Maker::Select"
	   Add a new FROM clause. You can specify the table name or an
	   instance of SQL::Maker::Select for a	sub-query.

	   Return: $stmt itself.

       "$stmt->add_join(user =>	{type => 'inner', table	=> 'config', condition
       => 'user.user_id	= config.user_id'});"
       "$stmt->add_join(user =>	{type => 'inner', table	=> 'config', condition
       => {'user.user_id' => 'config.user_id'});"
       "$stmt->add_join(user =>	{type => 'inner', table	=> 'config', condition
       => ['user_id']});"
	   Add a new JOIN clause. If you pass an arrayref for 'condition' then
	   it uses 'USING'. If 'type' is omitted it falls back to plain	JOIN.

	       my $stmt	= SQL::Maker::Select->new();
	       $stmt->add_join(
		   user	=> {
		       type	 => 'inner',
		       table	 => 'config',
		       condition => 'user.user_id = config.user_id',
		   }
	       );
	       $stmt->as_sql();
	       # => 'FROM user INNER JOIN config ON user.user_id = config.user_id'

	       my $stmt	= SQL::Maker::Select->new(quote_char =>	'`', name_sep => '.');
	       $stmt->add_join(
		   user	=> {
		       type	 => 'inner',
		       table	 => 'config',
		       condition => {'user.user_id' => 'config.user_id'},
		   }
	       );
	       $stmt->as_sql();
	       # => 'FROM `user` INNER JOIN `config` ON	`user`.`user_id` = `config`.`user_id`'

	       my $stmt	= SQL::Maker::Select->new();
	       $stmt->add_select('name');
	       $stmt->add_join(
		   user	=> {
		       type	 => 'inner',
		       table	 => 'config',
		       condition => ['user_id'],
		   }
	       );
	       $stmt->as_sql();
	       # => 'SELECT name FROM user INNER JOIN config USING (user_id)'

	       my $subquery = SQL::Maker::Select->new();
	       $subquery->add_select('*');
	       $subquery->add_from( 'foo' );
	       $subquery->add_where( 'hoge' => 'fuga' );
	       my $stmt	= SQL::Maker::Select->new();
	       $stmt->add_join(
		   [ $subquery,	'bar' ]	=> {
		       type	 => 'inner',
		       table	 => 'baz',
		       alias	 => 'b1',
		       condition => 'bar.baz_id	= b1.baz_id'
		   },
	       );
	       $stmt->as_sql;
	       # => "FROM (SELECT * FROM foo WHERE (hoge = ?)) bar INNER JOIN baz b1 ON	bar.baz_id = b1.baz_id";

       "$stmt->add_index_hint(foo => {type => 'USE', list =>
       ['index_hint']});"
       "$stmt->add_index_hint(foo => 'index_hint');"
       "$stmt->add_index_hint(foo => ['index_hint']);"
	       my $stmt	= SQL::Maker::Select->new();
	       $stmt->add_select('name');
	       $stmt->add_from('user');
	       $stmt->add_index_hint(user => {type => 'USE', list => ['index_hint']});
	       $stmt->as_sql();
	       # => "SELECT name FROM user USE INDEX (index_hint)"

       "$stmt->add_where('foo_id' => 'bar');"
	   Add a new WHERE clause.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->add_where('name' => 'john')
					      ->add_where('type' => {IN	=> [qw/1 2 3/]})
					      ->as_sql();
	       # => "SELECT c FROM foo WHERE (name = ?)	AND (type IN (?, ?, ?))"

       "$stmt->add_where_raw('id = ?', [1])"
	   Add a new WHERE clause from raw placeholder string and bind
	   variables.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->add_where_raw('EXISTS(SELECT * FROM bar	WHERE name = ?)' => ['john'])
					      ->add_where_raw('type IS NOT NULL')
					      ->as_sql();
	       # => "SELECT c FROM foo WHERE (EXISTS(SELECT * FROM bar WHERE name = ?))	AND (type IS NOT NULL)"

       "$stmt->set_where($condition)"
	   Set the WHERE clause.

	   $condition should be	instance of SQL::Maker::Condition.

	       my $cond1 = SQL::Maker::Condition->new()
						  ->add("name" => "john");
	       my $cond2 = SQL::Maker::Condition->new()
						  ->add("type" => {IN => [qw/1 2 3/]});
	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->set_where($cond1 & $cond2)
					      ->as_sql();
	       # => "SELECT c FROM foo WHERE ((name = ?)) AND ((type IN	(?, ?, ?)))"

       "$stmt->add_order_by('foo');"
       "$stmt->add_order_by({'foo' => 'DESC'});"
	   Add a new ORDER BY clause.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->add_order_by('name' => 'DESC')
					      ->add_order_by('id')
					      ->as_sql();
	       # => "SELECT c FROM foo ORDER BY	name DESC, id"

       "$stmt->add_group_by('foo');"
	   Add a new GROUP BY clause.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->add_group_by('id')
					      ->as_sql();
	       # => "SELECT c FROM foo GROUP BY	id"

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->add_group_by('id' => 'DESC')
					      ->as_sql();
	       # => "SELECT c FROM foo GROUP BY	id DESC"

       "$stmt->limit(30)"
       "$stmt->offset(5)"
	   Add LIMIT and OFFSET.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_select('c')
					      ->add_from('foo')
					      ->limit(30)
					      ->offset(5)
					      ->as_sql();
	       # => "SELECT c FROM foo LIMIT 30	OFFSET 5"

       "$stmt->add_having(cnt => 2)"
	   Add a HAVING	clause.

	       my $stmt	= SQL::Maker::Select->new()
					      ->add_from('foo')
					      ->add_select(\'COUNT(*)' => 'cnt')
					      ->add_having(cnt => 2)
					      ->as_sql();
	       # => "SELECT COUNT(*) AS	cnt FROM foo HAVING (COUNT(*) =	?)"

SEE ALSO
       Data::ObjectDriver::SQL

perl v5.32.0			  2014-12-22		 SQL::Maker::Select(3)

NAME | SYNOPSIS | DESCRIPTION | METHODS | SEE ALSO

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SQL::Maker::Select&sektion=3&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help