001
014
015 package com.liferay.portlet.asset.service.persistence;
016
017 import com.liferay.portal.kernel.dao.orm.QueryPos;
018 import com.liferay.portal.kernel.dao.orm.QueryUtil;
019 import com.liferay.portal.kernel.dao.orm.SQLQuery;
020 import com.liferay.portal.kernel.dao.orm.Session;
021 import com.liferay.portal.kernel.dao.orm.Type;
022 import com.liferay.portal.kernel.exception.SystemException;
023 import com.liferay.portal.kernel.util.CalendarUtil;
024 import com.liferay.portal.kernel.util.StringBundler;
025 import com.liferay.portal.kernel.util.StringPool;
026 import com.liferay.portal.kernel.util.StringUtil;
027 import com.liferay.portal.kernel.util.Validator;
028 import com.liferay.portal.model.Layout;
029 import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
030 import com.liferay.portal.util.PropsValues;
031 import com.liferay.portlet.asset.model.AssetEntry;
032 import com.liferay.portlet.asset.model.impl.AssetEntryImpl;
033 import com.liferay.util.dao.orm.CustomSQLUtil;
034
035 import java.sql.Timestamp;
036
037 import java.util.ArrayList;
038 import java.util.Date;
039 import java.util.Iterator;
040 import java.util.List;
041
042
047 public class AssetEntryFinderImpl
048 extends BasePersistenceImpl<AssetEntry> implements AssetEntryFinder {
049
050 public static final String FIND_BY_AND_CATEGORY_IDS =
051 AssetEntryFinder.class.getName() + ".findByAndCategoryIds";
052
053 public static final String FIND_BY_AND_TAG_IDS =
054 AssetEntryFinder.class.getName() + ".findByAndTagIds";
055
056 @Override
057 public int countEntries(AssetEntryQuery entryQuery) throws SystemException {
058 Session session = null;
059
060 try {
061 session = openSession();
062
063 SQLQuery q = buildAssetQuerySQL(entryQuery, true, session);
064
065 Iterator<Long> itr = q.iterate();
066
067 if (itr.hasNext()) {
068 Long count = itr.next();
069
070 if (count != null) {
071 return count.intValue();
072 }
073 }
074
075 return 0;
076 }
077 catch (Exception e) {
078 throw new SystemException(e);
079 }
080 finally {
081 closeSession(session);
082 }
083 }
084
085 @Override
086 public List<AssetEntry> findEntries(AssetEntryQuery entryQuery)
087 throws SystemException {
088
089 Session session = null;
090
091 try {
092 session = openSession();
093
094 SQLQuery q = buildAssetQuerySQL(entryQuery, false, session);
095
096 return (List<AssetEntry>)QueryUtil.list(
097 q, getDialect(), entryQuery.getStart(), entryQuery.getEnd());
098 }
099 catch (Exception e) {
100 throw new SystemException(e);
101 }
102 finally {
103 closeSession(session);
104 }
105 }
106
107 protected void buildAllCategoriesSQL(long[] categoryIds, StringBundler sb)
108 throws SystemException {
109
110 String findByAndCategoryIdsSQL = CustomSQLUtil.get(
111 FIND_BY_AND_CATEGORY_IDS);
112
113 sb.append(" AND (");
114
115 for (int i = 0; i < categoryIds.length; i++) {
116 String sql = null;
117
118 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
119 List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
120 categoryIds[i]);
121
122 if (treeCategoryIds.size() > 1) {
123 sql = StringUtil.replace(
124 findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
125 StringUtil.merge(treeCategoryIds));
126 }
127 }
128
129 if (sql == null) {
130 sql = StringUtil.replace(
131 findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
132 " = " + categoryIds[i]);
133 }
134
135 sb.append(sql);
136
137 if ((i + 1) < categoryIds.length) {
138 sb.append(" AND ");
139 }
140 }
141
142 sb.append(StringPool.CLOSE_PARENTHESIS);
143 }
144
145 protected void buildAllTagsSQL(long[][] tagIds, StringBundler sb) {
146 sb.append(" AND AssetEntry.entryId IN (");
147
148 for (int i = 0; i < tagIds.length; i++) {
149 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
150
151 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
152
153 sb.append(sql);
154
155 if ((i + 1) < tagIds.length) {
156 sb.append(" AND AssetEntry.entryId IN (");
157 }
158 }
159
160 for (int i = 0; i < tagIds.length; i++) {
161 if ((i + 1) < tagIds.length) {
162 sb.append(StringPool.CLOSE_PARENTHESIS);
163 }
164 }
165
166 sb.append(StringPool.CLOSE_PARENTHESIS);
167 }
168
169 protected void buildAnyCategoriesSQL(long[] categoryIds, StringBundler sb)
170 throws SystemException {
171
172 String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
173
174 String categoryIdsString = null;
175
176 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
177 List<Long> categoryIdsList = new ArrayList<Long>();
178
179 for (long categoryId : categoryIds) {
180 categoryIdsList.addAll(
181 AssetCategoryFinderUtil.findByG_L(categoryId));
182 }
183
184 if (categoryIdsList.isEmpty()) {
185 return;
186 }
187
188 categoryIdsString = StringUtil.merge(categoryIdsList);
189 }
190 else {
191 categoryIdsString = StringUtil.merge(categoryIds);
192 }
193
194 sb.append(" AND (");
195 sb.append(
196 StringUtil.replace(sql, "[$CATEGORY_ID$]", categoryIdsString));
197 sb.append(StringPool.CLOSE_PARENTHESIS);
198 }
199
200 protected String buildAnyTagsSQL(long[] tagIds, StringBundler sb) {
201 sb.append(" AND (");
202
203 for (int i = 0; i < tagIds.length; i++) {
204 sb.append("AssetTag.tagId = ");
205 sb.append(tagIds[i]);
206
207 if ((i + 1) != tagIds.length) {
208 sb.append(" OR ");
209 }
210 }
211
212 sb.append(StringPool.CLOSE_PARENTHESIS);
213
214 return sb.toString();
215 }
216
217 protected SQLQuery buildAssetQuerySQL(
218 AssetEntryQuery entryQuery, boolean count, Session session)
219 throws SystemException {
220
221 StringBundler sb = new StringBundler();
222
223 if (count) {
224 sb.append(
225 "SELECT COUNT(DISTINCT AssetEntry.entryId) AS COUNT_VALUE ");
226 }
227 else {
228 sb.append("SELECT {AssetEntry.*} ");
229
230 boolean selectRatings = false;
231
232 String orderByCol1 = entryQuery.getOrderByCol1();
233 String orderByCol2 = entryQuery.getOrderByCol2();
234
235 if (orderByCol1.equals("ratings") ||
236 orderByCol2.equals("ratings")) {
237
238 selectRatings = true;
239
240 sb.append(", TEMP_TABLE.averageScore ");
241 }
242
243 sb.append("FROM (SELECT DISTINCT AssetEntry.entryId ");
244
245 if (selectRatings) {
246 sb.append(", RatingsStats.averageScore ");
247 }
248 }
249
250 sb.append("FROM AssetEntry ");
251
252 if (entryQuery.getAnyTagIds().length > 0) {
253 sb.append("INNER JOIN ");
254 sb.append("AssetEntries_AssetTags ON ");
255 sb.append("(AssetEntries_AssetTags.entryId = ");
256 sb.append("AssetEntry.entryId) ");
257 sb.append("INNER JOIN ");
258 sb.append("AssetTag ON ");
259 sb.append("(AssetTag.tagId = AssetEntries_AssetTags.tagId) ");
260 }
261
262 if (entryQuery.getLinkedAssetEntryId() > 0) {
263 sb.append("INNER JOIN ");
264 sb.append("AssetLink ON ");
265 sb.append("(AssetEntry.entryId = AssetLink.entryId1) ");
266 sb.append("OR (AssetEntry.entryId = AssetLink.entryId2)");
267 }
268
269 if (entryQuery.getOrderByCol1().equals("ratings") ||
270 entryQuery.getOrderByCol2().equals("ratings")) {
271
272 sb.append(" LEFT JOIN ");
273 sb.append("RatingsStats ON ");
274 sb.append("(RatingsStats.classNameId = ");
275 sb.append("AssetEntry.classNameId) AND ");
276 sb.append("(RatingsStats.classPK = AssetEntry.classPK)");
277 }
278
279 sb.append("WHERE ");
280
281 int whereIndex = sb.index();
282
283 if (entryQuery.getLinkedAssetEntryId() > 0) {
284 sb.append(" AND ((AssetLink.entryId1 = ?) OR ");
285 sb.append("(AssetLink.entryId2 = ?))");
286 sb.append(" AND (AssetEntry.entryId != ?)");
287 }
288
289 if (entryQuery.isVisible() != null) {
290 sb.append(" AND (visible = ?)");
291 }
292
293 if (entryQuery.isExcludeZeroViewCount()) {
294 sb.append(" AND (AssetEntry.viewCount > 0)");
295 }
296
297
298
299 if (Validator.isNotNull(entryQuery.getKeywords())) {
300 sb.append(" AND ((AssetEntry.userName LIKE ?) OR");
301 sb.append(" (AssetEntry.title LIKE ?) OR");
302 sb.append(" (AssetEntry.description LIKE ?))");
303 }
304 else if (Validator.isNotNull(entryQuery.getUserName()) ||
305 Validator.isNotNull(entryQuery.getTitle()) ||
306 Validator.isNotNull(entryQuery.getDescription())) {
307
308 sb.append(" AND (");
309
310 boolean requiresOperator = false;
311
312 if (Validator.isNotNull(entryQuery.getUserName())) {
313 sb.append("(AssetEntry.userName LIKE ?)");
314
315 requiresOperator = true;
316 }
317
318 if (Validator.isNotNull(entryQuery.getTitle())) {
319 if (requiresOperator) {
320 sb.append(entryQuery.isAndOperator() ? " AND " : " OR ");
321 }
322
323 sb.append("(AssetEntry.title LIKE ?)");
324
325 requiresOperator = true;
326 }
327
328 if (Validator.isNotNull(entryQuery.getDescription())) {
329 if (requiresOperator) {
330 sb.append(entryQuery.isAndOperator() ? " AND " : " OR ");
331 }
332
333 sb.append("(AssetEntry.description LIKE ?)");
334 }
335
336 sb.append(")");
337 }
338
339
340
341 Layout layout = entryQuery.getLayout();
342
343 if (layout != null) {
344 sb.append(" AND (AssetEntry.layoutUuid = ?)");
345 }
346
347
348
349 if (entryQuery.getAllCategoryIds().length > 0) {
350 buildAllCategoriesSQL(entryQuery.getAllCategoryIds(), sb);
351 }
352
353 if (entryQuery.getAnyCategoryIds().length > 0) {
354 buildAnyCategoriesSQL(entryQuery.getAnyCategoryIds(), sb);
355 }
356
357 if (entryQuery.getNotAllCategoryIds().length > 0) {
358 buildNotAllCategoriesSQL(entryQuery.getNotAllCategoryIds(), sb);
359 }
360
361 if (entryQuery.getNotAnyCategoryIds().length > 0) {
362 buildNotAnyCategoriesSQL(entryQuery.getNotAnyCategoryIds(), sb);
363 }
364
365
366
367 if (entryQuery.getClassTypeIds().length > 0) {
368 buildClassTypeIdsSQL(entryQuery.getClassTypeIds(), sb);
369 }
370
371
372
373 if (entryQuery.getAllTagIds().length > 0) {
374 buildAllTagsSQL(entryQuery.getAllTagIdsArray(), sb);
375 }
376
377 if (entryQuery.getAnyTagIds().length > 0) {
378 buildAnyTagsSQL(entryQuery.getAnyTagIds(), sb);
379 }
380
381 if (entryQuery.getNotAllTagIds().length > 0) {
382 buildNotAllTagsSQL(entryQuery.getNotAllTagIdsArray(), sb);
383 }
384
385 if (entryQuery.getNotAnyTagIds().length > 0) {
386 buildNotAnyTagsSQL(entryQuery.getNotAnyTagIds(), sb);
387 }
388
389
390
391 sb.append(
392 getDates(
393 entryQuery.getPublishDate(), entryQuery.getExpirationDate()));
394 sb.append(getGroupIds(entryQuery.getGroupIds()));
395 sb.append(getClassNameIds(entryQuery.getClassNameIds()));
396
397 if (!count) {
398 sb.append(") TEMP_TABLE ");
399 sb.append("INNER JOIN ");
400 sb.append("AssetEntry AssetEntry ON ");
401 sb.append("TEMP_TABLE.entryId = AssetEntry.entryId");
402
403 sb.append(" ORDER BY ");
404
405 if (entryQuery.getOrderByCol1().equals("ratings")) {
406 sb.append("TEMP_TABLE.averageScore");
407 }
408 else {
409 sb.append("AssetEntry.");
410 sb.append(entryQuery.getOrderByCol1());
411 }
412
413 sb.append(StringPool.SPACE);
414 sb.append(entryQuery.getOrderByType1());
415
416 if (Validator.isNotNull(entryQuery.getOrderByCol2()) &&
417 !entryQuery.getOrderByCol1().equals(
418 entryQuery.getOrderByCol2())) {
419
420 if (entryQuery.getOrderByCol2().equals("ratings")) {
421 sb.append(", TEMP_TABLE.averageScore");
422 }
423 else {
424 sb.append(", AssetEntry.");
425 sb.append(entryQuery.getOrderByCol2());
426 }
427
428 sb.append(StringPool.SPACE);
429 sb.append(entryQuery.getOrderByType2());
430 }
431 }
432
433 if (sb.index() > whereIndex) {
434 String where = sb.stringAt(whereIndex);
435
436 if (where.startsWith(" AND")) {
437 sb.setStringAt(where.substring(4), whereIndex);
438 }
439 }
440
441 String sql = sb.toString();
442
443 SQLQuery q = session.createSQLQuery(sql);
444
445 if (count) {
446 q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
447 }
448 else {
449 q.addEntity("AssetEntry", AssetEntryImpl.class);
450 }
451
452 QueryPos qPos = QueryPos.getInstance(q);
453
454 if (entryQuery.getLinkedAssetEntryId() > 0) {
455 qPos.add(entryQuery.getLinkedAssetEntryId());
456 qPos.add(entryQuery.getLinkedAssetEntryId());
457 qPos.add(entryQuery.getLinkedAssetEntryId());
458 }
459
460 if (entryQuery.isVisible() != null) {
461 qPos.add(entryQuery.isVisible());
462 }
463
464 if (Validator.isNotNull(entryQuery.getKeywords())) {
465 qPos.add(
466 StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
467 qPos.add(
468 StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
469 qPos.add(
470 StringUtil.quote(entryQuery.getKeywords(), StringPool.PERCENT));
471 }
472 else {
473 if (Validator.isNotNull(entryQuery.getUserName())) {
474 qPos.add(
475 StringUtil.quote(
476 entryQuery.getUserName(), StringPool.PERCENT));
477 }
478
479 if (Validator.isNotNull(entryQuery.getTitle())) {
480 qPos.add(
481 StringUtil.quote(
482 entryQuery.getTitle(), StringPool.PERCENT));
483 }
484
485 if (Validator.isNotNull(entryQuery.getDescription())) {
486 qPos.add(
487 StringUtil.quote(
488 entryQuery.getDescription(), StringPool.PERCENT));
489 }
490 }
491
492 if (layout != null) {
493 qPos.add(layout.getUuid());
494 }
495
496 setDates(
497 qPos, entryQuery.getPublishDate(), entryQuery.getExpirationDate());
498
499 qPos.add(entryQuery.getGroupIds());
500 qPos.add(entryQuery.getClassNameIds());
501
502 return q;
503 }
504
505 protected void buildClassTypeIdsSQL(long[] classTypeIds, StringBundler sb) {
506 sb.append(" AND (");
507
508 for (int i = 0; i < classTypeIds.length; i++) {
509 sb.append(" AssetEntry.classTypeId = ");
510 sb.append(classTypeIds[i]);
511
512 if ((i + 1) < classTypeIds.length) {
513 sb.append(" OR ");
514 }
515 else {
516 sb.append(StringPool.CLOSE_PARENTHESIS);
517 }
518 }
519 }
520
521 protected void buildNotAllCategoriesSQL(
522 long[] categoryIds, StringBundler sb)
523 throws SystemException {
524
525 String findByAndCategoryIdsSQL = CustomSQLUtil.get(
526 FIND_BY_AND_CATEGORY_IDS);
527
528 sb.append(" AND (");
529
530 for (int i = 0; i < categoryIds.length; i++) {
531 sb.append("NOT ");
532
533 String sql = null;
534
535 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
536 List<Long> treeCategoryIds = AssetCategoryFinderUtil.findByG_L(
537 categoryIds[i]);
538
539 if (treeCategoryIds.size() > 1) {
540 sql = StringUtil.replace(
541 findByAndCategoryIdsSQL, "[$CATEGORY_ID$]",
542 StringUtil.merge(treeCategoryIds));
543 }
544 }
545
546 if (sql == null) {
547 sql = StringUtil.replace(
548 findByAndCategoryIdsSQL, " IN ([$CATEGORY_ID$])",
549 " = " + categoryIds[i]);
550 }
551
552 sb.append(sql);
553
554 if ((i + 1) < categoryIds.length) {
555 sb.append(" OR ");
556 }
557 }
558
559 sb.append(StringPool.CLOSE_PARENTHESIS);
560 }
561
562 protected void buildNotAllTagsSQL(long[][] tagIds, StringBundler sb) {
563 sb.append(" AND (");
564
565 for (int i = 0; i < tagIds.length; i++) {
566 sb.append("AssetEntry.entryId NOT IN (");
567
568 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
569
570 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(tagIds[i]));
571
572 sb.append(sql);
573 sb.append(StringPool.CLOSE_PARENTHESIS);
574
575 if (((i + 1) < tagIds.length) && (tagIds[i + 1].length > 0)) {
576 sb.append(" OR ");
577 }
578 }
579
580 sb.append(StringPool.CLOSE_PARENTHESIS);
581 }
582
583 protected void buildNotAnyCategoriesSQL(
584 long[] notCategoryIds, StringBundler sb)
585 throws SystemException {
586
587 sb.append(" AND (NOT ");
588
589 String sql = CustomSQLUtil.get(FIND_BY_AND_CATEGORY_IDS);
590
591 String notCategoryIdsString = null;
592
593 if (PropsValues.ASSET_CATEGORIES_SEARCH_HIERARCHICAL) {
594 List<Long> notCategoryIdsList = new ArrayList<Long>();
595
596 for (long notCategoryId : notCategoryIds) {
597 notCategoryIdsList.addAll(
598 AssetCategoryFinderUtil.findByG_L(notCategoryId));
599 }
600
601 notCategoryIdsString = StringUtil.merge(notCategoryIdsList);
602 }
603 else {
604 notCategoryIdsString = StringUtil.merge(notCategoryIds);
605 }
606
607 sb.append(
608 StringUtil.replace(sql, "[$CATEGORY_ID$]", notCategoryIdsString));
609 sb.append(StringPool.CLOSE_PARENTHESIS);
610 }
611
612 protected String buildNotAnyTagsSQL(long[] notTagIds, StringBundler sb) {
613 sb.append(" AND (");
614
615 for (int i = 0; i < notTagIds.length; i++) {
616 sb.append("AssetEntry.entryId NOT IN (");
617
618 String sql = CustomSQLUtil.get(FIND_BY_AND_TAG_IDS);
619
620 sql = StringUtil.replace(sql, "[$TAG_ID$]", getTagIds(notTagIds));
621
622 sb.append(sql);
623 sb.append(StringPool.CLOSE_PARENTHESIS);
624
625 if ((i + 1) < notTagIds.length) {
626 sb.append(" AND ");
627 }
628 }
629
630 sb.append(StringPool.CLOSE_PARENTHESIS);
631
632 return sb.toString();
633 }
634
635 protected String getClassNameIds(long[] classNameIds) {
636 if (classNameIds.length == 0) {
637 return StringPool.BLANK;
638 }
639
640 StringBundler sb = new StringBundler(classNameIds.length + 1);
641
642 sb.append(" AND (AssetEntry.classNameId = ?");
643
644 for (int i = 0; i < (classNameIds.length - 1); i++) {
645 sb.append(" OR AssetEntry.classNameId = ?");
646 }
647
648 sb.append(StringPool.CLOSE_PARENTHESIS);
649
650 return sb.toString();
651 }
652
653 protected String getDates(Date publishDate, Date expirationDate) {
654 StringBundler sb = new StringBundler(4);
655
656 if (publishDate != null) {
657 sb.append(" AND (AssetEntry.publishDate IS NULL OR ");
658 sb.append("AssetEntry.publishDate < ?)");
659 }
660
661 if (expirationDate != null) {
662 sb.append(" AND (AssetEntry.expirationDate IS NULL OR ");
663 sb.append("AssetEntry.expirationDate > ?)");
664 }
665
666 return sb.toString();
667 }
668
669 protected String getGroupIds(long[] groupIds) {
670 if (groupIds.length == 0) {
671 return StringPool.BLANK;
672 }
673
674 StringBundler sb = new StringBundler(groupIds.length + 1);
675
676 sb.append(" AND (AssetEntry.groupId = ?");
677
678 for (int i = 0; i < (groupIds.length - 1); i++) {
679 sb.append(" OR AssetEntry.groupId = ?");
680 }
681
682 sb.append(StringPool.CLOSE_PARENTHESIS);
683
684 return sb.toString();
685 }
686
687 protected String getTagIds(long[] tagIds) {
688 StringBundler sb = new StringBundler((tagIds.length * 3) - 1);
689
690 for (int i = 0; i < tagIds.length; i++) {
691 sb.append("tagId = ");
692 sb.append(tagIds[i]);
693
694 if ((i + 1) != tagIds.length) {
695 sb.append(" OR ");
696 }
697 }
698
699 return sb.toString();
700 }
701
702 protected void setDates(
703 QueryPos qPos, Date publishDate, Date expirationDate) {
704
705 if (publishDate != null) {
706 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
707
708 qPos.add(publishDate_TS);
709 }
710
711 if (expirationDate != null) {
712 Timestamp expirationDate_TS = CalendarUtil.getTimestamp(
713 expirationDate);
714
715 qPos.add(expirationDate_TS);
716 }
717 }
718
719 }