Simple query Expression question

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple query Expression question

Nenko Ivanov
Hi, as maybe you will understand from my question I'm a newbie in Cayenne.
But last few hours I was struggling with building a simple table join
expression, without any success.

So the SQL looks like that

SELECT col1... colN FROM table_name WHERE
col = <some_value>
AND col2 = col3

I can not make the join between col2 and col3. This is a child-parrent
relationship and in this significant
case I have to fetch the root of this tree structure. (this query always
returns one row)

First join is done by this
  Expression expression = ExpressionFactory.matchDbExp(ClassName.PROPERTY,
        "<some_value>");
      SelectQuery sq = new SelectQuery(ClassName.class,
        expression);
and what is the next step for the other join..

sq.addPrefetch("<relationship_path>");

builds query like this:

SELECT col1... colN FROM table_name a,table_name b WHERE
a.col = <some_value>
AND a.col2 = b.col3

because I have a relation between the required colums for joining.

Thanks in advance for your help.

PS. I was searching a lot for answer before bothering you with this stuff.

Reply | Threaded
Open this post in threaded view
|

Re: Simple query Expression question

Mike Kienenberger
Nenko,

I don't have time to answer you in depth right now, but you should be
able to do this just by setting up a relationship on the object that
points back to the object.

Assuming the following:

OBJECT_ID (primary key)
PARENT_OBJECT_ID

You would build a relationship name "parent" such that
PARENT_OBJECT_ID = OBJECT_ID in your model.

Then to use it in your code, you'd do something like

Expression expression1 =
ExpressionFactory.matchDbExp(ClassName.PROPERTY, "<some_value>");

Expression expression2 =
ExpressionFactory.matchExp(ClassName.PARENT_PROPERTY, parentObject);

Expression expression = expression1.andExp(expression2 );


On 8/31/05, Nenko Ivanov <[hidden email]> wrote:

> Hi, as maybe you will understand from my question I'm a newbie in Cayenne.
> But last few hours I was struggling with building a simple table join
> expression, without any success.
>
> So the SQL looks like that
>
> SELECT col1... colN FROM table_name WHERE
> col = <some_value>
> AND col2 = col3
>
> I can not make the join between col2 and col3. This is a child-parrent
> relationship and in this significant
> case I have to fetch the root of this tree structure. (this query always
> returns one row)
>
> First join is done by this
>   Expression expression = ExpressionFactory.matchDbExp(ClassName.PROPERTY,
>         "<some_value>");
>       SelectQuery sq = new SelectQuery(ClassName.class,
>         expression);
> and what is the next step for the other join..
>
> sq.addPrefetch("<relationship_path>");
>
> builds query like this:
>
> SELECT col1... colN FROM table_name a,table_name b WHERE
> a.col = <some_value>
> AND a.col2 = b.col3
>
> because I have a relation between the required colums for joining.
>
> Thanks in advance for your help.
>
> PS. I was searching a lot for answer before bothering you with this stuff.
>
>