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.