Skip to content

Instantly share code, notes, and snippets.

@mrenvoize

mrenvoize/Result::Report.pm

Last active June 9, 2016 16:01
Show Gist options
  • Save mrenvoize/6e8aba1a8298ffb9b9fa23faea7fafb2 to your computer and use it in GitHub Desktop.
Save mrenvoize/6e8aba1a8298ffb9b9fa23faea7fafb2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
correct.sql
SELECT "me"."id", "me"."name", "me"."updated", "me"."creator_id", "me"."pending", "me"."active", "me"."query",
(SELECT "runs_alias"."completed"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?),
(SELECT "runs_alias"."id"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?), "creator"."id",
"creator"."name",
"creator"."system_role_id",
"creator"."login",
"creator"."password",
"creator"."email",
"creator"."active"
FROM "reports" "me"
JOIN "users" "creator" ON "creator"."id" = "me"."creator_id"
ORDER BY
(SELECT "runs_alias"."completed"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?) DESC;
Raw
produced.sql
SELECT "me"."id",
"me"."name",
"me"."updated",
"me"."creator_id",
"me"."pending",
"me"."active",
"me"."query",
(SELECT "runs_alias"."completed"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?),
(SELECT "runs_alias"."id"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?),
"creator"."id",
"creator"."name",
"creator"."system_role_id",
"creator"."login",
"creator"."password",
"creator"."email",
"creator"."active"
FROM "reports" "me"
JOIN "users" "creator" ON "creator"."id" = "me"."creator_id"
ORDER BY "last_run_completed" DESC;
Raw
Result::Report.pm
use utf8;
package Rebus::Schema::Result::Report;
=head1 NAME
Rebus::Schema::Result::Report
=cut
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 COMPONENTS LOADED
=over 4
=item * L<DBIx::Class::InflateColumn::DateTime>
=back
=cut
__PACKAGE__->load_components("InflateColumn::DateTime");
=head1 TABLE: C<report>
=cut
__PACKAGE__->table("reports");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 name
data_type: 'text'
is_nullable: 0
=head2 updated
data_type: 'timestamp'
datetime_undef_if_invalid: 1
default_value: current_timestamp
is_nullable: 0
=head2 creator_id
data_type: 'integer'
is_foreign_key: 1
is_nullable: 0
=head2 pending
data_type: 'tinyint'
is_nullable: 0
=head2 active
data_type: 'tinyint'
is_nullable: 0
=head2 query
data_type: 'text'
is_nullable: 0
=cut
__PACKAGE__->add_columns(
"id",
{data_type => "integer", is_auto_increment => 1, is_nullable => 0,},
"name",
{data_type => "text", is_nullable => 0},
"updated",
{data_type => "timestamp", datetime_undef_if_invalid => 1, default_value => \"current_timestamp", is_nullable => 0},
"creator_id",
{data_type => "integer", is_foreign_key => 1, is_nullable => 0},
"pending",
{data_type => "tinyint", default_value => 0, is_nullable => 0},
"active",
{data_type => "tinyint", default_value => 0, is_nullable => 0},
"query",
{data_type => "text", default_value => '""', is_nullable => 0}
);
=head1 PRIMARY KEY
=over 4
=item * L</id>
=back
=cut
__PACKAGE__->set_primary_key("id");
=head1 RELATIONS
=head2 creator
Type: belongs_to
Related object: L<Rebus::Schema::Result::User>
=cut
__PACKAGE__->belongs_to(
"creator", "Rebus::Schema::Result::User",
{"foreign.id" => "self.creator_id"},
{is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRICT"},
);
=head2 runs
Type: has_many
Related object: L<Rebus::Schema::Result::ReportRun>
=cut
__PACKAGE__->has_many(
"runs",
"Rebus::Schema::Result::ReportRun",
{"foreign.report_id" => "self.id"},
{cascade_copy => 0, cascade_delete => 0},
);
Raw
Result::ReportRun.pm
use utf8;
package Rebus::Schema::Result::ReportRun;
=head1 NAME
Rebus::Schema::Result::ReportRun
=cut
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 COMPONENTS LOADED
=over 4
=item * L<DBIx::Class::InflateColumn::DateTime>
=back
=cut
__PACKAGE__->load_components("InflateColumn::DateTime");
=head1 TABLE: C<report_runs>
=cut
__PACKAGE__->table("report_runs");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 report_id
data_type: 'integer'
is_foreign_key: 1
is_nullable: 0
=head2 completed
data_type: 'timestamp'
datetime_undef_if_invalid: 1
default_value: current_timestamp
is_nullable: 0
=head2 result
data_type: 'text'
is_nullable: 0
=cut
__PACKAGE__->add_columns(
"id",
{data_type => "integer", is_auto_increment => 1, is_nullable => 0},
"report_id",
{data_type => "integer", is_foreign_key => 1, is_nullable => 0},
"completed",
{data_type => "timestamp", datetime_undef_if_invalid => 1, default_value => \"current_timestamp", is_nullable => 0},
"result",
{data_type => "text", is_nullable => 0}
);
=head1 PRIMARY KEY
=over 4
=item * L</id>
=back
=cut
__PACKAGE__->set_primary_key("id");
=head1 RELATIONS
=head2 report
Type: belongs_to
Related object: L<Rebus::Schema::Result::Report>
=cut
__PACKAGE__->belongs_to(
"report",
"Rebus::Schema::Result::Report",
{"foreign.id" => "self.report_id"},
{is_deferrable => 1, on_delete => "RESTRICT", on_update => "RESTRICT"},
);
1;
Raw
ResultSet::Report.pm
use utf8;
package Rebus::Schema::ResultSet::Report;
=head1 NAME
Rebus::Schema::ResultSet::Report
=cut
use strict;
use warnings;
use parent 'DBIx::Class::ResultSet';
__PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));
=head1 CORRELATE RELATIONS
=head2 with_last_run
Type: special case of 'runs'
Related object: L<Rebus::Schema::Result::ReportRun>
=cut
sub with_last_run {
my $self = shift;
$self->search(
undef,
{
'+columns' => {
last_run_id => $self->correlate('runs')->search(undef, {rows => 1, order_by => {-desc => [qw/completed/]}})
->get_column('id')->as_query,
last_run_completed =>
$self->correlate('runs')->search(undef, {rows => 1, order_by => {-desc => [qw/completed/]}})
->get_column('completed')->as_query,
}
}
);
}
1;
Raw
Test.txt
my @reportResults = $db->resultset('Report')->with_last_run->search(undef, {prefetch => 'creator', order_by => { '-desc' => 'last_run_completed'}})->all;
for my $reportResult ( @reportResults ) {
say $reportResult->last_run_id;
}
Error:
DBI Exception: DBD::Pg::st execute failed: ERROR: column "last_run_completed" does not exist
Which makes sense, as the query run does not add "AS 'field_name'" to the correlate query field... is there any way to get this working?
Raw
wanted.sql
SELECT "me"."id",
"me"."name",
"me"."updated",
"me"."creator_id",
"me"."pending",
"me"."active",
"me"."query",
(SELECT "runs_alias"."completed"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?) AS "last_run_completed",
(SELECT "runs_alias"."id"
FROM "report_runs" "runs_alias"
WHERE ("runs_alias"."report_id" = "me"."id")
ORDER BY "completed" DESC LIMIT ?) AS "last_run_id",
"creator"."id",
"creator"."name",
"creator"."system_role_id",
"creator"."login",
"creator"."password",
"creator"."email",
"creator"."active"
FROM "reports" "me"
JOIN "users" "creator" ON "creator"."id" = "me"."creator_id"
ORDER BY "last_run_completed" DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment