Querying related objects directly

[Esteban A. Maringolo]

Also: Worst mailing list subject ever. :)

I have a complex, or not so, composed object relationship, and I want to read elements of the lower level of the relation, filtering by the top level.

I have the following relations

ObjectA 1:n ObjectB

ObjectB 1:n ObjectC

ObjectC 1:n ObjectD

ObjectE 1:1 ObjectD

In the pristine ORM-free world, all objects know how to answer the instance of ObjectA to which each one belongs/references.

Ej, 

ObjectE>>#objectA, ask for #objectA to anObjectD, and anObjectD ask for its #objectA to its parent, and the same up to ObjectB, until you get the answer which is the root of the whole tree, anObjectA.

What I want is to perform a query reading instances of ObjectE filtering only those "belonging" to ObjectA.

Something like:

| query |

query := Query read: ObjectE where: [ :each | each objectA = anObjectA].

In my ObjectE class model there is no reference nor mapping to #objectA, nor there is a foreign key in ObjectE table, because to get to ObjectA table you can joining all references together.

I'm sure this is doable, I just don't know how to do it. 

Best regards!

--

Esteban.

[Alan Knight]

I don't have code here right now, but what happens if you do

(Query read: ObjectA where: [:each | each = someA])

   retrieve: [:a | a b c d e]

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to glorp-group...@googlegroups.com.
To post to this group, send email to glorp...@googlegroups.com.
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

[Esteban A. Maringolo]

Hi Alan,

I still don't know how to use #retrieve: but doing as you suggested
helped me reach the lowest level (ObjectD), but not ObjectE, because I
was wrong in my description, and ObjectD and ObjectE relation is not
1:1.

Putting everything in a the real context:

SurveyDefinition (ObjectA)
\_ 1:n CategorySurvey (ObjectB)
\_1:n SurveySection (ObjectC)
\_ 1:n SurveyQuestion (ObjectD)

In other hierarchy of equally composed objects, I have the "results"
for each SurveyDefinition.
SurveyResult (refs SurveyDefinition)
\_1:n CategorySurveyResult (refs CategorySurvey)
\_ 1:n SurveySectionResult (refs SurveySection)
\_ 1:n QuestionAnswer (refs SurveyQuestion)

All the above described 1:n relations are bidirectional, it is, all
levels answer it's parent when receive #owner.
And I can send #survey to any of the described classes, and they will
delegate it's resolution until they reach an object with the direct
reference to SurveyDefinition, it is SurveyResult or CategorySurvey.

If I instantiate the whole result in memory, I can do whatever I like with them.

What I'm trying is to narrow the results, querying only certain
QuestionAnswer's according to some values, but only belonging to a
particular SurveyDefinition.

E.g.:
query := Query read: QuestionAnswer where: [:each | each value > 10
AND: [each survey = aSurveyDefinition]].

Because I'm only interested in a small subset of answers (<5%), I have
thousands of QuestionAnswers, there is no point in loading the
remaining 95% just to be discarded, with all the CPU and I/O time it
means.

Also SurveyQuestion is a whole hierarchy of classes, sharing a common
table, but using a FilteredTypeResolver.

I hope it is more clear now.


There is a quick "solution" for this that I'm trying to avoid:
I can add a survey_id field to QuestionAnswer's table, and declare a
OneToOneMapping in QuestionAnswer.
Maybe it's the best, and the fastest too. But I try to unify
references as much as I can.


Regards,

--
Esteban.

> You received this message because you are subscribed to a topic in the
> Google Groups "glorp-group" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/glorp-group/KRcO5pdt2Zw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Alan Knight]

OK, sounds like you need to express it slightly differently, but if you do have the owner relationship and the answer->question relationship defined that should do it.

query := Query read: QuestionAnswer where: [:each | each value > 10 

AND: [each question section survey definition = aSurveyDefinition]].

Or maybe that's just [each question owner owner owner = aSurveyDefinition], depending what you called the relationship.

