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