How to specify a two step delegation in Glorp?

[Georg Heeg]

I hope the specialists have an idea right away, I don't.

This is the situation:

I have a database with three tables:

1. Person with  primaryKey and String attributes firstname and lastname and reference to the second table Company
2. Company with primaryKey and Sting attribute ccompanyname and reference to the third table Adress
3. Address with primaryKey and String attributes postCode, city and street

The goal is to a psudo variable postCode in person to (indirectly) access the attribute postCode in Address.

Certainly I can execute the following query:

session read: Person where: [:person | person company address postCode = '44227']

Glorp creates the following SQL statement:

'SELECT t1.primaryKey, t1.firstName, t1.lastName, t1.company FROM ((person t1 INNER JOIN company t2 ON (t1.company = t2.primaryKey)) INNER JOIN address t3 ON (t2.address = t3.primaryKey)) WHERE (t3.postCode = :1)ByteString'

And is works.

I can add a pseudo variable to Company with this code fragment in the descriptor message:

addressTable := self tableNamed: 'address'.
(aDescriptor directToOneMapping)
attributeName: #postCode referenceClass: String;
resultField: (addressTable
fieldNamed: 'postCode');
join: (Join from: (table fieldNamed: 'address')
to: (addressTable fieldNamed: 'primaryKey'));
beForPseudoVariable

And I can simplify the query to

session read: Person where: [:person | person company postCode = '44227']

Glorp generates the same SQL and it works perfectly.

But how would I specify a pseudo variable in Person that I can write?

session read: Person where: [:person | person postCode = '44227']

I tried different things (other mappings, other queries inclduing a block), but nothing works.

[Alan Knight]

Hi Georg!

I'm not sure I even qualify as an expert any more. But that should be very doable. 

I think you should be able to specify a block as a join for a relationship. i.e. [:person | person company postCode]. But I would also suggest looking at the test cases to see if you can find any mappings that look like what you want. There's an old thread that seems sort of relevant, but not entirely... http://forum.world.st/Query-with-pseudoVariable-fails-hwne-joining-another-table-td5104343.html

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/257b3841-c94e-47d8-a1b6-ccc9253a88b9n%40googlegroups.com.

[Georg Heeg]

Alan,

Thank you for your hint. This is what I tried:

addressTable := self tableNamed: 'address'.

personDescriptor := self descriptorFor: Heeg.Person.
personAddressJoin := [:person | person company address]
asGlorpExpressionOn: (BaseExpression new descriptor: personDescriptor).

(aDescriptor directToOneMapping)
attributeName: #postCode referenceClass: String;
resultField: (addressTable fieldNamed: 'postCode');

join: personAddressJoin;
beForPseudoVariable

With one fix in Glorp.SimpleQuery>>fixJoins:

fixJoins

| pseudoJoins realJoins |
joins := joins reject: [:each | each isMemberOf: MappingExpression]. "<<<<<<<< Added Line"
pseudoJoins := joins select: [:each | each tablesForANSIJoin size < 2].
pseudoJoins do: [:each | whereClause := each AND: whereClause].
realJoins := joins select: [:each | each tablesForANSIJoin size >= 2].
joins := realJoins

I finally get a SQL statement (and an error from the database):

'SELECT t1.primaryKey, t1.firstName, t1.lastName, t1.company

 FROM ((person t1 INNER JOIN company t3 ON (t1.company = t3.primaryKey)) INNER JOIN address t4 ON (t3.address = t4.primaryKey)), person t2
 WHERE (address.postCode = :1)'

I think the last line should read t4.postCode instead.

Do you have another great idea. As this is just a text code based on a test database, I can share it.

[Alan Knight]

Hmm. One thought is just to leave the block as is, not turn it into a glorp expression. But it's a long shot.

One of the things that's a bit ugly in glorp is rebuilding expressions. So there's a base expression and everything in the expression tree should be built off that. And it relies on identity to distinguish the tables. But sometimes when you're building the tree, it relies on information you may not have yet, e.g. if the base expression does not yet have a descriptor associated with it. So you can get order dependencies.

So if you see an un-aliased table like that it probably means that the table reference comes from a different base expression. So the party that walks the tree and assigns aliases doesn't see it.

Unfortunately I'm not even set up to run glorp code at the moment since the demise of vwnc. I could set up squeak/pharo, but I haven't.

To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/bddad779-6f68-4dfa-88f9-0444e6c7a576n%40googlegroups.com.

[Georg Heeg]

We have experienced more for a while and found out that using blocks as join does not work well at all (at least we could not get any example working. But the method multipleTableJoin: does the trick. Here is the complete method.

descriptorForPerson: aDescriptor

| personTable addressTable companyTable |
personTable := aDescriptor table: 'person'.
addressTable := aDescriptor table: 'address'.
companyTable := aDescriptor table: 'company'.
(aDescriptor newMapping: DirectMapping) from: #primaryKey
to: (personTable fieldNamed: 'primaryKey').
(aDescriptor newMapping: DirectMapping) from: #firstName
to: (personTable fieldNamed: 'firstName').
(aDescriptor newMapping: DirectMapping) from: #lastName
to: (personTable fieldNamed: 'lastName').
(aDescriptor newMapping: OneToOneMapping)
attributeName: #company;
join: (Join from: (personTable fieldNamed: 'company')
to: ((self tableNamed: 'company') fieldNamed: 'primaryKey')).
aDescriptor
addMultipleTableJoin: (Join from: (personTable fieldNamed: 'company')
to: (companyTable fieldNamed: 'primaryKey')).
aDescriptor
addMultipleTableJoin: (Join from: (companyTable fieldNamed: 'address')
to: (addressTable fieldNamed: 'primaryKey')).
(aDescriptor newMapping: DirectMapping)
from: #postCode to: (addressTable fieldNamed: 'postCode');
beForPseudoVariable.
(aDescriptor newMapping: OneToOneMapping)
attributeName: 'address';
referenceClass: Heeg.Address;
beForPseudoVariable

[Esteban Maringolo]

Hi George,

Thanks for sharing your findings, I wasn't aware of the #addMultipleTableJoin: method.

I guess that the join you define is an LEFT OUTER JOIN, isn't it.

Best regards,

Esteban A. Maringolo

To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/72c511d3-b397-4c00-b0c5-51861b6995b1n%40googlegroups.com.

[Alan Knight]

Ah, very good.

I find it peculiar that using a block for the join isn't working for you. It's definitely intended to, and all that should happen is that when it encounters one it sends it the asGlorpExpression... method of one form or another. And it has (maybe) the correct base expression to build it on at that point. And in general, if converting to an expression is deferred until the descriptor is known, then it avoids certain sorts of tricky errors (where the expression would be built differently depending on the descriptor). 

To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/72c511d3-b397-4c00-b0c5-51861b6995b1n%40googlegroups.com.