admin管理员组

文章数量:1024026

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:

  • article for meta data
  • article_content for the actual content

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 .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.

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:

  • article for meta data
  • article_content for the actual content

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./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.

  • perl
  • dbix-class
Share Improve this question edited Nov 25, 2024 at 16:35 Alexander Hartmaier 2,20413 silver badges21 bronze badges asked Nov 18, 2024 at 22:15 PeterPeter 3282 silver badges13 bronze badges 2
  • 1 Is this not the same reason as in your previous question stackoverflow/q/79157060/1331451? You need to use prefetch to avoid those second queries. – simbabque Commented Nov 19, 2024 at 10:33
  • Thanks for the reply... was allready fooling around with prefetch. Made myself a really complex SQL query, will inverstigate further. – Peter Commented Nov 19, 2024 at 10:40
Add a comment  | 

1 Answer 1

Reset to default 1

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

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:

  • article for meta data
  • article_content for the actual content

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 .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.

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:

  • article for meta data
  • article_content for the actual content

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./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.

  • perl
  • dbix-class
Share Improve this question edited Nov 25, 2024 at 16:35 Alexander Hartmaier 2,20413 silver badges21 bronze badges asked Nov 18, 2024 at 22:15 PeterPeter 3282 silver badges13 bronze badges 2
  • 1 Is this not the same reason as in your previous question stackoverflow/q/79157060/1331451? You need to use prefetch to avoid those second queries. – simbabque Commented Nov 19, 2024 at 10:33
  • Thanks for the reply... was allready fooling around with prefetch. Made myself a really complex SQL query, will inverstigate further. – Peter Commented Nov 19, 2024 at 10:40
Add a comment  | 

1 Answer 1

Reset to default 1

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

本文标签： Perl39s DBIxClass Order on joined column and limit resultStack Overflow

版权声明：本文标题：Perl's DBIx::Class: Order on joined column and limit result - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745590922a2157880.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。