2
2
# This program is distributed under the terms of the
3
3
# GNU General Public License, version 2.
5
# $Id: DBI.pm 1174 2008-01-08 21:02:50Z bchoate $
5
# $Id: DBI.pm 2548 2008-06-11 00:07:58Z bchoate $
7
7
package MT::ObjectDriver::Driver::DBI;
100
117
$driver->_do_group_by("AVG($avg_column) AS avg_$avg_column", @_);
122
my ($class, $terms, $args) = @_;
124
my $max_column = delete $args->{max};
125
return unless $max_column;
126
$max_column = $driver->_decorate_column_name($class, $max_column);
127
$args->{sort} = "max_$max_column" unless exists $args->{sort};
128
$args->{direction} = 'descend' unless exists $args->{direction};
129
$driver->_do_group_by("MAX($max_column) AS max_$max_column", @_);
103
132
sub _do_group_by {
104
133
my $driver = shift;
105
134
my ($agg_func, $class, $terms, $args) = @_;
106
135
my $props = $class->properties;
107
if ($props->{class_type}) {
108
my $class_col = $props->{class_column};
109
unless ($terms->{$class_col}) {
110
$terms->{$class_col} = $class->class_type;
113
if ($args->{no_class}) {
114
delete $terms->{$props->{class_column}};
115
delete $args->{no_class};
117
my $order = delete $args->{sort};
136
$terms ||= {}; $args ||= {}; # declare these for pre_search to work
137
$class->call_trigger('pre_search', $terms, $args);
138
my $order = delete $args->{sort} || '';
118
139
my $direction = delete $args->{direction};
140
if ( $order =~ /\sdesc|asc/i ) {
142
while ($order =~ /(?:\s*([\w\s\(\)]+?)\s(desc|asc))/ig) {
143
push @new_order, { column => $1, desc => $2 };
145
$order = \@new_order if @new_order;
119
147
my $limit = exists $args->{limit} ? delete $args->{limit} : undef;
120
148
my $offset = exists $args->{offset} ? delete $args->{offset} : undef;
121
149
my $stmt = $driver->prepare_statement($class, $terms, $args);
140
168
$stmt->group([ map { { column => $_ } } @group ]);
170
## Set statement's ORDER clause if any.
173
$stmt->order( [ { column => $decorate->($order),
174
desc => ($direction || '') eq 'descend' ? 'DESC' : 'ASC'
178
foreach my $ord (@$order) {
180
column => $decorate->($ord->{column}),
181
desc => $ord->{desc},
184
$stmt->order(\@order);
143
188
my $sql = $stmt->as_sql;
145
$sql .= "\nORDER BY " . $decorate->($order);
147
$sql .= $direction eq 'descend' ? ' DESC' : ' ASC';
151
190
my $dbh = $driver->r_handle;
152
191
$driver->start_query($sql, $stmt->bind);
153
my $sth = $dbh->prepare_cached($sql) or die $sql;
154
$sth->execute(@{ $stmt->bind }) or die $sql;
192
my $sth = $dbh->prepare_cached($sql);
193
$sth->execute(@{ $stmt->bind });
157
196
for (@{ $args->{group} }) {
196
242
## Convert $terms and $args like we would for a search.
198
244
if (ref($orig_terms) eq 'HASH') {
199
245
$terms = { %$orig_terms };
200
246
} elsif (ref($orig_terms) eq 'ARRAY') {
201
247
$terms = [ @$orig_terms ];
203
my $args = $orig_args ? { %$orig_args } : undef;
249
my $args = $orig_args ? { %$orig_args } : {};
204
250
$class->call_trigger('pre_search', $terms, $args);
206
252
my $stmt = $driver->prepare_statement($class, $terms, $args);
326
372
$stmt->add_select($dbcol => $col);
329
$stmt->from([ $tbl ]);
375
if ( my $alias = $orig_args->{alias} ) {
376
$stmt->from([ "$tbl $alias" ]);
379
$stmt->from([ $tbl ]);
331
382
if (defined($terms)) {
332
383
$stmt->column_mutator(sub {
334
return $dbd->db_column_name($tbl, $col);
385
my $db_col = $dbd->db_column_name($tbl, $col);
386
if ( my $alias = $orig_args->{alias} ) {
387
$db_col = "$alias.$db_col";
336
391
if (ref $terms eq 'ARRAY') {
337
392
$stmt->add_complex_where($terms);
347
402
## Set statement's ORDER clause if any.
348
403
if ($args->{sort} || $args->{direction}) {
349
404
my $order = $args->{sort} || 'id';
350
my $dir = $args->{direction} &&
351
$args->{direction} eq 'descend' ? 'DESC' : 'ASC';
353
column => $dbd->db_column_name($tbl, $order),
406
my $dir = $args->{direction} &&
407
$args->{direction} eq 'descend' ? 'DESC' : 'ASC';
409
column => $dbd->db_column_name($tbl, $order),
414
foreach my $ord (@$order) {
416
column => $dbd->db_column_name($tbl, $ord->{column}),
417
desc => $ord->{desc},
420
$stmt->order(\@order);
424
if ( my $ft_arg = delete $args->{'freetext'} ) {
425
my @columns = map { $dbd->db_column_name($tbl, $_) } @{ $ft_arg->{'columns'} };
426
$stmt->add_freetext_where( \@columns, $ft_arg->{'search_string'} );
358
429
$stmt->limit($args->{limit}) if $args->{limit};
432
503
if ($start_val) {
433
504
## TODO: support complex primary keys
434
505
my $col = $args->{sort} || $class->primary_key;
435
$col = $driver->_decorate_column_name($class, $col);
506
if (ref $col eq 'ARRAY') {
507
if (ref $col->[0] eq 'HASH') {
508
# complex 'sort' array/hash structure
510
$_->{column} = $driver->_decorate_column_name($class, $_->{column});
513
# primary key as array of column names
515
$_ = $driver->_decorate_column_name($class, $_);
519
$col = $driver->_decorate_column_name($class, $col);
436
521
my $op = $args->{direction} eq 'descend' ? '<' : '>';
437
522
$stmt->add_where($col, { value => $start_val, op => $op });