Chapter 2.6: JOIN Tables

So far, you are assumed to run topics for a single database table. For relational database, a search usually requires multiple table joins.

While you can build raw SQL to do this, as described in Section 2.2,  Genelet has a built-in solution which is cleaner and can take advantage of pagination etc. as well. Here is it.

 

Step 1: Add New Attribute current_tables to Model.pm

This array reference current_tables contains all joint tables, each of which is represented by a map having the following key-values:

• name: the table name
• alias: optional, the alias of the table
• type: one of join types – INNER or LEFT
• using: to use USING clause in joining the table
• on: to use ON clause in joining the table

The first item in current_tables is for the main table having only name and alias.

 

Step 2: Re-define topics_pars to be HASH

By default, the searched fields will use their table (or table alias) names as prefixes. You can assign labels to these cumbersome fields, by re-defining topics_pars as a hash.

 

Example

You’d like to get a list of all children in full names from a family table and a children table.

create table family (
  family_id int not null auto_increment,
  last_name varchar(255) not null,
  mother varchar(255),
  father varchar(255),
  primary key (family_id)
);

create table child (
  child_id int not null auto_increment,
  family_id int not null,
  first_name varchar(255) not null,
  primary key (child_id),
  index (family_id)
);

This SQL will produce the correct list:

SELECT f.family_id AS family_id, c.child_id as child_id, f.last_name AS last_name, c.first_name as first_name
FROM family f
INNER JOIN child c ON (f.family_id=c.family_id)

You may override the topics method in Child’s Model.pm to achieve the goal. Then you have to set up own pagination.

Alternatively, you may use:

__PACKAGE__->setup_accessors(
  ...
  current_tables => [
    {name=>"family", alias=>"f"},
    {name=>"child",  alias=>"c", type=>"INNER", on=>"f.family_id=c.family_id"}
  ],
  topics_pars    => {
    "f.family_id" => "family_id",
    "f.last_name" => "last_name",
    "c.child_id"  => "child_id",
    "c.first_name"=> "first_name"
  }
);

which will use the inherited topics to search and do other functions, as if it were a single table.