Eclipse Community Forums: EclipseLink » Trouble converting SQL into JPQL with OUTER JOIN

Help

Home

Home » Eclipse Projects » EclipseLink » Trouble converting SQL into JPQL with OUTER JOIN

Show: Today's Messages :: Show Polls :: Message Navigator

Trouble converting SQL into JPQL with OUTER JOIN [message #634733]

Fri, 22 October 2010 21:00

Fericit Bostan

Messages: 68
Registered: June 2010

Member

I'm using EclipseLink 2.1.1 on GlassFish 3.0.1. I'm having a hard time converting a SQL query into JPQL. The SQL works as expected. I guess the thing I'm struggling with is that I'm using a JoinTable on one of my objects that is not represented by an entity.

Below you will find my Entities that are involved in the SQL and the working SQL that returns the correct values.

Any help you can provide me with is greatly appreciated. Thank you for your effort.

Organization Entity:

@Entity
@Table(name = "Organization")
public class Organization implements Serializable {

	@Id
	@Column(name = "OrganizationId", nullable = false)
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "Name", nullable = false, length = 128)
	private String name;

	@Column(name = "Level", nullable = false)
	private int level;

	@Column(name = "Alias", nullable = false, length = 50)
	private String alias;

	@JoinTable(name = "OrganizationAncestry", 
			joinColumns = { 
				@JoinColumn(name = "Ancestor", referencedColumnName = "OrganizationId", nullable = false) 
			}, 
			inverseJoinColumns = { 
				@JoinColumn(name = "Descendent", referencedColumnName = "OrganizationId", nullable = false) 
			})
	@ManyToMany(fetch = FetchType.EAGER)
	private Collection<Organization> descendents;

	@ManyToMany(mappedBy = "descendents", fetch = FetchType.EAGER)
	private Collection<Organization> ancestors;

DocumentType entity:

@Entity
@Table(name = "DocumentType")
public class DocumentType implements Serializable {

	@Id
  @Column(name = "DocumentTypeID", nullable = false)
	@GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "TypeName", nullable = false, length = 40)
  private String name;

  @Column(name = "Alias", nullable = false, length = 40)
  private String alias;

  @Column(name = "Description", length = 256)
  private String description;

  @Column(name = "IsActive", nullable = false)
  private boolean active;
        
	@JoinColumn(name = "OrganizationID", referencedColumnName = "OrganizationID")
	@ManyToOne(fetch = FetchType.LAZY)
	private Organization organization;
	
}

My SQL:


SELECT
	t1.* 
FROM
    DocumentType AS t1 
JOIN
    OrganizationAncestry AS t2 
ON 
    t1.OrganizationId = t2.Ancestor 
JOIN 
	Organization AS t3 
ON 
    t3.OrganizationId = t2.Descendent
LEFT OUTER JOIN (
            OrganizationAncestry AS t4 
		JOIN 
            Organization AS t5 
		ON 
            t4.Descendent = t5.OrganizationId 
		JOIN 
            DocumentType AS t6 
		ON 
            t5.OrganizationId = t6.OrganizationId 
    ) 
ON 
    t2.Ancestor = t4.Ancestor 
AND
	t4.Ancestor <> t4.Descendent 
AND
	t1.Alias = t6.Alias 
WHERE
    t1.IsActive = 1
AND
	t3.Alias = 'IWT' 
AND
	t6.Alias IS NULL

[Updated on: Fri, 22 October 2010 22:37]

Report message to a moderator

