$self->messagec( 5, "There are {green}", $el->length, "{/} elements for this query.");
my$types= $q->binded_types_as_param;
$self->messagec( 5, "Using {green}", $el->types->length, "{/} binded types from the query object ($q) -> ", sub{ $self->Module::Generic::dump( $types) } );
@binded_types= @$types;
}
if( defined( $el) )
{
$self->messagec( 5, "{green}", $el->elements->length, "{/} elements set and {green}", scalar( @binded_types), "{/} types set so far with \$q->binded_types_as_param.");
warn( sprintf( "Warning: total %d bound values does not match the total %d bound types ('%s')! Check the code for query $self->{sth}->{Statement}\n", scalar( @binded), scalar( @binded_types), CORE::join( "','", @binded_types) ) );
# Cancel it, because it will create problems
@binded_types= ();
}
# If there are any array object of some sort provided, make sure they are transformed into a regular array so DBD::Ph can then transform it into a Postgres array.
my$q= $self->query_object || return( $self->error( "No query formatter object was set") );
my$tbl_o= $q->table_object || return( $self->error( "No table object is set in query object.") );
my$query= $q->query ||
return( $self->error( "No query prepared for join with another table.") );
if( $query!~ /^[[:blank:]]*SELECT[[:blank:]]+/i )
{
return( $self->error( "You may not perform a join on a query other than select.") );
}
my$constant= $q->constant;
# Constant is set and query object marked as final, which means this statement has already been processed as a join and so we skip all further processing.
if( scalar( keys( %$constant) ) && $q->final )
{
return( $self);
}
my$table= $tbl_o->table;
my$db= $tbl_o->database;
my$multi_db= $tbl_o->prefix_database;
my$alias= $tbl_o->as;
my$new_fields= '';
my$new_table= '';
my$new_db= '';
my$class= ref( $self);
my$q_source= $q->clone;
my$q_target;
# On the duplicated table object, add the current table in the join
$self->messagec( 5, "Merging the where clause of target table {green}", $q_target->table_object->name, "{/} with source table {green}", $q_source->table_object->name, "{/}");
$self->messagec( 5, "Souce table has no where clause. Setting the where clause of target table {green}", $q_target->table_object->name, "{/} to that of the source table {green}", $q_source->table_object->name, "{/}");
$q_source->where( $q_target->where );
}
if( my$group_target= $q_target->group )
{
$self->messagec( 5, "Adding group clause clause of the target table {green}", $q_target->table_object->name, "{/} to the source table {green}", $q_source->table_object->name, "{/} -> ", ( $group_target->value->length? 'yes': 'no, nothing to set') );
$self->messagec( 5, "Adding order clause clause of the target table {green}", $q_target->table_object->name, "{/} to the source table {green}", $q_source->table_object->name, "{/} -> ", ( $order_target->value->length? 'yes': 'no, nothing to set') );
$self->messagec( 5, "format_condition(): Value '", overload::StrVal( $vals->[0] ), "' is a DB::Object::Operator object with operator name {green}${sub_obj}{/} and values -> ", sub{ $self->Module::Generic::dump( \@sub_vals) } );
$self->messagec( 5, "format_condition(): \$f1 is a field object ({green}", $f1->name, "{/}). Adding its related table {green}", $f1->table, "{/} as known.");
$self->messagec( 5, "format_condition(): \$f2 is a field object ({green}", $f2->name, "{/}). Adding its related table {green}", $f2->table, "{/} as known.");
my@query= ( "SELECT ${fields} FROM ${from} ${left_join}");
push( @query, @$clause) if( @$clause);
my$statement= CORE::join( ' ', @query);
$q_source->query( $statement);
my$sth= $tbl_o->_cache_this( $q_source) ||
return( $self->error( "Error while preparing query to select:\n", $q_source->as_string(), $tbl_o->error ) );
# Routines such as as_string() expect an array on pupose so we do not have to commit the action
# but rather get the statement string. At the end, we write:
# $obj->select() to really select
# $obj->select->as_string() to ONLY get the formatted statement
# wantarray() returns the undefined value in void context, which is typical use of a real select command
# i.e. $obj->select();
if( !defined( wantarray() ) )
{
$sth->execute() ||
return( $self->error( "Error while executing query to select:\n", $q_source->as_string(), "\nError: ", $sth->error() ) );
}
return( $sth);
}
subobject
{
my$self= shift( @_);
# This is intended for statement to fetched their object:
# my $obj = $table->select( '*' )->object();
# my $obj = $table->select( '*' )
# would merly execute the statement before returning its object, but there are conditions
# such like using a SELECT to create a table where we do not want the statement to be executed already
return( $self->{sth} ) if( $self->{sth} );
# More sensible approach will return a special Module::Generic::Null object to avoid perl complaining of 'called on undef value' if this is used in chaining
This is the statement object package from which other driver specific packages inherit from.
=head1 METHODS
=head2 as_string
Returns the current statement object as a string.
=head2 bind_param
Provided with a list of arguments and they will be passed to L<DBI/bind_param>
If an error occurred, an error is returned, otherwise the return value of calling C<bind_param> is returned.
=head2 commit
If the statement parameter I<autocommit> is true, a C<COMMIT> statement will be prepared and executed.
The current object is returned.
=head2 database_object
Sets or gets the current database object.
=head2 distinct
Assuming a I<query> object property has already been set previously, this will add the C<DISTINCT> keyword to it if not already set.
If L</distinct> is called in void context, the query is executed immediately.
The query statement is returned.
=head2 dump
Provided with a file and this will print on STDOUT the columns used, separated by a tab and then will process each rows fetched with L<DBI::fetchrow> and will join the column valus with a tab before printing it out to STDOUT.
If binded values have been prepared, they are applied here before executing the query.
Sometime, you need to clearly specify what the datatype are for the value provided with C<execute>, because L<DB::Object::Query> could not figure it out.
Thus, if you do:
$tbl->where(
$tbl->fo->name == '?'
);
L<DB::Object::Query> knows the datatype, because you are using a field object (C<fo>), but if you were doing:
$tbl->where(
'?' == $dbh->ANY( $tbl->fo->alias )
);
In this scenario, L<DB::Object::Query> does not know what the bind value would be, although we could venture a guess by looking at the right-hand side, but this is a bit hazardous. So you are left with a placeholder, but no datatype. So you would execute like:
$sth->execute({ $val => 'varchar' });
If the total number of binded values does not match the total number of binded type, this will trigger a warning.
L<DBI/execute> will be called with the binded values and if this method was called in an object context, the current object is returned, otherwise the returned value from L<DBI/execute> is returned.
With the version C<0.5.0> of this module, this method is more able to find out the data type of the table field. To achieve this, it uses the L<field object|DB::Object::Fields::Field> set in each L<element object|DB::Object::Query::Element>. Those element objects are instantiated upon C<insert> or C<update> query.
Also, if you provide a value during an C<insert> or C<update> for a field that the database expects an array, this method will automatically convert it into an array.
Likewise, if the table field is of type C<json> or C<jsonb> and an hash reference value is provided, this method will encode the hash reference into a C<JSON> string.
=head2 executed
Returns true if this statement has already been executed, and false otherwise.
=head2 fetchall_arrayref
Similar to L<DBI/fetchall_arrayref>, this will execute the query and return an array reference of data.
=head2 fetchcol
Provided with an integer that represents a column number, starting from 0, and this will get each row of results and add the value for the column at the given offset.
it returns a list of those column value fetched.
=head2 fetchhash
This will retrieve an hash reference for the given row and return it as a regular hash.
=head2 fetchrow
This will retrieve the data from database using L</fetchrow_arrayref> and return the list of data as array in list context, or the first entry of the array in scalar context.
=head2 fetchrow_hashref
This will retrieve the data from the database as an hash reference.
It will convert any data from json to hash reference if L<DB::Object/auto_decode_json> is set to true.
it will also convert any datetime data into a L<DateTime> object if L<DB::Object/auto_convert_datetime_to_object> is true.
It returns the hash reference retrieved.
=head2 fetchrow_object
This will create dynamically a package named C<DB::Object::Postgres::Result::SomeTable> for example and load the hash reference retrieved from the database into this dynamically created packackage.
It returns the object thus created.
=head2 finish
Calls L<DBI/finish> and return the returned value, or an error if an error occurred.
=head2 ignore
This will change the query prepared and add the keyword C<IGNORE>.
If called in void context, this will execute the resulting statement handler immediately.
=head2 join
Provided with a target and an hash reference, or list or array reference of condition for the join and this will prepare the join statement.
If the original query is not of type C<select>, this will trigger an error.
The target mentioned above can be either a L<DB::Object::Statement> object, or a table object (L<DB::Object::Tables>), or even just a string representing the name of a table.
$tbl->select->join( $sth );
$tbl->select->join( $other_tbl );
$tbl->select->join( 'table_name' );
The condition mentioned above can be a L<DB::Object::Operator> (C<AND>, C<OR> or C<NOT>), in which case the actual condition will be taken from that operator embedded value.
The condition can also be a L<DB::Object::Fields::Overloaded> object, which implies a table field with some operator and some value.
The condition can also be an hash reference with each key being a table name to join and each value an hash reference of condition for that particular join with each key being a column name and each value the value of the join for that column.
my $tbl = $dbh->first_table;
$tbl->select->join({
other_table =>
{
id => 'first_table.id',
user => 'first_table.user',
},
yet_another_table =>
{
id => 'other_table.id',
},
});
would become something like:
SELECT *
FROM first_table
LEFT JOIN other_table ON
first_table.id = id AND
first_table.user = user
LEFT JOIN yet_another_table ON
other_table.id = id
Each condition will be formatted assuming an C<AND> expression, so this is less flexible than using operator objects and table field objects.
If no condition is provided, this is taken to be a straight join.
$tbl->where( $tbl->fo->id == 2 );
$other_tbl->where( $other_tbl->fo->user 'john' );
$tbl->select->join( $other_tbl );
Would become something like:
SELECT *
FROM first_table, other_table
WHERE id = 2 AND user = 'john'
If called in void context, this will execute the resulting statement handler immediately.
It returns the resulting statement handler.
It returns the statement handler.
=head2 object
Returns the statement object explicitly.
my $sth = $tbl->select->object;
which is really equivalent to:
my $sth = $tbl->select;
=head2 priority
Provided with a priority integer that can be 0 or 1 with 0 being C<LOW_PRIORITY> and 1 being C<HIGH_PRIORITY> and this will adjust the query formatted to add the priority. This works only on Mysql drive though.
If used on queries other than C<DELETE>, C<INSERT>, C<REPLACE>, C<SELECT>, C<UPDATE> an error will be returned.
If called in void context, this will execute the newly create statement handler immediately.
It returns the newly create statement handler.
=head2 promise
This the same as calling L</execute>, except that the query will be executed asynchronously and a L<Promise::Me> object will be returned, so you can do asynchronous queries like this: