Jacob,
Please disregard my last message. I figured out what my problem was. I was
not declaring the reverse-foreignkey correctly.
That said, I still see what seems to be some anomalies in the query
generation.
For instance, if I run a report query and specify the following list as an
attribute list, using different aliases in that list, I get the correct
projection list in the SQL query (A1.reference_name,A2.reference_name),
which extracts the same column from 2 different rows satisfying different
criteria on the same table:
q.setAttributes(new String[]{
"HASVIEW.references_.referenceName_","PROPERTIES.references_.referenceName_"
});
SQL: SELECT A1.reference_name,A2.reference_name FROM EObject A0 INNER JOIN
EReferenceValue A1 ON A0.repository_id=A1.container_repository_id INNER JOIN
EReferenceValue A2 ON A0.repository_id=A2.container_repository_id WHERE (
A1.feature_id = ?) AND (A2.feature_id = ?)
Now, if I add one extra level of navigation in the path expression in my
attribute list, as shown below, the generated SQL query is wrong, as the
same column is accessed twice in the SQL projection
(A4.database_id,A4.database_id):
q.setAttributes(new String[]{
"HASVIEW.references_.referenceList_.databaseID_","PROPERTIES.references_.ref
erenceList_.databaseID_"});
SQL:SELECT A4.database_id,A4.database_id FROM EObject A0 INNER JOIN
EReferenceValue A1 ON A0.repository_id=A1.container_repository_id INNER JOIN
EReferenceValue A2 ON A0.repository_id=A2.container_repository_id INNER JOIN
EReferenceValue A3 ON A0.repository_id=A3.container_repository_id INNER JOIN
EReferenceListElement A4 ON A3.database_id=A4.database_id WHERE (
A1.feature_id = ?) AND (A2.feature_id = ?)
I was expecting to get something like the following query instead:
SQL:SELECT A3.database_id,A5.database_id FROM EObject A0 INNER JOIN
EReferenceValue A1 ON A0.repository_id=A1.container_repository_id INNER JOIN
EReferenceValue A2 ON A0.repository_id=A2.container_repository_id INNER JOIN
EReferenceListElement A3 ON A2.database_id=A3.database_id INNER JOIN
EReferenceValue A4 ON A0.repository_id=A4.container_repository_id INNER JOIN
EReferenceListElement A5 ON A4.database_id=A5.database_id WHERE (
A1.feature_id = ?) AND (A2.feature_id = ?)
Would it be wrong to expect something like the query above?
Thanks,
- Cassio
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 11:54 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: regression with use of alias in ReportQuery setAttributes?
Hi,
The new test passes fine and most of my queries seem to work fine too. I'm
having a problem to access a field of a given class using a report query
attribute list. This doesn't seem to have anything to do with aliases.
The field is mapped as a foreign key (reference-descriptor), and instead of
having the generated SQL query accessing that field of the object at the end
of a traversal path, as it is the case for the other fields of the same
object that I access using the same path in the same query, I have the
primary key of the object just before that last object in the traversal
path, as declared in the report query attribute list (see code extract
below).
Criteria crit1 = new Criteria();
crit1.addEqualTo("references_.featureID_", new Integer(11));
ReportQueryByCriteria q =
QueryFactory.newReportQuery(PersistentEObject.class, crit1);
q.setAttributes(new String
{"HASVIEW.references_.referenceObject_.className_",
"HASVIEW.references_.referenceObject_.repositoryID_"});
The produced query is:
SQL:SELECT A2.class_name,A2.repository_id FROM EObject A0 INNER JOIN
EReferenceValue A1 ON A0.repository_id=A1.container_repository_id INNER JOIN
EObject A2 ON A1.database_id=A2.repository_id WHERE A1.feature_id = ?
The problem I have is that the reference-descriptor for referenceObject_ is
not supposed to refer to the primary key (database_id) of the pointed table
but to another column which is not a key. Is that possible?
I see it works fine for collections (through inverse-foreignkey) but I can't
see how I can make it work for a 1-1 reference.
Thanks,
- Cassio
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 07, 2004 2:23 PM
To: OJB Users List; [EMAIL PROTECTED]
Subject: Re: regression with use of alias in ReportQuery setAttributes?
hi cassio,
there's a testcase QuerTest#testReportQueryAlias that uses aliases to choose
columns. i added "NAMES.allArticlesInGroup.articleName" to have the same
column
twice.
Criteria crit1 = new Criteria();
crit1.setAlias("NAMES");
crit1.addLike("upper(allArticlesInGroup.articleName)", "F%");
Criteria crit2 = new Criteria();
crit2.setAlias("STOCKS");
crit2.addGreaterOrEqualThan("allArticlesInGroup.stock", new Integer(110));
crit1.addAndCriteria(crit2);
ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class,
crit1);
q.setAttributes(new String[]{
"groupId","groupName",
"STOCKS.allArticlesInGroup.articleName",
"NAMES.allArticlesInGroup.articleName", "NAMES.allArticlesInGroup.stock"});
Iterator iter = broker.getReportQueryIteratorByQuery(q);
the generate sql is as follows, and imo the aliases are correctly resolved
A2.Artikelname,A1.Artikelname,A1.Lagerbestand
SELECT
A0.Kategorie_Nr,A0.KategorieName,A2.Artikelname,A1.Artikelname,A1.Lagerbesta
nd
FROM Kategorien A0 LEFT OUTER JOIN Artikel A1 ON
A0.Kategorie_Nr=A1.Kategorie_Nr
LEFT OUTER JOIN BOOKS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr LEFT OUTER
JOIN
CDS A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr LEFT OUTER JOIN Artikel A2 ON
A0.Kategorie_Nr=A2.Kategorie_Nr LEFT OUTER JOIN BOOKS A2E1 ON
A0.Kategorie_Nr=A2E1.Kategorie_Nr LEFT OUTER JOIN CDS A2E2 ON
A0.Kategorie_Nr=A2E2.Kategorie_Nr WHERE ( (upper(A1.Artikelname) LIKE 'F%'
OR
upper(A1E1.Artikelname) LIKE 'F%' OR upper(A1E2.Artikelname) LIKE 'F%')) AND
((A2.Lagerbestand >= '110' OR A2E1.Lagerbestand >= '110' OR
A2E2.Lagerbestand >=
'110'))
jakob
J.Braeuchi schrieb:
> Hi Jacob,
>
> I apologize for sending this directly to you. I tried to send it to the
ojb
> users list but it returned with an error.
> I tried your fix (files SqlQueryStatement.java and
SqlSelectStatement.java,
> is there anything else I'm missing?).
>
> I don't get the "undefined alias" error message anymore but I don't always
> get the right query result. I'm giving some code extract and the generated
> SQL query below. My report query extracts different attribute values using
> different aliases but in the generated SQL, the values returned for the 2
> different attribute extractions are exactly the same (A3.
> reference_object_repository_id), and the join conditions don't look
> right to
> me. I hope the code extract below will allow you to see where the problem
> is, let me know otherwise.
>
> If I change the path expressions used in the call to method
> setAttributes as
> follows, then I get the right SQL query and result. Am I doing something
> wrong?
>
> q.setAttributes(new String[]{"repositoryID_", "className_",
>
"HASVIEW.references_.referenceObjectRepositoryID_","PROPERTIES.references_.r
>
> eferenceObjectRepositoryID_"});
> Thanks,
>
> - Cassio
>
> ArrayList list = new java.util.ArrayList();
>
> Criteria crit1 = new Criteria();
> crit1.setAlias("HASVIEW");
> crit1.addEqualTo("references_.featureID_", new Integer(11));
>
> Criteria crit2 = new Criteria();
> crit2.setAlias("PROPERTIES");
> crit2.addEqualTo("references_.featureID_", new Integer(17));
>
> crit1.addAndCriteria(crit2);
> ReportQueryByCriteria q =
> QueryFactory.newReportQuery(PersistentEObject.class, crit1);
> q.setAttributes(new String[]{"repositoryID_", "className_",
>
"HASVIEW.references_.referenceObjectRepositoryID_","PROPERTIES.references_.r
>
> eferenceObjectRepositoryID_"});
>
>
> GENERATED SQL:SELECT
>
A0.repository_id,A0.class_name,A3.reference_object_repository_id,A3.referenc
>
> e_object_repository_id FROM EObject A0 INNER JOIN EReferenceValue A1 ON
> A0.repository_id=A1.container_repository_id INNER JOIN EReferenceValue
> A2 ON
> A0.repository_id=A2.container_repository_id INNER JOIN EReferenceValue
> A3 ON
> A0.repository_id=A3.container_repository_id WHERE ( A1.feature_id = ?) AND
> (A2.feature_id = ?)
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