Re: Trouble converting SQL into JPQL with OUTER JOIN [message #635092 is a reply to message #634733]

Mon, 25 October 2010 15:42

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

Your SQL looks very complicate, I'm not sure what you are trying to do. Could you describe your query in English.

Perhaps also include the JPQL you have tried.

The JPQL may be something like:

Select doc from DocumentType doc join dt.descendents org
where doc.active = 1 and org.alias = 'IWT'

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: Trouble converting SQL into JPQL with OUTER JOIN [message #635156 is a reply to message #635092]

Mon, 25 October 2010 19:21

Fericit Bostan

Messages: 68
Registered: June 2010

Member

I've implemented a closure table pattern in a few of our applications. It's worked out very well. We use this for an Organization structure to represent different business units within the company. I have the closure tables implemented and an adjacent table (PartyDocumentType) that references the PartyDocumentOrganization table. The intent is that Organizations can have different document types. DocumentTypes can be inherited from the parent Organization but can also be overridden at the child Organization. So the child Organization's DocumentType has priority over the parent.

The SQL statement returns all the DocumentTypes for a given Organization, including those defined in the parent Organizations, but excluding the duplicates found in the parent organizations (if any, based upon the DocumentType's Alias column).

I have entities defined for the Organization and the DocumentType but not the OrganizationAncestry as it is defined as a JoinTable.

I've provided the DDL for my tables, seed data to populate them and a sample SQL statement.

Thank you for the help...
Chris

DDL:

CREATE TABLE [dbo].[PartyDocumentOrganization]  ( 
    [PartyDocumentOrganizationId]   int IDENTITY(1,1) NOT NULL,
    [Level]                         int NOT NULL,
    [Alias]                         varchar(50) NOT NULL,
    [Name]                          varchar(128) NOT NULL,
    [UUID]                          varchar(36) NOT NULL,
    [WhenCreated]                   datetime NOT NULL CONSTRAINT [DF__PartyDocu__WhenC__26D2223A]  DEFAULT (getdate()),
    [WhenModified]                  datetime NOT NULL CONSTRAINT [DF__PartyDocu__WhenM__27C64673]  DEFAULT (getdate()),
    [ModifiedById]                  varchar(15) NOT NULL CONSTRAINT [DF__PartyDocu__Modif__28BA6AAC]  DEFAULT (suser_sname()),
    [Version]                       int NOT NULL CONSTRAINT [DF__PartyDocu__Versi__29AE8EE5]  DEFAULT ((0)),
    CONSTRAINT [PK__PartyDoc__40493131220D6D1D] PRIMARY KEY([PartyDocumentOrganizationId])
)
GO

CREATE TABLE [dbo].[PartyDocumentOrganizationAncestry]  ( 
    [Ancestor]      int NOT NULL,
    [Descendent]    int NOT NULL,
    CONSTRAINT [PK__PartyDoc__D7F327222E734402] PRIMARY KEY([Ancestor],[Descendent])
)
GO
ALTER TABLE [dbo].[PartyDocumentOrganizationAncestry]
    ADD CONSTRAINT [rgnztnncstrDscndnt]
    FOREIGN KEY([Descendent])
    REFERENCES [dbo].[PartyDocumentOrganization]([PartyDocumentOrganizationId])
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION 
GO
ALTER TABLE [dbo].[PartyDocumentOrganizationAncestry]
    ADD CONSTRAINT [rgnztnncestryncstr]
    FOREIGN KEY([Ancestor])
    REFERENCES [dbo].[PartyDocumentOrganization]([PartyDocumentOrganizationId])
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION 
GO


CREATE TABLE [dbo].[PartyDocumentType]  ( 
    [PartyDocumentTypeID]           int IDENTITY(1,1) NOT NULL,
    [TypeName]                      varchar(40) NOT NULL,
    [Description]                   varchar(256) NULL,
    [WhenCreated]                   datetime NOT NULL CONSTRAINT [DF_PartyDocumentType_WhenCreated]  DEFAULT (getdate()),
    [IsActive]                      bit NOT NULL CONSTRAINT [DF_PartyDocumentType_IsActive]  DEFAULT ((1)),
    [PartyDocumentOrganizationID]   int NULL,
    [Version]                       int NOT NULL CONSTRAINT [DF__PartyDocu__Versi__39E4F6AE]  DEFAULT ((0)),
    [Alias]                         nvarchar(225) NOT NULL CONSTRAINT [DF__PartyDocu__Alias__3AD91AE7]  DEFAULT (''),
    CONSTRAINT [PK_PartyDocumentType] PRIMARY KEY([PartyDocumentTypeID])
)
GO
ALTER TABLE [dbo].[PartyDocumentType]
    ADD CONSTRAINT [fk_artyDocumentType_PartyDocumentOrganizationID]
    FOREIGN KEY([PartyDocumentOrganizationID])
    REFERENCES [dbo].[PartyDocumentOrganization]([PartyDocumentOrganizationId])
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION 
GO

Seed Data:

INSERT IGNORE INTO [dbo].[PartyDocumentOrganization]([Level], [Alias], [Name], [UUID]) 
    VALUES(1,'INTRAXINC','Intrax Inc','137B3E7D-4938-4746-8AB5-4F7EC834F4AB' )
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganization]([Level], [Alias], [Name], [UUID]) 
    VALUES(2,'ICD','Intrax Career Development','1B912F25-538F-4CCC-AC46-40AD3D44A866' )
Go
INSERT IGNORE INTO [dbo].[PartyDocumentOrganization]([Level], [Alias], [Name], [UUID]) 
    VALUES(3,'IWT','Intrax Work Travel','D0440813-31C9-450E-903B-39754EB6CD27' )
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganization]([Level], [Alias], [Name], [UUID]) 
    VALUES(3,'ICT','Intrax Career Training','F32C35C8-F29A-4B88-AFF9-278D1C96355B' )
GO


INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(1,1)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(1,2)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(1,3)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(1,4)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(2,2)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(2,3)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(2,4)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(3,3)
GO
INSERT IGNORE INTO [dbo].[PartyDocumentOrganizationAncestry]([Ancestor], [Descendent]) 
    VALUES(4,4)
GO


INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('Participant Eligibility Form','Participant Eligibility Form',3,'ParticipantEligibilityForm')
GO
INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('University Letter','University Letter',3,'UniversityLetter')
GO
INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('University Letter (Translation)','University Letter (Translation)',3,'UniversityLetterTranslation')
GO
INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('Other','Other',3,'Other')
GO
INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('Program Agreement','Program Agreement',3,'ProgramAgreement')
GO
INSERT IGNORE INTO [dbo].[PartyDocumentType]([TypeName], [Description], [PartyDocumentOrganizationID], [Alias]) 
    VALUES('Other','Other',2,'Other')
GO

Report message to a moderator

Re: Trouble converting SQL into JPQL with OUTER JOIN [message #635331 is a reply to message #635156]

Tue, 26 October 2010 13:11

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

Unfortunately, the DDL and insert data does not make it easier to distinguish and interpret the SQL and correlate it to the object model being used. In the future I would recommend you simplify the problem down to components.

In this case, it looks like the you have the Organization and DocumentType tables mapped to objects, and a OrganizationAncestry used as a relation table for a self referencing M:M on Organization. Unfortunately, you are trying to access fields in the relationtable and map them to DocumentType which is not apart of the relationship - JPQL is object oriented and does not support this. So this query will have to be done with native SQL.

Best Regards,
Chris

Report message to a moderator

Re: Trouble converting SQL into JPQL with OUTER JOIN [message #635886 is a reply to message #635156]

Thu, 28 October 2010 13:44

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

Maybe something like:

Select distinct doc from DocumentType doc, Organization org left join org.ancestors ancs
where doc.active = 1 and org.alias = 'IWT' and (doc.organization = org || doc.organization = ancs)

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Previous Topic:	OneToOne + MySQL issue
Next Topic:	connecting an application to two different JNDI datasources

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:58:06 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter