Re: Emulating left outer joins with JDOQL
On Friday 22 Jan 2016 19:25:24 Andy Jefferson wrote: > > > SELECT p.*, t.* FROM Person p > > > LEFT JOIN PersonTemplate pt ON p.id = pt.personId > > > LEFT JOIN Template t ON pt.templateId = t.id > > > > > > With this query, I can get Persons even if they don't have a Template. > > Which is what you would do if you followed the example I suggested, using > > JDOQL variables. > > > > SELECT p, t FROM mydomain.Person WHERE pt.person == this && pt.t == t > > VARIABLES mydomain.PersonTemplate pt, mydomain.Template t > > That should be > > SELECT p, t FROM mydomain.Person WHERE pt.person == this && pt.template == t > VARIABLES mydomain.PersonTemplate pt, mydomain.Template t Duh, and fixing the Person alias also that should be SELECT this, t FROM mydomain.Person WHERE pt.person == this && pt.template == t VARIABLES mydomain.PersonTemplate pt, mydomain.Template t but then its simple if you just try it. -- Andy DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus)
Re: Emulating left outer joins with JDOQL
> > SELECT p.*, t.* FROM Person p > > LEFT JOIN PersonTemplate pt ON p.id = pt.personId > > LEFT JOIN Template t ON pt.templateId = t.id > > > > With this query, I can get Persons even if they don't have a Template. > Which is what you would do if you followed the example I suggested, using > JDOQL variables. > > SELECT p, t FROM mydomain.Person WHERE pt.person == this && pt.t == t > VARIABLES mydomain.PersonTemplate pt, mydomain.Template t That should be SELECT p, t FROM mydomain.Person WHERE pt.person == this && pt.template == t VARIABLES mydomain.PersonTemplate pt, mydomain.Template t -- Andy DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus)
Re: Emulating left outer joins with JDOQL
> The query I'd like to perform is something like: > > SELECT p.*, t.* FROM Person p > LEFT JOIN PersonTemplate pt ON p.id = pt.personId > LEFT JOIN Template t ON pt.templateId = t.id > > With this query, I can get Persons even if they don't have a Template. Which is what you would do if you followed the example I suggested, using JDOQL variables. SELECT p, t FROM mydomain.Person WHERE pt.person == this && pt.t == t VARIABLES mydomain.PersonTemplate pt, mydomain.Template t > AFAIK you cannot return two types in a single query with JDOQL You can set a result clause in JDOQL can't you? So it's safe to conclude you can return multiple objects as well. Regards -- Andy DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus)
Re: Emulating left outer joins with JDOQL
AFAIK you cannot return two types in a single query with JDOQL You could contribute the template on the the person class: @DomainService(nature = NatureOfService.VIEW_CONTRIBUTIONS_ONLY) public class PersonTemplateContributions { @Action(semantics = SemanticsOf.SAFE) @ActionLayout(contributed = Contributed.AS_ASSOCIATION) public Template template( final Person person) { return personTemplateRepository.findByPerson(person); } @Inject private PersonTemplateRepository personTemplateRepository; } On 22 January 2016 at 09:47, Willie Loyd Tandingan wrote: > The query I'd like to perform is something like: > > SELECT p.*, t.* FROM Person p > LEFT JOIN PersonTemplate pt ON p.id = pt.personId > LEFT JOIN Template t ON pt.templateId = t.id > > With this query, I can get Persons even if they don't have a Template. > > On Fri, Jan 22, 2016 at 3:05 AM, Andy Jefferson > wrote: > > > > Person > > > - String name > > > > > > Template > > > - String name > > > - String field1 > > > - boolean otherBunchOfProperties > > > > > > PersonTemplate > > > - Person person > > > - Template template > > > > > I want to query all Persons along with their corresponding Template > > > through PersonTemplate. > > > Adding the Template property to Person is not an option. At the moment, > > > we resorted to generating a query per Person to check PersonTemplate > > > just for prototyping purposes.. but we really have to fix this soon. > > > > No idea why you'd contemplate JPQL, it is way less flexible than JDOQL, > > and always better to stick to Java syntax in Java code IMHO. > > > > You don't define the precise query wanted, but suggest that you start > from > > > > SELECT FROM mydomain.Person WHERE tmpl.person == this > > VARIABLES mydomain.PersonTemplate tmpl > > > > so you can then refer to your associated PersonTemplate object and its > > "template" field via "tmpl.template", and impose further constraints in > the > > WHERE clause. > > > > Should this generate SQL with a different JOIN type to what you would > > prefer then you can specify the join for the variable as per this page > > > > > > > http://www.datanucleus.org/products/accessplatform_4_2/datastores/rdbms_query.html > > > > Look for *datanucleus.query.jdoql.{varName}.join* on this page > > > > > > > > Regards > > -- > > Andy > > DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus) > > >
Re: Emulating left outer joins with JDOQL
The query I'd like to perform is something like: SELECT p.*, t.* FROM Person p LEFT JOIN PersonTemplate pt ON p.id = pt.personId LEFT JOIN Template t ON pt.templateId = t.id With this query, I can get Persons even if they don't have a Template. On Fri, Jan 22, 2016 at 3:05 AM, Andy Jefferson wrote: > > Person > > - String name > > > > Template > > - String name > > - String field1 > > - boolean otherBunchOfProperties > > > > PersonTemplate > > - Person person > > - Template template > > > I want to query all Persons along with their corresponding Template > > through PersonTemplate. > > Adding the Template property to Person is not an option. At the moment, > > we resorted to generating a query per Person to check PersonTemplate > > just for prototyping purposes.. but we really have to fix this soon. > > No idea why you'd contemplate JPQL, it is way less flexible than JDOQL, > and always better to stick to Java syntax in Java code IMHO. > > You don't define the precise query wanted, but suggest that you start from > > SELECT FROM mydomain.Person WHERE tmpl.person == this > VARIABLES mydomain.PersonTemplate tmpl > > so you can then refer to your associated PersonTemplate object and its > "template" field via "tmpl.template", and impose further constraints in the > WHERE clause. > > Should this generate SQL with a different JOIN type to what you would > prefer then you can specify the join for the variable as per this page > > > http://www.datanucleus.org/products/accessplatform_4_2/datastores/rdbms_query.html > > Look for *datanucleus.query.jdoql.{varName}.join* on this page > > > > Regards > -- > Andy > DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus) >