George A. Fitch III > Relations-Query-0.93 > Relations::Query

Download:
Relations-Query-0.93.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.93   Source  

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.

syntax highlighting: