Retrieving an object based upon a composed relationship

[James Allen]

Hi guys,

I know this has been discussed before but I can't remember the best
way of doing it.. Here's what I'm trying to do:

User object:

<object name="Users" table="tblSiteUsers"
decorator="model.users.user">
<id name="id" type="numeric" />
<property name="Name" type="string" column="Name" />
<property name="Username" type="string" column="Username" />
<property name="Password" type="string" column="Password" />
<property name="Email" type="string" column="Email" />
</object>

Search Options object:

<object name="SearchOptions" table="tblUserSearchOptions"
decorator="model.search.searchoptions">
<id name="ID" type="numeric" />
<property name="SearchFields" type="string" column="SearchFields" /
>
<property name="SearchType" type="string" column="SearchType" />
<property name="SearchFilter" type="string" column="SearchFilter" /
>

<!-- Link between a search option and the user it relates to -->
<manytoone name="User">
<link to="users.Users" column="lnkIDUser"/>
</manytoone>
</object>

In this scenario, what is the best way to retrieve a SearchOptions
object based upon a specific user?

Do I have to resort to TQL and do an inner join on users, match the
user ID and then grab the object from Transfer based on it's ID?

I can't remember if there is an easier way to match an object based on
one of it's compositions.. Anything that has been added to Transfer in
recent version that might help or is this definitely one for TQL?

Thanks in advance,
James.

[Brian Kotek]

As far as I know, TQL is the only way this can be done.

[Elliott Sprehn]

There's two ways to do this. The first is TQL.

The other way is to add:

<property
name="userId"
column="lnkIDUser"
ignore-insert="true"
refresh-insert="true"
ignore-update="true"
refresh-update="true"
set="false"/>

(Wow that's long winded! I wish there was a way to get this by default
from Transfer... anyway.)

This tells transfer to add a read only property to each SeachOptions
object that has to same value as the associated User object would.
It'll automatically be refreshed by transfer when this object changes.

Now you can do:

filter = structNew();
filter.userId = 1;
filter.searchType = "awesome";
searchOptions = transfer.readByPropertyMap("SearchOptions",filter);

Note that since it's a many2one you have to have at least one other
filter option like "searchType" otherwise you could get more than one
row back and Transfer will throw an exception, or just use
listByPropertyMap() instead. :)

- Elliott

[James Allen]

Hey there Elliott,

Oooooh I like that.. When did Mr Mandel slip that one in?

So this will work fine even with a composed relationship defined? Spot on..
So the ignore and refresh params allow this to work - if I didn't include
them I would get the duplicate field error that you usually get when trying
to mix a propery and a composed relationship in the same definition?

I've done it with TQL but might look at changing to this or using it in
other places.

Why do I need another filter option though? userId will always be unique.

Cheers and thanks again,
James.

[Mark Mandel]

Heh... interesting how people find clever tricks.

Not * my * preferred method, but it works!

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[James Allen]

Is your preferred method TQL Mark?

[Mark Mandel]

I prefer TQL, as then you don't get a bleed between the object model
and the relational model.

But hey, that's just my opinion ;o)

Mark

[Elliott Sprehn]

On Aug 30, 6:44 am, "James Allen" <slingsho...@googlemail.com> wrote:
> Hey there Elliott,
>
> Oooooh I like that.. When did Mr Mandel slip that one in?
>
> So this will work fine even with a composed relationship defined? Spot on..
> So the ignore and refresh params allow this to work - if I didn't include
> them I would get the duplicate field error that you usually get when trying
> to mix a propery and a composed relationship in the same definition?
>
> I've done it with TQL but might look at changing to this or using it in
> other places.
>
> Why do I need another filter option though? userId will always be unique.
>

If userId is always unique then why not just have it be a many2one on
the User object instead and just call user.getSearchOptions() instead?

With the relationship (SearchOptions many-to-one Users) as you've
defined it now, multiple SearchOptions objects can be related to any
one User, which means they have the same userId. This means that
calling readByProperty("SearchOptions","userId",123) is unsafe because
if there's more than one SearchOptions for that user Transfer will
throw an exception.

- Elliott

[Elliott Sprehn]

On Aug 30, 12:09 pm, "Mark Mandel" <mark.man...@gmail.com> wrote:
> I prefer TQL, as then you don't get a bleed between the object model
> and the relational model.
>
> But hey, that's just my opinion ;o)
>

I really wish Transfer provided this by default so you could make
efficient TQL queries. Currently there's no way to make a proper TQL
query based on the composite object without doing a join, which
transfer turns into a join, which means your database is potentially
joining huge record sets when 'select * from table where userId = ?'
would have been sufficient.

Can't say I really see any bleed argument since it's effectively the
same as object.getUser().getId(), only it doesn't force the lazy load!
I mean Transfer has the knowledge about the composition, it's just not
available. :/ Hmm...

I guess I'm okay with the <property/> trick. Not the most efficient,
but it works. What I think fixes the bigger join TQL issue is
providing proper native syntax to be smarter about this.

What about adding composition id access instead:

select * from SearchOptions s where s.user.id = ?

Then you can do a proper query against the table without needing the
JOIN, but you also don't have to expose the getWhateverId() by
default.

What do you think? :)

- Elliott

[James Allen]

Hi Elliot,

The relationship between SearchOptions and User is really onetoone but
obviously we can't model this in Transfer which is why I have to use
manytoone. In this scenario User will always be unique.

I did start by making SearchOptions a part of the User object but this
doesn't really work for my application. SearchOptions need to persist in
Session (cloned Transfer objects) to allow users to change their setting and
only save them if they want to. There also needs to be a default
SearchOptions created which I found was a bit of a hassle when trying to
ensure a new User object had a new SearchOptions object inside it. I also
wanted to separate the SearchOptions from the user object so it would be
easy to move the system to another site which didn't have a user component
to it (just search).

Cheers,

James.

-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
On Behalf Of Elliott Sprehn
Sent: 30 August 2008 19:49
To: transfer-dev
Subject: [transfer-dev] Re: Retrieving an object based upon a composed
relationship