Re: Emulating left outer joins with JDOQL

[Dan Haywood]

Could you copy all the relevant code so that I can write this up as an faq
?
On 28 Jan 2016 17:17, "Willie Loyd Tandingan" 
wrote:

> It worked! "datanucleus.query.jdoql.{varName}.join" had to be set to
> OUTERJOIN for both pt and t. Thanks!
>
> On Sun, Jan 24, 2016 at 2:45 AM, Willie Loyd Tandingan <
> tandingan@gmail.com> wrote:
>
> > I'm gonna try that one back at work next week. Thanks!
> >
> > Admittedly, I thought of that one before but didn't try it out since I
> > thought it would generate an inner join instead and didn't think of
> turning
> > SQL logging on. We tried this erroneous funny query which we thought was
> > kind of correct but DN didn't accept anyway:
> >
> > SELECT this, IF (pt.person == this) THEN pt.template ELSE null FROM
> Person
> > p VARIABLES PersonTemplate pt
> >
> > I will have to read thoroughly the DN documentation again.
> >
> > On Sat, Jan 23, 2016 at 3:36 AM, Andy Jefferson 
> > wrote:
> >
> >> 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

[Willie Loyd Tandingan]

It worked! "datanucleus.query.jdoql.{varName}.join" had to be set to
OUTERJOIN for both pt and t. Thanks!

On Sun, Jan 24, 2016 at 2:45 AM, Willie Loyd Tandingan <
tandingan@gmail.com> wrote:

> I'm gonna try that one back at work next week. Thanks!
>
> Admittedly, I thought of that one before but didn't try it out since I
> thought it would generate an inner join instead and didn't think of turning
> SQL logging on. We tried this erroneous funny query which we thought was
> kind of correct but DN didn't accept anyway:
>
> SELECT this, IF (pt.person == this) THEN pt.template ELSE null FROM Person
> p VARIABLES PersonTemplate pt
>
> I will have to read thoroughly the DN documentation again.
>
> On Sat, Jan 23, 2016 at 3:36 AM, Andy Jefferson 
> wrote:
>
>> 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

[Willie Loyd Tandingan]

The setup was as follows:

