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

FreeBSD Manual Pages

  
 
  

home | help
Query(3)	      User Contributed Perl Documentation	      Query(3)

NAME
       Relations::Query	- Object for building queries with DBI/DBD::mysql

SYNOPSIS
	 # Relations::Query Script that	creates	some queries.

	 use Relations::Query;

	 $query	= new Relations::Query(-select	 => {'fife' => 'barney'},
				       -from	 => {'green_teeth' => 'moogoo'},
				       -where	 => "flotsam>jetsam",
				       -group_by => "denali",
				       -having	 => {'fortune' => 'cookie'},
				       -order_by => ['was','is','will'],
				       -limit	 => '1');

	 $get_query = $query->get();

	 $query->set(-select   => {'clean' => 'sparkle'},
		     -from     => {'lean' => 'book'},
		     -where    => "fighting is between courage and chaos",
		     -limit    => '123');

	 $set_query = $query->get();

	 $get_add_query	= $query->get_add(-select   => {'mean' => 'dog'},
					  -where    => "running	is null",
					  -having   => {'kitties'=> 'on_tv'},
					  -limit    => ['9678']);

	 $query	= to_string({'select' => 'this',
			     'from'   => 'that'});

ABSTRACT
       This perl library uses perl5 objects and	functions to simplify the
       query creation and manipulation process.	It uses	an object orientated
       interface, with the exception of	the to_string()	function, complete
       with functions to manipulate the	query and return the query as a
       string.

       The current version of Relations::Query is available at

	 http://www.gaf3.com

