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