Re: Emulating left outer joins with JDOQL

[Andy Jefferson]

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

[Andy Jefferson]

> 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

[Andy Jefferson]

> > 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

[Willie Loyd Tandingan]

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

[Jeroen van der Wal]

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)
> >
>