No, sub-queries in JoSQL aren't supported (and to be honest have little meaning).
However this kind of thing is easily done since the IN operator supports Map and Lists.
So potentially you could write your query as:
SELECT *
FROM Object1
WHERE id IN (:idsFromObject2)
Where "idsFromObject2" is a bind variable that holds a List of the ids from Object2.
Of course you would need to get the ids from Object2 somehow, maybe as a separate Query, or some other means.
So it would potentially be:
Query qObj2 = new Query ();
qObj2.parse ("SELECT object1.id FROM Object2");
List obj2Ids = qObj2.execute (obj2List).getResults ();
Query qObj1 = new Query ();
qObj1.parse ("SELECT * FROM Object1 WHERE id IN (?)");
qObj1.setVariable (1, obj2Ids);
QueryResults qr = qObj1.execute (obj1List);
A little more long winded but you get to the same end result...
Gary
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm trying to develop a graphical UI component to build a query string (which could have any level of nesting) dynamically and then pass it to JoSQL for parsing/execution. It'd have saved me work if the inner sql results were converted to map/list by JoSQL internally. Does this qualify as a feature request?
Regards,
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Could you give me a clearer idea of what you are trying to achieve?
The trouble is, is that looking at your query, it would make more sense to write it as:
SELECT object1
FROM Object2
WHERE id IN (<something>)
rather than trying to use the inner query...
In principle the inner-selects are possible but will require some re-work to the grammar and creation of a new expression type. Trouble is this is quite a bit of work and I have a "rule" that I only include features that have a "practical" purpose, i.e. are they gonna solve a real world problem... Hence my question about what you would like to achieve... I'm also a bit queasy about inner-selects in that it's then only a small step away from joins, which kind of negates the point of using objects!
So whilst it certainly does qualify as a feature request, I don't have a lot of time to to work on JoSQL (I have about 5 other things on the go at the same time!) so I need to be sure it's gonna be "worthwhile" (hate that word!)...
Alternatively of course you could do the changes yourself and contribute them back, I would then be happy to merge the changes (as long as they fit with the architecture)... One of the biggest challenges I can see is that any inner selects would need to have some kind of alias associated with them so that a List can be "assigned" to them, i.e.
SELECT * FROM Object1 obj1List WHERE id IN
( SELECT getObject1.id FROM Object2 obj2List)
Ther other major issue/hurdle I can see is that you would also need to support the use of bind variables, save values and accessors in the FROM clause of the inner-selects, something like: SELECT * FROM ? or SELECT * FROM myList (again a little tricky, especially since it would require quite a few grammar changes, or at least grammar duplication)...
Anyway, let me know!
Thanks,
Gary
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry for not getting back to you sooner - was out of office last week.
What I am trying to build is a generic query interface which will work on top of any interconnected object domain.
Background: My application has data beans which are assosciated to other data beans just like any application's object map. I also have metadata definition classes for all my beans (we've used the PropertyAccess pattern). I can plug in new data objects dynamically and the basic classes that manage and use entities in my application donot have to change.
Now - I've use the metadata to build a generic UI with any level of nesting, the UI allows a user to select an entity type and then the property of that entity, if the selected property is a reference to another entity (or a collection) then the UI allows me to select a property of THAT entity and so on. The design of the UI is not that important, finally what I get is a string of the form that I put in my initial question which could be potentially nested to any level.
I realize that expecting the resultant string to parse "by magic" is a bit too much but thats what I was hoping for. It may be a worthwhile feature if you had the time...
Thanks for your time so far.
Regards,
Sandeep
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've given this quite a bit of thought now, and to be honest it's been the most requested feature! (It's been requested 7-8 times now and it's needed for the polliwog project anyway).
So I think it will be something that I'll introduce, probably in version 1.3.
It will take the form (given your original query):
SELECT * FROM Object1 WHERE id IN
( SELECT getObject1.id FROM :myObjs )
Where the bind variable :myObjs is a list provided to the query, the class of the objects will be determined when the query is executed.
It will also be possible to have sub-queries on maps/lists from accessors in the "main" object, for example if Object1 had an accessor (getObjList) that returned a List.
SELECT *
FROM Object1
WHERE (SELECT *
FROM objList
WHERE id = ?)
The sub-query above will return "true" if the number of objects that matched the "id = ?" clause is greater than 0. BTW, this method will also work for maps. It will also be possible to do sub-query comparisons, thus:
SELECT *
FROM Object1
WHERE (SELECT * FROM :myObjs1 WHERE id = '1')
=
(SELECT * FROM objList WHERE id = '1')
In this case the results from both sides of the equals will be compared and "true" returned if they match.
I'll also introduce a new special bind variable ":_parent" that allows a sub-query get access to it's parent query.
I don't have a definite time-scale for 1.3 yet (still got to release 1.2!), but it won't be too long, as I say it's needed for polliwog anyway.
Good luck with the UI, it sounds interesting...
BTW, just a note on class-loading, if you plan to have your data objects be "reloaded" be aware that their class object won't be unloaded (at least not in Java 1.4) by the VM... It can lead to lots of pain, you tend to have to create new classloaders all the time but of course a class loaded by one classloader is completely different (to the VM) than one loaded by another classloader. This can lead to lots of strange class errors! Having being involved in a "plug-in" framework a few years ago that was one of the main problems we faced...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just thought I'd let you know that I've released version 1.2 now (didn't want to wait until version 1.3 for sub-queries), so it's now possible to perform those sub-queries.
So the following (your original query):
SELECT * FROM Object1 WHERE id IN
( SELECT getObject1.id FROM Object2 )
is possible, but "Object2" will need to be replaced with either a bind variable, save value, a function call or an accessor from Object1.
Gary
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just refreshed the page and posted my Message again :).
Thanks a lot for your answer and it definitely helps to solve my issue.
I have just a question concerning your first solution. Why should there be a difference in the way the subquery is built between an IN and a NOT IN statement?
SELECT * FROM Parent WHERE IN (SELECT * FROM children WHERE name = 'Santa') ; works fine and it is actually quite natural to try out the same with a NOT in front of the IN.
But anyway, thank you very much!
Regards,
Alex
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
No worries :) If you have any more queries just let me know.
Just on the sub-selects, one of my aims for JoSQL was to make it "javaish" (rather than being just straight SQL) to that end I wanted the queries to have some cognizance of some of the built-in Java types and make it easier to use. As such, when a sub-select is used in a WHERE clause, the org.josql.expressions.Expression.isTrue(Query) method is called to determine whether that expression is true, for sub-selects this means that if there is one or more values in the result then it will evaluate to "true". This is (hopefully) an intuitive result for a java programmer and thus means that you can write less SQL and still retain the semantics you are after, i.e.
SELECT *
FROM MyObject
WHERE (SELECT * FROM children WHERE x = :y)
Similarly with the "[NOT] IN" clause it understands about Lists and Maps so you can use those as well (and a sub-select will evaluate to a List which is why it all just "works).
Anyway, I hope that helps, if you need any more help just let me know.
Gary
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
Does the IN operator accept subqueries eg
SELECT * FROM Object1 WHERE id IN
( SELECT getObject1.id FROM Object2 )
assuming Object2 holds a reference to Object1 (available through the accessor getObject1() )
Thanks for your help.
Regards,
Hi there,
No, sub-queries in JoSQL aren't supported (and to be honest have little meaning).
However this kind of thing is easily done since the IN operator supports Map and Lists.
So potentially you could write your query as:
SELECT *
FROM Object1
WHERE id IN (:idsFromObject2)
Where "idsFromObject2" is a bind variable that holds a List of the ids from Object2.
Of course you would need to get the ids from Object2 somehow, maybe as a separate Query, or some other means.
So it would potentially be:
Query qObj2 = new Query ();
qObj2.parse ("SELECT object1.id FROM Object2");
List obj2Ids = qObj2.execute (obj2List).getResults ();
Query qObj1 = new Query ();
qObj1.parse ("SELECT * FROM Object1 WHERE id IN (?)");
qObj1.setVariable (1, obj2Ids);
QueryResults qr = qObj1.execute (obj1List);
A little more long winded but you get to the same end result...
Gary
Hi Barry,
Thanks for your reply.
I'm trying to develop a graphical UI component to build a query string (which could have any level of nesting) dynamically and then pass it to JoSQL for parsing/execution. It'd have saved me work if the inner sql results were converted to map/list by JoSQL internally. Does this qualify as a feature request?
Regards,
Hi again,
Could you give me a clearer idea of what you are trying to achieve?
The trouble is, is that looking at your query, it would make more sense to write it as:
SELECT object1
FROM Object2
WHERE id IN (<something>)
rather than trying to use the inner query...
In principle the inner-selects are possible but will require some re-work to the grammar and creation of a new expression type. Trouble is this is quite a bit of work and I have a "rule" that I only include features that have a "practical" purpose, i.e. are they gonna solve a real world problem... Hence my question about what you would like to achieve... I'm also a bit queasy about inner-selects in that it's then only a small step away from joins, which kind of negates the point of using objects!
So whilst it certainly does qualify as a feature request, I don't have a lot of time to to work on JoSQL (I have about 5 other things on the go at the same time!) so I need to be sure it's gonna be "worthwhile" (hate that word!)...
Alternatively of course you could do the changes yourself and contribute them back, I would then be happy to merge the changes (as long as they fit with the architecture)... One of the biggest challenges I can see is that any inner selects would need to have some kind of alias associated with them so that a List can be "assigned" to them, i.e.
SELECT * FROM Object1 obj1List WHERE id IN
( SELECT getObject1.id FROM Object2 obj2List)
Ther other major issue/hurdle I can see is that you would also need to support the use of bind variables, save values and accessors in the FROM clause of the inner-selects, something like: SELECT * FROM ? or SELECT * FROM myList (again a little tricky, especially since it would require quite a few grammar changes, or at least grammar duplication)...
Anyway, let me know!
Thanks,
Gary
Hi Gary,
Sorry for not getting back to you sooner - was out of office last week.
What I am trying to build is a generic query interface which will work on top of any interconnected object domain.
Background: My application has data beans which are assosciated to other data beans just like any application's object map. I also have metadata definition classes for all my beans (we've used the PropertyAccess pattern). I can plug in new data objects dynamically and the basic classes that manage and use entities in my application donot have to change.
Now - I've use the metadata to build a generic UI with any level of nesting, the UI allows a user to select an entity type and then the property of that entity, if the selected property is a reference to another entity (or a collection) then the UI allows me to select a property of THAT entity and so on. The design of the UI is not that important, finally what I get is a string of the form that I put in my initial question which could be potentially nested to any level.
I realize that expecting the resultant string to parse "by magic" is a bit too much but thats what I was hoping for. It may be a worthwhile feature if you had the time...
Thanks for your time so far.
Regards,
Sandeep
Hi Sandeep,
I've given this quite a bit of thought now, and to be honest it's been the most requested feature! (It's been requested 7-8 times now and it's needed for the polliwog project anyway).
So I think it will be something that I'll introduce, probably in version 1.3.
It will take the form (given your original query):
SELECT * FROM Object1 WHERE id IN
( SELECT getObject1.id FROM :myObjs )
Where the bind variable :myObjs is a list provided to the query, the class of the objects will be determined when the query is executed.
It will also be possible to have sub-queries on maps/lists from accessors in the "main" object, for example if Object1 had an accessor (getObjList) that returned a List.
SELECT *
FROM Object1
WHERE (SELECT *
FROM objList
WHERE id = ?)
The sub-query above will return "true" if the number of objects that matched the "id = ?" clause is greater than 0. BTW, this method will also work for maps. It will also be possible to do sub-query comparisons, thus:
SELECT *
FROM Object1
WHERE (SELECT * FROM :myObjs1 WHERE id = '1')
=
(SELECT * FROM objList WHERE id = '1')
In this case the results from both sides of the equals will be compared and "true" returned if they match.
I'll also introduce a new special bind variable ":_parent" that allows a sub-query get access to it's parent query.
I don't have a definite time-scale for 1.3 yet (still got to release 1.2!), but it won't be too long, as I say it's needed for polliwog anyway.
Good luck with the UI, it sounds interesting...
BTW, just a note on class-loading, if you plan to have your data objects be "reloaded" be aware that their class object won't be unloaded (at least not in Java 1.4) by the VM... It can lead to lots of pain, you tend to have to create new classloaders all the time but of course a class loaded by one classloader is completely different (to the VM) than one loaded by another classloader. This can lead to lots of strange class errors! Having being involved in a "plug-in" framework a few years ago that was one of the main problems we faced...
Hi Barry,
Thats great! Will wait for 1.3 anxiously :) Thnx for the pointers about classloaders and loaded classes - will keep in mind.
Regards,
Sandeep
Hi Sandeep,
Just thought I'd let you know that I've released version 1.2 now (didn't want to wait until version 1.3 for sub-queries), so it's now possible to perform those sub-queries.
So the following (your original query):
SELECT * FROM Object1 WHERE id IN
( SELECT getObject1.id FROM Object2 )
is possible, but "Object2" will need to be replaced with either a bind variable, save value, a function call or an accessor from Object1.
Gary
Thanks a bunch Barry - this'll certainly make my life easier :)
Regards,
Sandeep
Hi there,
First, let me thank you for JoSQL. It's wonderful :) !
I'm using sub queries like:
SELECT * FROM Parent WHERE (SELECT * FROM children WHERE name = 'Santa') ;
My question: Can I express negation? Something like:
SELECT * FROM Parent WHERE NOT (SELECT * FROM children WHERE name = 'Santa') ;
The SQL equivalent to the above is:
SELECT * FROM Parent WHERE parentname NOT IN (SELECT parentname FROM children WHERE name = 'Santa')
I would like to get all the parents that have no child called "Santa".
Thank you for your help.
Regards,
Alex
Hi Alex,
There are 2 ways to tackle this, depending upon your preference.
The inner select needs to be wrapped in ( and ), and so if you use the IN or NOT IN operators then it would look like:
SELECT * FROM Parent WHERE parentname NOT IN ((SELECT parentname FROM children WHERE name = 'Santa'))
An alternative approach is to use an accessor from the list generated by the inner select, thus:
SELECT * FROM Parent WHERE (SELECT parentname FROM children WHERE name = 'Santa').size = 0
The 2nd approach is a little more flexible because you can then use operators and a bind variable to get minimum sizes etc.
Hope this helps,
Gary
Hi there,
First, let me thank you for JoSQL. It's wonderful :) !
I'm using sub queries like:
SELECT * FROM Parent WHERE (SELECT * FROM children WHERE name = 'Santa') ;
My question: Can I express negation? Something like:
SELECT * FROM Parent WHERE NOT (SELECT * FROM children WHERE name = 'Santa') ;
The SQL equivalent to the above is:
SELECT * FROM Parent WHERE parentname NOT IN (SELECT parentname FROM children WHERE name = 'Santa')
I would like to get all the parents that have no child called "Santa".
Thank you for your help.
Regards,
Alex
Ooo sorry Gary,
I just refreshed the page and posted my Message again :).
Thanks a lot for your answer and it definitely helps to solve my issue.
I have just a question concerning your first solution. Why should there be a difference in the way the subquery is built between an IN and a NOT IN statement?
SELECT * FROM Parent WHERE IN (SELECT * FROM children WHERE name = 'Santa') ; works fine and it is actually quite natural to try out the same with a NOT in front of the IN.
But anyway, thank you very much!
Regards,
Alex
Hi again,
I've just read the message more carefully. There is of course no discrepancy between the two cases - IN and NOT IN.
Once again, thank you very much!
It is really wonderful to get a reply so quickly!
Regards.
Alex
Hi Alex,
No worries :) If you have any more queries just let me know.
Just on the sub-selects, one of my aims for JoSQL was to make it "javaish" (rather than being just straight SQL) to that end I wanted the queries to have some cognizance of some of the built-in Java types and make it easier to use. As such, when a sub-select is used in a WHERE clause, the org.josql.expressions.Expression.isTrue(Query) method is called to determine whether that expression is true, for sub-selects this means that if there is one or more values in the result then it will evaluate to "true". This is (hopefully) an intuitive result for a java programmer and thus means that you can write less SQL and still retain the semantics you are after, i.e.
SELECT *
FROM MyObject
WHERE (SELECT * FROM children WHERE x = :y)
Similarly with the "[NOT] IN" clause it understands about Lists and Maps so you can use those as well (and a sub-select will evaluate to a List which is why it all just "works).
Anyway, I hope that helps, if you need any more help just let me know.
Gary