DBIC- select specific columns in "prefetch" while maintaining has_many relationship

Question

I have two tables with 'Artist' has_many 'CD' .

I want to fetch 'artist' and 'CDs' and use HashRefInflator to get something like (JSON format) I use 'HashRefInflator'.

     [ {
        
        "artist_name": "Dummy",
        "artist_id": "1",
            "cds": [{
            "cd_id": "1,
            "cd_desc": "Dummy",
            }],
         },
      ]

When I use

$schema->resultset('Artist')->search({}, 
                                       {
                                         prefetch => 'cds',
                                       });

I get result with "EXTRA" column from CD table. I want to be able to select only specific column from 'CD'.

When I use

$schema->resultset('Artist')->search({}, 
                                     {'+select' => [ 'columns I need' ],
                                      '+as' => [ 'col names' ],
                                     });

I get 'columns i need' merged with main columns instead of a hierarchy.

      [ {
        "artist_name": "Dummy",
        "artist_id": "1",
        "cds": []
        **"cd_id": "1,
        "cd_desc": "Dummy",**
      
   
         },
        ]

How can I prefetch specific columns from related table while maintaining the hierarchical structure?

Edit: I am sorry if I was not very clear. The problem here with join and +columns / +select is that it does not preserve the hierarchical data structure.

The 'cds' should be an array of objects within 'Artist' object.Instead it gets joined at Artist level. I could not use "collapse" as I have an older version of DBIx::Class. I would try to install a later version and check.

possible duplicate of [DBIx-Class prefetch usage](http://stackoverflow.com/questions/19786540/dbix-class-prefetch-usage) — Relequestual, Sep 17 '15 at 15:22

user353255 · Answer 1 · 2014-03-10T13:37:39.983

1

Use join instead of prefetch:

$schema->resultset('Artist')->search(
    {},
    {
        join         => ['cds'],
        '+select'    => ['cd.desc'],
        result_class => 'DBIx::Class::ResultClass::HashRefInflator'
    }
);

edited Mar 10 '14 at 13:37

answered Mar 10 '14 at 11:57

user353255

315
2
8

1

prefetch is just join + columns. It's recommended to use columns instead of select + as because it's easier to use and harder to get wrong. – Alexander Hartmaier Mar 10 '14 at 12:03
"How can I prefetch specific columns from related table while maintaining the hierarchical structure. ?" I think the user wants only some columns from the join. Prefetch will get all of them. edit: I have to research. Did not know about columns. – user353255 Mar 10 '14 at 13:36
This leads to the problem where 'cds' => [] is empty and the columns from 'CD' table appear at a higher level as shown in the second JSON example. – kaykay Mar 11 '14 at 06:35
If you are trying to join on a many to many, you will need to use +columns, and specify the matching DBIC space. This is shown in a good example at https://metacpan.org/pod/DBIx::Class::ResultSet#prefetch – Relequestual Sep 17 '15 at 15:20

score 0 · Answer 2 · answered Mar 10 '14 at 15:53

0

You'll need DBIx::Class version 0.08250 or later which supports the collapse result set attribute:

my $rs = $schema->resultset('Artist')->search({}, {
    '+columns' => [ qw/ cds.col1 cds.col2 / ],
    join       => 'cds',
    collapse   => 1,
});

answered Mar 10 '14 at 15:53

nwellnhof

32,319
7
89
113

DBIC- select specific columns in "prefetch" while maintaining has_many relationship

2 Answers2