Relations::Query - Object for building queries with DBI/DBD::mysql
# 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'});
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
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.
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.
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 ','.
$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']);
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.
$query = new Relations::Query(-select => "name", -from => 'sales.cheap_parts');
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 '.
$query = new Relations::Query(-where => {'price' => "4.99", 'type' => "'cap'"}, -having => {'total' => '100', 'cost' => "19.96"});
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 '.
$query = new Relations::Query(-where => ['price > 4.99', "type in ('cap','res','ind')"], -having => ['total between 90 and 100', 'cost=19.96']);
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.
$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)");
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.
$query = new Relations::Query(-group_by => {'name' => 'color', 'category' => 'size'}, -order_by => {'color' => 'size', 'name' => 'category'}, -limit => {'30' => '5'});
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 ','.
$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):
If sent as string, a group by, order by or limit argument will stay a string.
$query = new Relations::Query(-group_by => 'name,color,category,size', -order_by => 'color,size desc,name,category', -limit => '30');
group by name,color,category,size order by color,size desc,name,category limit 30
An example of each function is provided in 'test.pl'.
$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 = $query->clone();
Returns creates a copy of a Relations::Query object.
$query_string = $query->get();
Returns the query in string form.
$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.
$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.
$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.
$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.
$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'.
$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.
The select part of the query in string form (without the word 'select').
The from part of the query in string form (without the word 'from').
The where part of the query in string form (without the word 'where').
The group by part of the query in string form (without the words 'group by').
The having part of the query in string form (without the word 'having').
The order by part of the query in string form (without the words 'order by').
The limit part of the query in string form (without the word 'limit').
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.
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.
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.
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.
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.
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.
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.
To install Relations::Query, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Relations::Query
CPAN shell
perl -MCPAN -e shell install Relations::Query
For more information on module installation, please visit the detailed CPAN module installation guide.