Foren
Using Dynamic Query to select rows with null values
Cipriano Teibão, geändert vor 11 Jahren.
Using Dynamic Query to select rows with null values
New Member Beiträge: 10 Beitrittsdatum: 14.12.12 Neueste Beiträge
Hi,
Can any one please help me out with a Dynamic Query?
I need to select all Journal Articles that have not expired or that have the expire date as null. I do now want to look at the Status, only at the date.
This is the sql query I used to test:
And this is what I have so far
I have searched the web for hours and cant find how to solve my problem, please help me out, I am new to Liferay and this is driving me crazy.
Best regards,
Cipriano
Can any one please help me out with a Dynamic Query?
I need to select all Journal Articles that have not expired or that have the expire date as null. I do now want to look at the Status, only at the date.
This is the sql query I used to test:
select * from `journalarticle`
where `journalarticle`.`groupId`=10180
and `journalarticle`.`companyId`=10154
and `journalarticle`.`structureId`='18406'
and `journalarticle`.`version` = (
select max(ja.`version`) from `journalarticle` ja
where ja.`articleId`=`journalarticle`.`articleId`)
and (`journalarticle`.`expirationDate`>NOW() or `journalarticle`.`expirationDate` is null)
And this is what I have so far
ClassLoader cl = PortalClassLoaderUtil.getClassLoader();
DynamicQuery subQuery = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "articleSub", cl )
.add(PropertyFactoryUtil.forName("articleSub.articleId").eqProperty("articleParent.articleId"))
.setProjection(ProjectionFactoryUtil.max("articleSub.version"));
Junction junction = RestrictionsFactoryUtil.disjunction()
.add(RestrictionsFactoryUtil.gt("expirationDate", now ))
.add(RestrictionsFactoryUtil.eq("expirationDate", "0000-00-00 00:00:00" )); // <----------- This is where my code fails, i tried with that zeros but doesnt works
DynamicQuery query = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "articleParent", cl )
.add(PropertyFactoryUtil.forName("version").eq(subQuery))
.add(PropertyFactoryUtil.forName("groupId").eq(groupId))
.add(PropertyFactoryUtil.forName("companyId").eq(companyId))
.add(PropertyFactoryUtil.forName("structureId").eq(newsTemplateId))
.add( junction )
.addOrder(OrderFactoryUtil.desc("modifiedDate"));
query.setLimit(from, to);
results = JournalArticleLocalServiceUtil.dynamicQuery(query);
I have searched the web for hours and cant find how to solve my problem, please help me out, I am new to Liferay and this is driving me crazy.
Best regards,
Cipriano
David H Nebinger, geändert vor 11 Jahren.
RE: Using Dynamic Query to select rows with null values
Liferay Legend Beiträge: 14916 Beitrittsdatum: 02.09.06 Neueste Beiträge
Why not use RestrictionsFactoryUtil.isNull("expirationDate")?
Cipriano Teibão, geändert vor 11 Jahren.
RE: Using Dynamic Query to select rows with null values
New Member Beiträge: 10 Beitrittsdatum: 14.12.12 Neueste Beiträge
That was it, thank you!