DESCRIPTION
   WHAT	IT DOES
       With Relations::Query you can create a 'select' query by	creating a new
       query object, and passing hashes, arrays, or strings of info to the
       constructor, such as what's within the variables	clause,	what to	order
       by, etc.	 You can also add and override clause info in the query	as
       well, on	both a permanent and temporary basis. With the to_string()
       function, you can create	a query	string from a hash, query object or
       string.

   CALLING RELATIONS::QUERY ROUTINES
       All Relations::Query routines use an ordered, named and hashed argument
       calling style, with the exception of the	to_string() function which
       uses only an ordered argument calling style. This is because some
       routines	have as	many as	seven arguments, and the code is easier	to
       understand given	a named	or hashed argument style, but since some
       people, however,	prefer the ordered argument style because its smaller,
       I'm glad	to do that too.

       If you use the ordered argument calling style, such as

	 $query	= new Relations::Query(['id','label'],'parts');

       the order matters, and you should consult the function defintions later
       in this document	to determine the order to use.

       If you use the named argument calling style, such as

	 $query	= new Relations::Query(-select => ['id','label'],
				       -from   => 'parts');

       the order does not matter, but the names, and minus signs preceeding
       them, do.  You should consult the function defintions later in this
       document	to determine the names to use.

       In the named arugment style, each argument name is preceded by a	dash.
       Neither case nor	order matters in the argument list.  -from, -From, and
       -FROM are all acceptable.  In fact, only	the first argument needs to
       begin with a dash.  If a	dash is	present	in the first argument,
       Relations::Query	assumes	dashes for the subsequent ones.

       If you use the hashed argument calling style, such as

	 $query	= new Relations::Query({select => ['id','label'],
					from   => 'parts'});

       or

	 $query	= new Relations::Query({-select	=> ['id','label'],
					-from	=> 'parts'});

       the order does not matter, but the names, and curly braces do, (minus
       signs are optional). You	should consult the function defintions later
       in this document	to determine the names to use.

       In the hashed arugment style, no	dashes are needed, but they won't
       cause problems if you put them in. Neither case nor order matters in
       the argument list. from,	From, and FROM are all acceptable. If a	hash
       is the first argument, Relations::Query assumes that is the only
       argument	that matters, and ignores any other arguments after the	{}'s.

   QUERY ARGUMENTS
       All of the Relations::Query object functions require arguments to be
       used as different clauses of a "select *	from blah" statements. To be
       as easy and flexible as possible	(In my opinion anyway! :D ), you can
       specify these arguments as a hash, an array or a	string.

       SELECT AND FROM FUNCTIONALITY

       If sent as a hash, a select or from argument will become	a string of
       'field as name' pairs, concatented with a ','.

       For example,

	 $query	= new Relations::Query(-select => {'id'	    => 'parts.part_id',
						   'label'  => "concat(parts.name,' - $	',prices.price)"},
				       -from   => {'parts'  => 'sales.cheap_parts',
						   'prices' => 'stock.all_prices'});

       creates the SQL statment:

	 select	parts.part_id as id,concat(parts.name,'	- $ ',prices.price) as label
	 from sales.cheap_parts	as parts,stock.all_prices as prices

       If sent as an array, a select or	from argument will become a string of
       array members, concatented with a ','.

       For example,

	 $query	= new Relations::Query(-select => ['cheap_parts.part_id',
						   "concat(cheap_parts.name,' -	$ ',all_prices.price) as price"],
				       -from   => ['sales.cheap_parts',
						   'stock.all_prices']);

       creates the SQL statment:

	 select	cheap_parts.part_id,concat(cheap_parts.name,' -	$ ',all_prices.price) as price
	 from sales.cheap_parts,stock.all_prices

       If sent as string, a select or from argument will stay a	string.

       For example,

	 $query	= new Relations::Query(-select => "name",
				       -from   => 'sales.cheap_parts');

       creates the SQL statment:

	 select	name from sales.cheap_parts

       WHERE AND HAVING	FUNCTIONALITY

       If sent as a hash, a where or having argument will become a string of
       'field=value' pairs, concatented	with an	' and '.

       For example,

	 $query	= new Relations::Query(-where  => {'price' => "4.99",
						   'type'  => "'cap'"},
				       -having => {'total' => '100',
						   'cost'  => "19.96"});

       creates the SQL statment:

	 where price=4.99 and type='cap'
	 having	total=100 and cost=19.96

       If sent as an array, a where or having argument will become a string of
       array members, concatented with an ' and	'.

       For example,

	 $query	= new Relations::Query(-where  => ['price > 4.99',
						   "type in ('cap','res','ind')"],
				       -having => ['total between 90 and 100',
						   'cost=19.96']);

       creates the SQL statment:

	 where price > 4.99 and	type in	('cap','res','ind')
	 having	total between 90 and 100 and cost=19.96

       If sent as string, a where or having argument will stay a string.

       For example,

	 $query	= new Relations::Query(-where  => "price > 4.99	or type	in ('cap','res','ind')",
				       -having => "total between 90 and	100 or (cost=19.96 and not total=70)");

       creates the SQL statment:

	 where price > 4.99 or type in ('cap','res','ind')
	 having	total between 90 and 100 or (cost=19.96	and not	total=70)

       GROUP BY, ORDER BY, AND LIMIT FUNCTIONALITY

       If sent as a hash, a group by, order by or limit	argument will become a
       string of 'field_1,field_2' pairs, concatented with a ','. Why did I do
       this? The clause	delimitter is the same for all clauses.	So, this
       behavior	is more	by default than	by design. Keep	in mind	that since a
       hash has	no order, the order of your arguments is not guaranteed.  So,
       it's really not advisable to pass the "order by"	arguments this way.
       You can if you want, but	I will you taunt you for doing so.

       For example,

	 $query	= new Relations::Query(-group_by => {'name'	=> 'color',
						     'category'	=> 'size'},
				       -order_by => {'color'	=> 'size',
						     'name'	=> 'category'},
				       -limit	 => {'30'  => '5'});

       creates the SQL statment:

	 group by name,color,category,size
	 order by color,size,name,category
	 limit 30,5

       or possibly:

	 group by category,size,name,color
	 order by name,category,color,size
	 limit 30,5

       If sent as an array, a group by,	order by or limit argument will	become
       a string	of array members, concatented with a ','.

       For example,

	 $query	= new Relations::Query(-group_by => ['name','color','category','size'],
				       -order_by => ['color','size','name','category'],
				       -limit	 => ['30','5']);

       creates the SQL statment	(without a doubt):

	 group by name,color,category,size
	 order by color,size,name,category
	 limit 30,5

       If sent as string, a group by, order by or limit	argument will stay a
       string.

       For example,

	 $query	= new Relations::Query(-group_by => 'name,color,category,size',
				       -order_by => 'color,size	desc,name,category',
				       -limit	 => '30');

       creates the SQL statment	(without a doubt):

	 group by name,color,category,size
	 order by color,size desc,name,category
	 limit 30