Also note that you can define relationships like that so that they never materialize in memory, they're only usable as query values, by defining both shouldRead: and shouldWrite: as false.

[Esteban A. Maringolo]

2014-10-16 18:40 GMT-03:00 Alan Knight <kni...@acm.org>:
> OK, sounds like you need to express it slightly differently, but if you do
> have the owner relationship and the answer->question relationship defined
> that should do it.
>
> query := Query read: QuestionAnswer where: [:each | each value > 10
> AND: [each question section survey definition = aSurveyDefinition]].
>
> Or maybe that's just [each question owner owner owner = aSurveyDefinition],
> depending what you called the relationship.

Please apologize I forgot to mention, but before starting this
discussion I tried using the second option and it works (as expected).
The issue is I that all objects who "are part" of another object (in
an exclusive relation) inherit from ModelPart, and all the mappings
and attributes are defined using a set of utilily methods, which in a
search of convention over configuration share a common protocol of
#owner/#owner: and an owner_id field in their respective tables.

But for a better readability now I see your example I'm considering
renaming the attributes to have a more "semantic" meaning.

That's why I asked if there was a "direct" way of reaching the top object.

> Also note that you can define relationships like that so that they never
> materialize in memory, they're only usable as query values, by defining both
> shouldRead: and shouldWrite: as false.

I don't have any #shouldRead:/#shouldWrite: methods in the Pharo version :-/
There is however a #beForPseudoVariable in GlorpAttributeModel, which
sets both canRead and canWrite to false.

So my question again is if is it possible and then how can I define
such transitive relation in my Descriptor and/or ClassModel in order
to perform:

query := Query read: QuestionAnswer where: [:each | each value > 10

AND: [each definition = aSurveyDefinition]].


Thanks for all your support.


Esteban A. Maringolo

[Alan Knight]

Yes, it's possible, but it does start to make the brain hurt. Or at least they make my brain hurt, having been away from the code for a while, and not even having a database running on this machine. So you can't really express a join that's got multiple clauses directly, but you can write an expression that does that. So I think it might work if you just express the join of a one to one mapping as [:each | each owner owner owner] and make it beForPseudoVariable. I think... But I don't see any examples of it in the tests, which is usually not a good sign for something working properly :-) 

[Esteban A. Maringolo]

Don't worry, I ended up adding a #survey and 'survey_id' columns to
the Answer object. Which gave me an improvement of one order of
magnitude over my previous code. Next iteration might do something
different, now everything works faster than user expectations. :)

GLORP is so full of features, and everything seems so intricate that's
easy to get lost in the code.
However it works, and it does really fast. Sometimes I ask too much of it :)

Regards!



Esteban A. Maringolo

> You received this message because you are subscribed to a topic in the
> Google Groups "glorp-group" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/glorp-group/KRcO5pdt2Zw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Esteban A. Maringolo]

2014-10-20 0:35 GMT-03:00 Alan Knight <kni...@acm.org>:
> Yes, it's possible, but it does start to make the brain hurt. Or at least
> they make my brain hurt, having been away from the code for a while, and not
> even having a database running on this machine. So you can't really express
> a join that's got multiple clauses directly, but you can write an expression
> that does that. So I think it might work if you just express the join of a
> one to one mapping as [:each | each owner owner owner] and make it
> beForPseudoVariable. I think... But I don't see any examples of it in the
> tests, which is usually not a good sign for something working properly :-)

I'm coming back to this because being able to dynamically add a
condition to the where clause would save the retrieval of thousands of
useless objects.

What you mean when you say "express the join of a 1:1 mapping" using a
block (instead of a Join instance).
I can see Join's #asGlorpExpression returns self, and I get lost in
its polymorphism with GlorpExpression.

