DBIx::Class - get all relationship that was used as a condition using prefetch?

Question

Here are three tables: product, model, and product_model that maps products and models in N:M relationship.

product          product_model            model

id name          product_id model_id      id name
------------     -------------------      ----------
p1 Product 1     p1         m1            m1 Model 1
p2 Product 2     p2         m1            m2 Model 2
...              p2         m2

What I want to do: Find all products that support Model 2(eg. product 2). Then, for each product, show the list of model_ids that the product supports(product 2 => [ m1,m2 ])

This was my first try. I needed N more queries to search model_ids for each product.

# 1 query for searching products
my @products = $schema->resultset('Product')->search(
   { 'product_models.model_id' => 'm2' },
   { 'join' => 'product_model' },
)
# N queries for searching product_models for each product
foreach my $product ( @products ) {
   my @model_ids = map { $_->model_id } $product->product_models;
   # @model_ids = ( 'm1', 'm2' ) for p2
}

I looked for a way to get the result using only one query. Replacing join with prefetch didn't work.

my @products = $schema->resultset('Product')->search(
   { 'product_models.model_id' => 'm2' },
   { 'prefetch' => 'product_model' },      # here
)

# no additional queries, but...
foreach my $product ( @products ) {
   my @model_ids = map { $_->model_id } $product->product_models;
   # now, @model_ids contains only ( `m2` )
}

Next, I tried "prefetch same table twice":

my @products = $schema->resultset('Product')->search(
    { 'product_models.model_id' => 'm2' },
    { 'prefetch' => [ 'product_models', 'product_models' ] },
);

foreach my $product ( @products ) {
    my @model_ids = map { $_->model_id } $product->product_models;
}

It seemed that I succeeded. Only one query was executed and I got all model IDs from it.

However I wasn't so sure that this is the right(?) way. Is this a correct approach?

For example, if I used join instead of prefetching, Product 2 appears in the loop twice. I understand that, because the joined table is like:

id name      p_m.p_id p_m.m_id p_m_2.p_id p_m_2.m_id
p2 Product 2 p2       m2       p2         m1
p2 Product 2 p2       m2       p2         m2   -- Product 2, one more time

Why does Product 2 appear only once when I use prefetch?

The resulting queries are almost same, except the difference of SELECT fields:

SELECT "me"."id", "me"."name",
 "product_models"."product_id", "product_models"."model_id",    -- only in prefetch
 "product_models_2"."product_id", "product_models_2"."model_id" --
  FROM "product" "me"
  LEFT JOIN "product_model" "product_models"
    ON "product_models"."product_id" = "me"."id"
  LEFT JOIN "product_model" "product_models_2"
    ON "product_models_2"."product_id" = "me"."id"
WHERE "product_models"."model_id" = 'm2'

Answer 1

If you have the correct relationships in your schema, this is possible with a single query. But it's tricky. Let's assume your database looks like this:

CREATE TABLE product
 (`id` VARCHAR(2) PRIMARY KEY, `name` VARCHAR(9))
;
INSERT INTO product
 (`id`, `name`) VALUES
 ('p1', 'Product 1'),
 ('p2', 'Product 2')
;
CREATE TABLE product_model (
  `product_id` VARCHAR(2),
  `model_id` VARCHAR(2),
  PRIMARY KEY (product_id, model_id),
  FOREIGN KEY(product_id) REFERENCES product(id),
  FOREIGN KEY(model_id) REFERENCES model(id)
)
;
INSERT INTO product_model
 (`product_id`, `model_id`) VALUES
 ('p1', 'm1'),
 ('p2', 'm1'),
 ('p2', 'm2')
;
CREATE TABLE model
 (`id` VARCHAR(2) PRIMARY KEY, `name` VARCHAR(7))
;
INSERT INTO model
 (`id`, `name`) VALUES
 ('m1', 'Model 1'),
 ('m2', 'Model 2')
;

This is essentially your DB from the question. I added primary keys and foreign keys. You probably have those anyway.

We can now create a schema from that. I've written a simple program that uses DBIx::Class::Schema::Loader to do that. It creates an SQLite database on the fly. (If no-one has put this on CPAN, I will).

The SQL from above will go in the __DATA__ section.

use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/ make_schema_at /;

