rajeev rajeev - 3 months ago 14
Perl Question

equivalent DBIx::Class syntax?

i have this mysql query from this question.

SELECT a.*
FROM products a
INNER JOIN product_tags b ON a.product_id = b.product_id
WHERE b.tag_id IN (1,23,54)
GROUP BY a.product_id
HAVING COUNT(1) = 3


i am trying to figure how to get this converted to DBIx::Class syntax?

from DBIx docs, this is what it says about joins, but i am not sure how to incorporate it?

DEFINING JOINS AND RELATIONSHIPS ^

In DBIx::Class each relationship between two tables needs to first be defined in the ResultSource for the table. If the relationship needs to be accessed in both directions (i.e. Fetch all tracks of a CD, and fetch the CD data for a Track), then it needs to be defined for both tables.

For the CDs/Tracks example, that means writing, in MySchema::CD:

MySchema::CD->has_many('tracks', 'MySchema::Tracks');


And in MySchema::Tracks:

MySchema::Tracks->belongs_to('cd', 'MySchema::CD', 'CDID');


There are several other types of relationships, they are more comprehensively described in DBIx::Class::Relationship.
USING JOINS ^

Once you have defined all your relationships, using them in actual joins is fairly simple. The type of relationship that you chose e.g. has_many, already indicates what sort of join will be performed. has_many produces a LEFT JOIN for example, which will fetch all the rows on the left side, whether there are matching rows on the right (table being joined to), or not. You can force other types of joins in your relationship, see the DBIx::Class::Relationship docs.

When performing either a search or a find operation, you can specify which relations to also refine your results based on, using the join attribute, like this:

$schema->resultset('CD')->search(
{ 'Title' => 'Funky CD',
'tracks.Name' => { like => 'T%' }
},
{ join => 'tracks',
order_by => ['tracks.id'],
}
);


If you don't recognise most of this syntax, you should probably go read "search" in DBIx::Class::ResultSet and "ATTRIBUTES" in DBIx::Class::ResultSet, but here's a quick break down:

The first argument to search is a hashref of the WHERE attributes, in this case a restriction on the Title column in the CD table, and a restriction on the name of the track in the Tracks table, but ONLY for tracks actually related to the chosen CD(s). The second argument is a hashref of attributes to the search, the results will be returned sorted by the id of the related tracks.

Answer

So first you'd define a rel on the tags from products.

 MySchema::Result::Product->has_many(
    'tags', 'MySchema::Result::ProductTag', 'product_id'
 );

Thne define a rel on products from tags:

 MySchema::Result::ProductTag->belongs_to(
    'products', 'MySchema::Result::Product', 'product_id'
 );

These would have already been inferred if you were using Schema::Loader by the way. (Shameless plug: the above would be shorter and sweeter with DBIx::Class::Candy and DBIx::Class::Helper::Row::RelationshipDWIM)

Now to replicate your original query:

$schema->resultset('Product')->search({
  tags.tag_id => { -in => [1,23,54] },
}, {
  join => 'tags',
  group_by => 'me.product_id',
  having => { 'count 1' => 3 },
})