If I understood what you meant you say that in my class descriptor I
could do something similar to this (which doesn't works)

(aDescriptor newMapping: OneToOneMapping)
attributeName: #survey;
referenceClass: SurveyDefinition;
join: [:each | each owner owner owner];
beForPseudoVariable

If something like this could be done it would be really powerful.

In my example case I know in advance the filtering condition
(#survey), but in the context of a dynamically defined filter (which
I'm working on), it would be great to translate those filters to
GlorpExpressions without having to define a column in my filtered
object, delegating everything to the already defined relations.

Regards!

Esteban A. Maringolo

[Alan Knight]

Yes, that's what I meant. A join is in some ways just a degenerate expression, and you should be able to use an arbitrary expression in its place.

Esteban A. Maringolo

[Esteban A. Maringolo]

"degenerate expression" :)

Well... it seems I'm being too degenerated, because the following doesn't work.

Should the join be defined as follows?

(aDescriptor newMapping: OneToOneMapping)
attributeName: #survey;
referenceClass: SurveyDefinition;

join: [:each | each owner owner owner] asGlorpExpression;
beForPseudoVariable


Esteban A. Maringolo

> You received this message because you are subscribed to a topic in the
> Google Groups "glorp-group" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/glorp-group/KRcO5pdt2Zw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Henrik Johansen]

> On 20 Oct 2014, at 11:56 , Esteban A. Maringolo <emari...@gmail.com> wrote:
>
>
> In my example case I know in advance the filtering condition
> (#survey), but in the context of a dynamically defined filter (which
> I'm working on), it would be great to translate those filters to
> GlorpExpressions without having to define a column in my filtered
> object, delegating everything to the already defined relations.
>
> Regards!
>
> Esteban A. Maringolo

Not sure it's exactly what you're looking for, but I recently wrote a query containing a subquery like this:

session read: A where: [:a | a c in: ((Query read: B) retrieve: #d)]

which (, while bad form for performance/SQL aficionados,) worked like a charm at my main goal of filtering A's without loading B's from the database.

Cheers,
Henry

[Esteban A. Maringolo]

Helo Henrik,

2014-10-21 7:21 GMT-03:00 Henrik Johansen <henrik.s...@veloxit.no>:

> Not sure it's exactly what you're looking for, but I recently wrote a query containing a subquery like this:
>
> session read: A where: [:a | a c in: ((Query read: B) retrieve: #d)]

It's not what I'm looking for, but I'm starting to do extensive use of
subselects, no only using IN() but also EXISTS/NOT EXISTS. It's a
powerful feature.

What I'm trying to achieve these days is the dynamic construction of
glorp expressions, using dynamically generated filters which already
work at the object level using object metadata (it is, Magritte). With
no success so far :)

> which (, while bad form for performance/SQL aficionados,) worked like a charm at my main goal of filtering A's without loading B's from the database.

Not always, some databases have a really good optimization for the IN
clause, with similar explain plans as using an EXISTS subquery.



Esteban A. Maringolo

[Alan Knight]

For an example of dynamically constructing expressions you can look at the VW StoreReplicationManager, which does that based on menu drop downs. One thing that's convenient is that you can just construct individual queries and AND: the query objects together.

A long time ago I took a look at generating Glorp information from Magritte, but at least at that time, Magritte didn't provide enough information. It only distinguished things that were important for presentation, which wasn't enough for persistence. I could go from Glorp to Magritte, but not vice versa. But that was also a long time ago, and things may be different.

[Alan Knight]

OK, it's possibly a little more complicated than that. I actually fired up an image. So the expression needs to be from the reverse direction as you might expect, see the comment in RelationshipMapping join: So I can write, in the Store schema, something for StoreBundle like

   (aDescriptor newMapping: ToManyMapping)

        attributeName: #descendants;

        referenceClass: StoreBundle;

        beForPseudoVariable;

join: (Join from: (table fieldNamed: 'primaryKey')

to: (table fieldNamed: 'trace')).

      (aDescriptor newMapping: OneToOneMapping)

          attributeName: #grandParent;

          referenceClass: StoreBundle;

          beForPseudoVariable; 

          join: [:ancestor | ancestor descendants].

That gets as far as attempting to print the SQL statement for me and then it isn't printing properly into ANSI joins. I hate ANSI joins. So I'm not sure what's going on there, and don't have time to look further right now.