• Joins
• Join Example:
• Links
• LinkName
• Automatic detection of links

DBIx::Recordset has some nice features to make working with multiple tables and their relations easier.

First, you can specify more than one table to the !Table parameter. If you do so, you need to specify how both tables are related. You do this with !TabRelation parameter. This method will access all the specified tables simultanously.

If you have the following two tables, where the field street_id is a pointer to the table street:

  table name
  name	    char (30),
  street_id  integer

  table street
  id	    integer,
  street    char (30),
  city      char (30)

You can perform the following search:

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
		     '!Table'	   => 'name, street',
		     '!TabRelation'=> 'name.street_id = street.id'}) ;

The result is that you get a set which contains the fields name, street_id, street, city and id, where id is always equal to street_id. If there are multiple streets for one name, you will get as many records for that name as there are streets present for it. For this reason, this approach works best when you have a 1:1 relation.

It is also possible to specify JOINs. Here's how:

  *set = DBIx::Recordset -> Search ({
            '!DataSource' => 'dbi:drv:db',
	    '!Table'   => 'name, street',
	    '!TabJoin' => 'name LEFT JOIN street ON (name.street_id=street.id)'}) ;

The difference between this and the first example is that this version also returns a record even if neither table contains a record for the given id. The way it's done depends on the JOIN you are given (LEFT/RIGHT/INNER) (see your SQL documentation for details about JOINs).

If you have 1:n relations between two tables, the following may be a better way to handle it:

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
		     '!Table'	   => 'name',
		     '!Links'	   => {
			'-street'  => {
			    '!Table' => 'street',
			    '!LinkedField' => 'id',
			    '!MainField'   => 'street_id'
			    }
			}
		    }) ;

After that query, every record will contain the fields name and street_id. Additionally, there is a pseudofield named -street, which could be used to access another recordset object, which is the result of a query where street_id = id. Use

  $set{name} to access the name field
  $set{-street}{street} to access the first street (as long as the
				    current record of the subobject isn't
				    modified)

  $set{-street}[0]{street}	first street
  $set{-street}[1]{street}	second street
  $set{-street}[2]{street}	third street

  $set[2]{-street}[1]{street} to access the second street of the
				    third name

You can have multiple linked tables in one recordset; you can also nest linked tables or link a table to itself.

NOTE: If you select only some fields and not all, the field which is specified by '!MainField' must be also given in the '!Fields' or '$fields' parameter.

NOTE: See also Automatic detection of links below

In the LinkName feature you may specify a "name" for every table. A name is one or more fields which gives a human readable "key" of that record. For example in the above example id is the key of the record, but the human readable form is street.

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
		     '!Table'	   => 'name',
		     '!LinkName'   => 1,
		     '!Links'	   => {
			'-street'  => {
			    '!Table' => 'street',
			    '!LinkedField' => 'id',
			    '!MainField'   => 'street_id',
			    '!NameField'   => 'street'
			    }
			}
		    }) ;

For every record in the table, this example will return the fields:

  name  street_id  street

If you have more complex records, you may also specify more than one field in !NameField and pass it as an reference to an array e.g. ['street', 'city']. In this case, the result will contain

  name  street_id  street  city

If you set !LinkName to 2, the result will contain the fields

  name  street_id  STREET_ID

where STREET_ID contains the values of the street and city fields joined together. If you set !LinkName to 3, you will get only

  name  street_id

where street_id contains the values of the street and city fields joined together.

NOTE: The !NameField can also be specified as a table attribute with the function TableAttr. In this case you don't need to specify it in every link. When a !NameField is given in a link description, it overrides the table attribute.

DBIx::Recordset and DBIx::Database will try to automatically detect links between tables based on the field and table names. For this feature to work, the field which points to another table must consist of the table name and the field name of the destination joined together with an underscore (as in the above example name.street_id). Then it will automatically recognized as a pointer to street.id.

  *set = DBIx::Recordset -> Search ({'!DataSource' => 'dbi:drv:db',
				     '!Table'	   => 'name') ;

is enough. DBIx::Recordset will automatically add the !Links attribute. Additionally, DBIx::Recordset adds a backlink (which starts with a star ('*')), so for the table street, in our above example, there will be a link, named *name, which is a pointer from table street to all records in the table name where street.id is equal to name.street_id.

You may use the !Links attribute to specify links which can not be automatically detected.

NOTE: To specify more then one link from one table to another table, you may prefix the field name with an specifier followed by two underscores. Example: first__street_id, second__street_id. The link (and backlink) names are named with the prefix, e.g. -first__street and the backlink *first__name.