I have the following tables in sqlite (in create table statement):

CREATE TABLE users (
id autoinc_int,
name varchar(30),
password varchar(30)
);

CREATE TABLE roles (
id autoinc_int,
name varchar(20)
);

CREATE TABLE users_roles (
id autoinc_int,
user_id int,
role_id int,
foreign key(user_id) references users(id),
foreign key(role_id) references roles(id)
);

and the following classes:

TUser = class(TtiObject)
private
  FName: String;
  FPassword: String;
  FRoles: TRoles;
published
  property OID;
  property Name: String read FName write FName;
  property Password: String read FPassword write FPassword;
  property Roles: TRoles read FRoles write FRoles;
end;
TRole = class(TtiObject)
private
  FName: String;
published
  property OID;
  property Name: String read FName write FName;
end;

they're mapped with:

with gTIOPFManager.ClassDBMappingMgr do begin
  RegisterMapping(TUser,'users','OID','id',[pktDB]);
  RegisterMapping(TUser,'users','Name','name');
  RegisterMapping(TUser,'users','Password','password');
  RegisterMapping(TUser,'users_roles','Roles','???'); // <-- what should I
fill it with?
  RegisterCollection(TUsers,TUser);
end;
with gTIOPFManager.ClassDBMappingMgr do begin
  RegisterMapping(TRole,'roles','OID','id',[pktDB]);
  RegisterMapping(TRole,'roles','Name','name');
  RegisterCollection(TRoles,TRole);
end;

I need to have access to TUser.Roles[i].Name

The "auto-mapping" feature of tiOPF is very limited, so I personally never use it. There are other options though:

1) Use "hard-coded" visitors for this area of your application (yes you can use auto-mapping and hard-coded visitors in the same application). Many developers start off using auto-mapping, and when they hit the limitations of auto-mapping, switch over to hard-coded visitors for those areas.

2) Use something like the Relationship Manager (RM) design pattern for that area of your application. There is an implementation of RM in tiOPF, or create your own. Here are two links describing the Relationship Manager design pattern:

http://opensoft.homeip.net:8080/articles/
or
http://www.andypatterns.com/index.php/design_patterns/relationship_manager

For option (2), this is normally the steps to follow. The basic approach is to:

a) Read in the first object hierarchy (the from objects) b) Read in the second object hierarchy (the to objects) c) Read in the mappings, which are stored in a table with three columns: OID, OID_From and OID_To. For each row in this table, I search for the matching object
in the From list and To list by OID. I then add a mapping object to the list of mappings that is described in the 'Relationship Manager' patter.

There are some methods on TtiObject and TtiObjectList to make it easy to find an owned object by OID.

Ouch, that's a bit bad to hear, I thought I could get something simpler. I'll try to implement that relationship manager pattern because I want to avoid direct sql.

leledumbo wrote

Ouch, that's a bit bad to hear, I thought I could get something simpler. I'll try to implement that relationship manager pattern because I want to avoid direct sql.

Yeah, Auto-Mapping hasn't received a lot of love in recent years. I have plans to totally revamp it, but I just never seem to get the time to do the actual work.

As for hard-coded visitors. Yes they are driven by SQL, but that doesn't mean you need to write the SQL yourself, or implement the visitors manually. Use the tools! :-) Lazarus's Data Desktop (I think that's the name) can generate complete units of code with SQL for you. I also ported and updated a old tiOPF tool called tiSQLEditor. With tiSQLEditor, it can generate vast amounts of tiOPF code for you, based on simple SQL statements. For the remainder, I use the power of Code Templates in my IDE. I've produced some 30-40 fully working hard coded visitors in 15 minutes - that's a lot of mostly auto generated code. So "hard-coded visitors" doesn't always equal "hard work"! ;-)

Use the tools! :-) Lazarus's Data Desktop (I think

that's the name) can generate complete units of code with SQL for you.

Oh, yeah. I remember ever tried that feature (generating tiOPF classes) but only had a glance guess this is the time to have a serious look at it B)