CODEWITHSUNDEEP
perldbix-class
Peter
Peter

Reputation: 320

Perl's DBIx::Class: Order on joined column and limit result

Working my way through some tutorials and other sources on the internet to learn Perl's DBIx::Class. Making some progress, but the following is nagging me for some time now. Kinda at a loss

I've got 2 tables:

There is an one2many relationship: 1 article having several versions of content. In the schema files it look like this:

For the article

__PACKAGE__->add_columns(
  "article_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "title",
  { data_type => "text", is_nullable => 0 },
  "slug",
  { data_type => "text", is_nullable => 0 },
  "authorid",
  { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
  "created",
  { data_type => "timestamp", is_nullable => 0 },
  "abstract",
  { data_type => "text", is_nullable => 0 },
);

the relation being:

__PACKAGE__->has_many(
  "article_contents",
  "MyApp::Schema::Result::ArticleContent",
  { "foreign.articleid" => "self.article_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

For the content

__PACKAGE__->add_columns(
  "article_content_id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "articleid",
  { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
  "version",
  { data_type => "integer", is_nullable => 0 },
  "editorid",
  { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
  "created",
  { data_type => "timestamp", is_nullable => 0 },
  "published",
  { data_type => "timestamp", is_nullable => 0 },
  "content",
  { data_type => "text", is_nullable => 0 },
);

the relation being

__PACKAGE__->belongs_to(
  "articleid",
  "MyApp::Schema::Result::Article",
  { article_id => "articleid" },
  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
);

What I would like to do is get the meta data for article 1 and the content with the highest version number. Something like

Select article.title, article_content.content
Join article_content On article_content.articleid = article.article_id
Order by article_content.version DESC
Where article_id = 1
Limit 1 Offset 1

in SQL

Kinda following https://metacpan.org/release/JROBINSON/DBIx-Class-Manual-SQLHackers-1.2/view/lib/DBIx/Class/Manual/SQLHackers/SELECT.pod#SELECT-with-LIMIT-and-OFFSET to get that going and came up with the following test code

say "\nArticle 1 content versie 2";
my $article_1_rs = $schema->resultset('Article')->search(
    { 
        articleid => 1
    },
    {
        join => 'article_contents',
        order_by => {'-desc' => ['article_contents.version']},
        rows => 1,
        page => 1
    }
);

while( my $article_1 =$article_1_rs->next() ){
    say $article_1->title;
    for my $content ($article_1->article_contents){
        say $content->version, " => ", $content->content;
    }
}

This gives me:

Article 1 content versie 2
Artikel 1
1 => Inhoud artikel 1
2 => Inhoud artikel 1 versie 2

As you can see I get the content from both version which are in the database. I was expection to get only the version 2 content. Tried it without the join. A previous problem I had was solved NOT using a join, but I get no result at all without it.

Nov 19: Some further research:

Learned about about "DBIC_TRACE" and added it:

$ENV{DBIC_TRACE} = '1';

and after correcting a goofup in the article_id column name the code is now

say "\n__Article 1 content versie 2 using search__";
my $article_1_rs = $schema->resultset('Article')->search(
    { 
        article_id => 1
    },
    {
        join => 'article_contents',
        order_by => {'-desc' => ['article_contents.version']},
        rows => 1,
        page => 1
    }
);
while( my $article_1 =$article_1_rs->next() ){
    say $article_1->title;
    for my $content ($article_1->article_contents){
        say $content->version, " => ", $content->content;
    }
}

This gave me the following output (edited for clarity):

say =>__Article 1 content versie 2 using search__
SELECT 
    me.article_id, 
    me.title, 
    me.slug, 
    me.authorid, 
    me.created, 
    me.abstract 
FROM article me 
LEFT JOIN article_content article_contents ON article_contents.articleid = me.article_id 
WHERE ( article_id = ? ) 
ORDER BY article_contents.version DESC 
LIMIT ?: '1', '1'
say =>Artikel 1
SELECT 
    me.article_content_id, 
    me.articleid, 
    me.version, 
    me.editorid, 
    me.created, 
    me.published, 
    me.content 
FROM article_content me 
WHERE ( me.articleid = ? ): '1'
say =>1 => Inhoud artikel 1
say =>2 => Inhoud artikel 1 versie 2

I can see the first query is influenced by what I do in the join. It had in fact the join, the order and the offset. No columns are selected from "article_content". The second query kinda surprised me. It's a straightforward select from 'article_content' using a where on the foreign key. It makes it clear to me where the resulting data set comes from. Not the why :S

Seems the join section in the declaration of "$article_1_rs" serves no purpose. So am going to rebuild it to a find without the join, being more to the point anyway. Seeams I should be looking for a way to influence the second query. Not sure how though.

Upvotes: 1

Views: 95

Answers (1)

Peter
Peter

Reputation: 320

Fooled around a bit with "prefetch" but could not get that going for me. Made me humble: I thought I had a pretty good eye for SQL, but I couldn't make heads or tails out of the SQL it was producing. Anyway...

Already had the feeling I should be able to search the related table using the result set (result row?) which I get by doing a simple search. Finding something called "search_related" got me going:

say "\n__Article 1 content versie 2 using search_related__";
# First create a resultset (resultrow?) by doing a search
my $article_1 = $schema->resultset('Article')->find(
    { 
        article_id => 1
    }
);

# Must be a row, I can directly access the content without a loop
say 'Title: ',$article_1->title;

# Use the row to do a search_related using the relation article_contents
my $contents = $article_1->search_related(
    'article_contents',
    {
        # No actual need for searching
        # since I'm using the relation
        # so I can leave this out
        # articleid => '1',
    },
    {
        order_by => {'-desc' => ['version']},
        rows => 1,
        page => 1

    }
);

# Got a resultset so have to loop it 
foreach my $content ($contents->next){
    say 'Version: ', $content->version;
}

which gives me

__Article 1 content versie 2 using search_related__
Title: Artikel 1
Version: 2

Still wondering though. Since I'm using the relationship which limits the article_content to article 1, I have no real need to search in the relation and can leave that empty. The order by and limit do the trick of selecting the highest version. Feels like there must me another method than search_related to do this. Something for later I guess

Upvotes: 1

Related Questions