LIST OF	RELATIONS::QUERY FUNCTIONS
       An example of each function is provided in 'test.pl'.

   new
	 $query	= Relations::Query->new($select,$from,$where,$group_by,$having,$order_by,$limit);

	 $query	= new Relations::Query(-select	 => $select,
				       -from	 => $from,
				       -where	 => $where,
				       -group_by => $group_by,
				       -having	 => $having,
				       -order_by => $order_by,
				       -limit	 => $limit);

       Creates creates a new Relations::Query object with each clause stored
       as a string.

   clone
	 $clone	= $query->clone();

       Returns creates a copy of a Relations::Query object.

   get
	 $query_string = $query->get();

       Returns the query in string form.

   add
	 $query->add($select,$from,$where,$group_by,$having,$order_by,$limit);

	 $query->add(-select   => $select,
		     -from     => $from,
		     -where    => $where,
		     -group_by => $group_by,
		     -having   => $having,
		     -order_by => $order_by,
		     -limit    => $limit);

       Adds more info to the query object. If the clause to be added to	is
       already set, add() concatenates the new clause onto to current one with
       the appropriate delimitter. If the clause to be added to	is not already
       set, add() sets that clause to the new one.

   set
	 $query->set($select,$from,$where,$group_by,$having,$order_by,$limit);

	 $query->set(-select   => $select,
		     -from     => $from,
		     -where    => $where,
		     -group_by => $group_by,
		     -having   => $having,
		     -order_by => $order_by,
		     -limit    => $limit);

       Sets (overwrites) info to the query object. Only	the clauses specified
       will be over written.

   get_add
	 $query->get_add($select,$from,$where,$group_by,$having,$order_by,$limit);

	 $query->get_add(-select   => $select,
			 -from	   => $from,
			 -where	   => $where,
			 -group_by => $group_by,
			 -having   => $having,
			 -order_by => $order_by,
			 -limit	   => $limit);

       Returns the query, plus whatever's to be	added, in string form. The
       query object is not added to, but the string is returned	with the info
       added to	the specified clauses.

   get_set
	 $query->get_set($select,$from,$where,$group_by,$having,$order_by,$limit);

	 $query->get_set(-select   => $select,
			 -from	   => $from,
			 -where	   => $where,
			 -group_by => $group_by,
			 -having   => $having,
			 -order_by => $order_by,
			 -limit	   => $limit);

       Returns the query, plus whatever's to be	set, in	string form. The query
       object is not over written, but the string is returned with the info
       over written in the specified clauses.

   to_string
	 $string = to_string('select this from that');

	 $string = to_string({'select' => 'this',
			      'from'   => 'that'});

	 $string = to_string({-select => 'this',
			      -from   => 'that'});

	 $string = to_string(Relations::Query->new(-select => 'this',
						   -from   => 'that'));

       Returns a query in string form from the arguments sent. It may seem a
       little silly, but Relations::Abstract relies heavily on this function.
       All the examples	above set string equal to 'select this from that'.

   to_text
	 $text = $query->to_text($string,$current);

       Returns a text representation of	a query. Useful	for debugging
       purposes. It takes a a string to	use for	indenting, $string, and	the
       current number of indents, $current.

LIST OF	RELATIONS::QUERY PROPERTIES
   select
       The select part of the query in string form (without the	word
       'select').

   from
       The from	part of	the query in string form (without the word 'from').

   where
       The where part of the query in string form (without the word 'where').

   group_by
       The group by part of the	query in string	form (without the words	'group
       by').

   having
       The having part of the query in string form (without the	word
       'having').

   order_by
       The order by part of the	query in string	form (without the words	'order
       by').

   limit
       The limit part of the query in string form (without the word 'limit').

OTHER RELATED WORK
   Relations (Perl)
       Contains	functions for dealing with databases. It's mainly used as the
       foundation for the other	Relations modules. It may be useful for	people
       that deal with databases	as well.

   Relations-Query (Perl)
       An object oriented form of a SQL	select query. Takes hashes.  arrays,
       or strings for different	clauses	(select,where,limit) and creates a
       string for each clause. Also allows users to add	to existing clauses.
       Returns a string	which can then be sent to a database.

   Relations-Abstract (Perl)
       Meant to	save development time and code space. It takes the most	common
       (in my experience) collection of	calls to a MySQL database, and changes
       them to one liner calls to an object.

   Relations-Admin (PHP)
       Some generalized	objects	for creating Web interfaces to relational
       databases. Allows users to insert, select, update, and delete records
       from different tables. It has functionality to use tables as lookup
       values for records in other tables.

   Relations-Family (Perl)
       Query engine for	relational databases.  It queries members from any
       table in	a relational database using members selected from any other
       tables in the relational	database. This is especially useful with
       complex databases: databases with many tables and many connections
       between tables.

   Relations-Display (Perl)
       Module creating graphs from database queries. It	takes in a query
       through a Relations-Query object, along with information	pertaining to
       which field values from the query results are to	be used	in creating
       the graph title,	x axis label and titles, legend	label (not used	on the
       graph) and titles, and y	axis data. Returns a graph and/or table	built
       from from the query.

   Relations-Report (Perl)
       An Web interface	for Relations-Family, Reations-Query, and Relations-
       Display.	 It creates complex (too complex?) web pages for selecting
       from the	different tables in a Relations-Family object. It also has
       controls	for specifying the grouping and	ordering of data with a
       Relations-Query object, which is	also based on selections in the
       Relations-Family	object.	That Relations-Query can then be passed	to a
       Relations-Display object, and a graph and/or table will be displayed.

perl v5.32.0			  2001-11-12			      Query(3)

NAME | SYNOPSIS | ABSTRACT | DESCRIPTION | LIST OF RELATIONS::QUERY FUNCTIONS | LIST OF RELATIONS::QUERY PROPERTIES | OTHER RELATED WORK

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

home | help