JT Smith

DBIx::Class is a great way to hide database interactions in your Perl classes. However, something that you might find easy in normal DBI queries can seem hard in DBIx::Class, because you lack direct access to the SQL. Take for example the following query:

select dayparts.name from eventtyperooms 
  join slots on (eventtyperooms.room_id=slots.room_id) 
  join dayparts on (slots.daypart_id = dayparts.id) 
  where slots.is_reserved=0 and eventtyperooms.eventtype_id='E375219C-CDBB-11E5-8739-AFC57843E904' 
  group by slots.daypart_id 
  order by dayparts.start_date asc;

There are lots of joins going on here and not all of them are on primary keys. Plus we’ve got some other qualifiers in there. This is where search_related() can come to the rescue.

$eventtype->search_related('eventtyperooms')
  ->search_related('slots', {'slots.is_reserved' => 0})
  ->search_related('daypart')
  ->search(undef, {
    group_by => 'slots.daypart_id', 
    order_by => 'daypart.start_date'
  });

The above code will generate the following query:

SELECT `daypart`.`id`, `daypart`.`name`, `daypart`.`is_locked`, `daypart`.`start_date`
  FROM `eventtyperooms` `me` 
  JOIN `slots` `slots` 
    ON `slots`.`room_id` = `me`.`room_id` 
  JOIN `dayparts` `daypart` 
    ON `daypart`.`id` = `slots`.`daypart_id` 
WHERE `me`.`eventtype_id` = 'E375219C-CDBB-11E5-8739-AFC57843E904' AND `slots`.`is_reserved` = '0' 
GROUP BY `slots`.`daypart_id` 
ORDER BY `daypart`.`start_date`

This allows you to use all the existing relationships you’ve set up in your schema to do complex joins.

[From my blog.]