# create db
unlink 'foo.db';
open my $fh, '|-', 'sqlite3 foo.db' or die $!;
print $fh do { local $/; <DATA> };
close $fh;

$ENV{SCHEMA_LOADER_BACKCOMPAT} = 1;

# create schema
my $dsn = 'dbi:SQLite:foo.db';
make_schema_at(
    'DB',
    {
        #    debug => 1,
    },
    [ $dsn, 'sqlite', '', ],
);

$ENV{DBIC_TRACE} = 1;

# connect schema
my $schema = DB->connect($dsn);

# query goes here

__DATA__
# SQL from above

Now that we have that, we can concentrate on the query. At first this will look scary, but I'll try to explain.

my $rs     = $schema->resultset('Product')->search(
    { 'product_models.model_id' => 'm2' },
    {
        'prefetch' => {
            product_models => {
                product_id => {
                    product_models => 'model_id'
                }
            }
        }
    },
);

while ( my $product = $rs->next ) {
    foreach my $product_model ( $product->product_models->all ) {
        my @models;
        foreach my $supported_model ( $product_model->product_id->product_models->all ) {
            push @models, $supported_model->model_id->id;
        }
        printf "%s: %s\n", $product->id, join ', ', @models;
    }
}

The prefetch means join on this relation, and keep the data around for later. So to get all models for your product, we have to write

#          1                   2
{ prefetch => { product_models => 'product_id' } }

Where product_models is the N:M table, and product_id is the name of the relation to the Models table. The arrow => 1 is for the first join from Product to ProductModel. The 2 is for ProductModel back to every product that has the model m2. See the drawing of the ER model for an illustration.

Now we want to have all the ProductModels that this Product has. That's arrow 3.

#          1                   2               3
{ prefetch => { product_models => { product_id => 'product_models' } } }

And finally, to get the Models for that N:M relation, we have to use the model_id relationshop with arrow 4.

{
    'prefetch' => {                            # 1
        product_models => {                    # 2
            product_id => {                    # 3
                product_models => 'model_id'   # 4
            }
        }
    }
},

Looking at the ER model drawing should make that clear. Remember that each of those joins is a LEFT OUTER join by default, so it will always fetch all the rows, without loosing anything. DBIC just takes care of that for you.

Now to access all of that, we need to iterate. DBIC gives us some tools to do that.

while ( my $product = $rs->next ) {

    #                                   1
    foreach my $product_model ( $product->product_models->all ) {
        my @models;

        #                                           2           3
        foreach my $supported_model ( $product_model->product_id->product_models->all ) {

            #                             4 
            push @models, $supported_model->model_id->id;
        }
        printf "%s: %s\n", $product->id, join ', ', @models;
    }
}

First we grab all the ProductModel entries (1). For each of those, we take the Product (2). There is always only one Product in every line, because that way we have a 1:N relation, so we can directly access it. This Product in turn has a ProductModel relation. That's 3. Because this is the N side, we need to take all of them and iterate. We then push the id of every Model (4) into our list of models for this product. After that, it's just printing.

Here's another way to look at it:

We could eliminate that last model_id in the prefetch, but then we'd have to use get_column('model_id') to get the ID. It would save us a join.

Now if we turn on DBIC_TRACE=1, we get this SQL statement:

SELECT me.id, me.name, product_models.product_id, product_models.model_id, product_id.id, product_id.name, product_models_2.product_id, product_models_2.model_id, model_id.id, model_id.name
FROM product me
LEFT JOIN product_model product_models ON product_models.product_id = me.id
LEFT JOIN product product_id ON product_id.id = product_models.product_id
LEFT JOIN product_model product_models_2 ON product_models_2.product_id = product_id.id
LEFT JOIN model model_id ON model_id.id = product_models_2.model_id
WHERE (product_models.model_id = 'm2')
ORDER BY me.id

If we run this against our DB, we have these rows:

p2|Product 2|p2|m2|p2|Product 2|p2|m1|m1|Model 1
p2|Product 2|p2|m2|p2|Product 2|p2|m2|m2|Model 2

Of course that's pretty useless if we do it manually, but DBIC's magic really helps us, because all the weird joining and combining is completely abstracted away, and we only need one single query to get all the data.