final Query query = getJdoPersistenceManager().newQuery(Employee.class);
query.setResult("this, p");
query.setResultClass(EmployeePayslipTemplate.class);
query.setFilter("ept.employee == this && ept.payslipTemplate == p");
query.declareVariables("domain.EmployeePayslipTemplate ept;
domain.PayslipTemplate p");
query.addExtension("datanucleus.query.jdoql.ept.join", "LEFTOUTERJOIN");
query.addExtension("datanucleus.query.jdoql.p.join", "LEFTOUTERJOIN");
return (List) query.execute();


Domain objects:
* Employee
* EmployeePayslipTemplate
- Employee employee
- PayslipTemplate payslipTemplate
* PayslipTemplate

View model:
* EmployeePayslipTemplateView
- public EmployeePayslipTemplateView(Employee employee, PayslipTemplate
payslipTemplate) { ... }
- Employee employee
- PayslipTemplate payslipTemplate

On Fri, Jan 29, 2016 at 1:22 AM, Dan Haywood 
wrote:

> Could you copy all the relevant code so that I can write this up as an faq
> ?
> On 28 Jan 2016 17:17, "Willie Loyd Tandingan" 
> wrote:
>
> > It worked! "datanucleus.query.jdoql.{varName}.join" had to be set to
> > OUTERJOIN for both pt and t. Thanks!
> >
> > On Sun, Jan 24, 2016 at 2:45 AM, Willie Loyd Tandingan <
> > tandingan@gmail.com> wrote:
> >
> > > I'm gonna try that one back at work next week. Thanks!
> > >
> > > Admittedly, I thought of that one before but didn't try it out since I
> > > thought it would generate an inner join instead and didn't think of
> > turning
> > > SQL logging on. We tried this erroneous funny query which we thought
> was
> > > kind of correct but DN didn't accept anyway:
> > >
> > > SELECT this, IF (pt.person == this) THEN pt.template ELSE null FROM
> > Person
> > > p VARIABLES PersonTemplate pt
> > >
> > > I will have to read thoroughly the DN documentation again.
> > >
> > > On Sat, Jan 23, 2016 at 3:36 AM, Andy Jefferson 
> > > wrote:
> > >
> > >> 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

[Jeroen van der Wal]

Very elegant, nice!

On 28 January 2016 at 18:55, Willie Loyd Tandingan 
wrote:

> The setup was as follows:
>
> final Query query = getJdoPersistenceManager().newQuery(Employee.class);
> query.setResult("this, p");
> query.setResultClass(EmployeePayslipTemplate.class);
> query.setFilter("ept.employee == this && ept.payslipTemplate == p");
> query.declareVariables("domain.EmployeePayslipTemplate ept;
> domain.PayslipTemplate p");
> query.addExtension("datanucleus.query.jdoql.ept.join", "LEFTOUTERJOIN");
> query.addExtension("datanucleus.query.jdoql.p.join", "LEFTOUTERJOIN");
> return (List) query.execute();
>
>
> Domain objects:
> * Employee
> * EmployeePayslipTemplate
> - Employee employee
> - PayslipTemplate payslipTemplate
> * PayslipTemplate
>
> View model:
> * EmployeePayslipTemplateView
> - public EmployeePayslipTemplateView(Employee employee, PayslipTemplate
> payslipTemplate) { ... }
> - Employee employee
> - PayslipTemplate payslipTemplate
>
> On Fri, Jan 29, 2016 at 1:22 AM, Dan Haywood  >
> wrote:
>
> > Could you copy all the relevant code so that I can write this up as an
> faq
> > ?
> > On 28 Jan 2016 17:17, "Willie Loyd Tandingan" 
> > wrote:
> >
> > > It worked! "datanucleus.query.jdoql.{varName}.join" had to be set to
> > > OUTERJOIN for both pt and t. Thanks!
> > >
> > > On Sun, Jan 24, 2016 at 2:45 AM, Willie Loyd Tandingan <
> > > tandingan@gmail.com> wrote:
> > >
> > > > I'm gonna try that one back at work next week. Thanks!
> > > >
> > > > Admittedly, I thought of that one before but didn't try it out since
> I
> > > > thought it would generate an inner join instead and didn't think of
> > > turning
> > > > SQL logging on. We tried this erroneous funny query which we thought
> > was
> > > > kind of correct but DN didn't accept anyway:
> > > >
> > > > SELECT this, IF (pt.person == this) THEN pt.template ELSE null FROM
> > > Person
> > > > p VARIABLES PersonTemplate pt
> > > >
> > > > I will have to read thoroughly the DN documentation again.
> > > >
> > > > On Sat, Jan 23, 2016 at 3:36 AM, Andy Jefferson <
> a...@datanucleus.org>
> > > > wrote:
> > > >
> > > >> 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

[Jeroen van der Wal]

Thanks for correcting Andy. I learned now that you can create a
List collection from a JDOQL query, nice! Apache Isis however can
only render List collections. Would make a nice feature though to
support List rendering (pivot tables anyone?).

On 22 January 2016 at 20:36, Andy Jefferson  wrote:

> 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

[Willie Loyd Tandingan]

I'm gonna try that one back at work next week. Thanks!

Admittedly, I thought of that one before but didn't try it out since I
thought it would generate an inner join instead and didn't think of turning
SQL logging on. We tried this erroneous funny query which we thought was
kind of correct but DN didn't accept anyway:

SELECT this, IF (pt.person == this) THEN pt.template ELSE null FROM Person
p VARIABLES PersonTemplate pt

I will have to read thoroughly the DN documentation again.

On Sat, Jan 23, 2016 at 3:36 AM, Andy Jefferson 
wrote:

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

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

Re: Emulating left outer joins with JDOQL

[Andy Jefferson]

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